Neoview SQL Reference Manual (R2.4)

triggers are used to condition input data, while AFTER-type triggers encode actual application
logic.
Restrictions on Triggers
• The trigger feature does not allow the use of:
— Positioned deletes and updates as triggered statements.
— Subqueries in search-condition for AFTER triggers (but they are allowed in
search-condition for BEFORE triggers.)
• To create a trigger on a given table, the name of the table should be at least six characters
less than the maximum length of a valid table name (128 characters).
• There is a compile time limit of 256 triggers for each statement. This means if the statement
has the potential to activate more than 256 triggers (not necessarily different triggers, there
could be one or more recursive triggers), an error is raised. The restriction applies regardless
of whether a trigger is disabled or enabled and whether the trigger may or may not be
activated.
Recompilation and Triggers
User applications that change (INSERT, UPDATE, or DELETE) information in a table require
compilation 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:
*** ERROR[1085] The calculated key length is greater than 2048 bytes.
*** ERROR[11041] Temporary table could not be created! Check default partitions.
This is because of the way that trigger temporary tables are created. Trigger temporary tables
are auxiliary tables used by triggers to store transient data it uses during its execution. 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(2040) to varchar(2000), the primary key
length is now 2000 bytes, and the CREATE TRIGGER statement completes successfully. Keep
this limit in mind when you create tables.
96 SQL Statements