Restrictions on Triggers

To create a trigger on a table (or an INSTEAD OF trigger on a view), you must own the table or view, or have DBA privilege. For the relationship between the privileges of the trigger owner and those of other users, see Privileges to Execute Trigger Actions.

The table on which you create a trigger must exist in the current database. You cannot create a trigger on any of the following types of tables:
  • A diagnostics table, a violations table, or a table in another database
  • A temporary table or a system catalog table
  • A table object that the CREATE EXTERNAL TABLE or CREATE SEQUENCE statement created.

In DB-Access, if you want to define a trigger as part of a schema, place the CREATE TRIGGER statement inside a CREATE SCHEMA statement.

If you are embedding the CREATE TRIGGER statement in programs, you cannot use a host variable in the trigger definition.

You can use the DROP TRIGGER statement to remove an existing trigger. If you use the DROP TABLE or DROP VIEW statement to remove triggering tables or views from the database, all triggers on those tables or views are also dropped.

The ON EXCEPTION statement of SPL has no effect when it is issued from a trigger routine, nor from the Action clause or the Correlated Action clause of a trigger.

The triggered action of an Insert trigger that increments a BIGSERIAL, SERIAL, or SERIAL8 column does not update the sqlca.sqlerrd[1] field of the SQL Communication Area structure. The triggered INSERT operation can successfully increment the serial counter for the column, but the value of the sqlca.sqlerrd[1] field remains zero, rather than being reset to the new serial value.

You cannot define a DELETE trigger on a table that has a referential constraint that specifies ON DELETE CASCADE.

UNION subqueries cannot be triggering events. If a valid UNION subquery specifies a column on which a Select trigger has been defined, the query succeeds, but the trigger (or the INSTEAD OF trigger on a view) is ignored.

The database server cannot use parallel processing for some triggered actions. PDQ is automatically disabled in the FOR EACH ROW section for any DML statement that corresponds to the type of triggering event:
  • SELECT statements in the Action clause of a Select trigger
  • DELETE or MERGE statements in the Action clause of a Delete trigger
  • INSERT or MERGE statements in the Action clause of an Insert trigger
  • UPDATE or MERGE statements in the Action clause of an Update trigger.
The scope of this restriction on PDQ is the FOR EACH ROW section. It has no effect on DML statements in the BEFORE or AFTER sections of the Action clause.

For additional restrictions on INSTEAD OF triggers on views, see Restrictions on INSTEAD OF Triggers on Views.