|
Each row in the SYSTRIGGER system view describes one trigger in the database. This view also contains triggers that are automatically created for foreign key definitions which have a referential triggered action (such as ON DELETE CASCADE). The underlying system table for this view is ISYSTRIGGER.
| Column name | Column type | Column constraint |
|---|---|---|
| trigger_id | UNSIGNED INT | NOT NULL |
| table_id | UNSIGNED INT | NOT NULL |
| object_id | UNSIGNED BIGINT | NOT NULL |
| event | CHAR(1) | NOT NULL |
| trigger_time | CHAR(1) | NOT NULL |
| trigger_order | SMALLINT | |
| foreign_table_id | UNSIGNED INT | |
| foreign_key_id | UNSIGNED INT | |
| referential_action | CHAR(1) | |
| trigger_name | CHAR(128) | |
| trigger_defn | LONG VARCHAR | NOT NULL |
| remarks | LONG VARCHAR | |
| source | LONG VARCHAR |
trigger_id Each trigger is assigned a unique number (the trigger number).
table_id The table ID of the table to which this trigger belongs.
event The event or events that cause the trigger to fire. This single-character value corresponds to the trigger event that was specified when the trigger was created.
A INSERT, DELETE
B INSERT, UPDATE
C UPDATE COLUMNS
D DELETE
E DELETE, UPDATE
I INSERT
U UPDATE
M INSERT, DELETE, UPDATE
trigger_time The time at which the trigger will fire. This single-character value corresponds to the trigger time that was specified when the trigger was created.
A AFTER (row-level trigger)
B BEFORE (row-level trigger)
R RESOLVE
S AFTER (statement-level trigger)
trigger_order The order in which the trigger will fire. This determines the order that triggers are fired when there are triggers of the same type (insert, update, or delete) that fire at the same time (before or after).
foreign_table_id The table number of the table containing a foreign key definition which has a referential triggered action (such as ON DELETE CASCADE).
foreign_key_id The foreign key number of the foreign key for the table referenced by foreign_table_id.
referential_action The action defined by a foreign key. This single-character value corresponds to the action that was specified when the foreign key was created.
C CASCADE
D SET DEFAULT
N SET NULL
R RESTRICT
trigger_name The name of the trigger. One table cannot have two triggers with the same name.
trigger_defn The command that was used to create the trigger.
remarks Remarks about the trigger. This value is stored in the ISYSREMARK system table.
source The SQL source for the trigger. This value is stored in the ISYSSOURCE system table.
PRIMARY KEY (trigger_id)
FOREIGN KEY (object_id) references SYS.ISYSOBJECT (object_id) MATCH UNIQUE FULL
FOREIGN KEY (table_id) references SYS.ISYSTAB (table_id)
FOREIGN KEY (foreign_table_id, foreign_key_id) references SYS.ISYSIDX (table_id, index_id)
UNIQUE (table_id, event, trigger_time, trigger_order)
UNIQUE (trigger_name, table_id)
UNIQUE (table_id, foreign_table_id, foreign_key_id, event)