SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-138
Considerations for CREATE TRIGGER
Triggers and Primary Keys
Suppose you create this table:
CREATE TABLE t1( c1 varchar(2040) 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
2048 bytes.
*** ERROR[11041] Temporary table could not be created! Check
default partitions.
Trigger temporary table is a table associated with the subject table of the trigger and is
used to store intermediate results during trigger execution. In the example, error is
returned because of the way trigger temporary tables are created. The temporary table
is created with two more columns than its corresponding subject table. The combined
length of the additional columns 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 update the length of column c1 of table t1 from varchar (2040) to a varchar of
2032 or less bytes (for example, varchar (2000)), the CREATE TRIGGER statement
completes successfully.
Rowsets
SQL/MX rowsets are allowed in UPDATE and DELETE statements that are trigger
events.
UPDATE and DELETE statements that use rowset arrays perform multiple executions
of UPDATE or DELETE statements. UPDATE and DELETE statement triggers behave
as a sequence of statement triggers that are triggered once for each value in the array
of values in the rowset.
Note. If you want to create triggers on a table, its primary key length cannot exceed 2032
bytes. A table that does not include triggers can have a primary key of 2048 bytes.