SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-100
Considerations for CREATE TRIGGER
•
You cannot define triggers on SQL/MP objects. SQL/MP objects cannot be
referenced in a trigger.
Recompilation and Triggers
User applications that change (INSERT, UPDATE, or DELETE) information in a table
are automatically recompiled when a trigger with a matching event is added or
dropped. User applications that use a SELECT on the subject table do not require
recompilation. User applications do not require an SQL compilation when a trigger is
changed from DISABLED to ENABLED, or from ENABLED to DISABLED, using the
ALTER TRIGGER statement. User applications require SQL recompilations only when
triggers are added or dropped. No source code changes or language compilations are
required.
Triggers and Primary Keys
Suppose you create this table:
CREATE TABLE t1( c1 varchar(255) NOT NULL,
c2 int,
c3 int,
c4 char(3),
c5 char(3),
primary key (c1)
);
CREATE TABLE t2 (c1 char(3), c2 char(3));
When you try to create a trigger on this table using these commands, you receive
errors:
CREATE TRIGGER trg1
AFTER INSERT ON t1
REFERENCING NEW AS newrow
FOR EACH ROW
WHEN (newrow.c2 > newrow.c3)
INSERT INTO t2 VALUES (newrow.c4, newrow.c5);
*** ERROR[1085] The calculated key length is greater than 255
bytes.
*** ERROR[11041] Temporary table could not be created! Check
default partitions.
This is because of the way that trigger temporary tables are created. This temporary
table is created with two more columns than its corresponding subject table has,
whose combined length is 16 bytes. The two added columns, along with the subject
table’s primary key, form the primary key of the temporary table. This primary key is too
long.
If you change column c1 of table t1 from varchar(255) to varchar(52), the primary key
length is now 52 bytes, and the CREATE TRIGGER statement completes successfully.