SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-149
Considerations for CREATE TRIGGER
•
Triggers are not allowed on SQL/MP aliases.
•
All types of subqueries are not supported in search-condition for AFTER
triggers:
•
Nested subqueries, queries containing UNION construct and compound
statements are not allowed.
•
The transition table cannot be referenced in the WHEN clause of an AFTER
trigger statement.
•
INSERT, DELETE, and UPDATE queries are not allowed in the WHEN clause
of an AFTER trigger statement.
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(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.