Specifying the Trigger Event

The trigger event is the type of statement that activates the trigger. When a statement of this type is performed on the table, the database server executes the SQL statements that make up the triggered action. The trigger event can be an INSERT, SELECT, DELETE, or UPDATE statement. When you define an UPDATE or SELECT trigger event, you can name one or more columns in the table to activate the trigger. If you do not name any columns, then an update or SELECT of any column in the table activates the trigger. You can create only one INSERT and one DELETE trigger per table, but you can create multiple UPDATE or SELECT triggers as long as the triggering columns are mutually exclusive.

You can only create a trigger on a table in the current database. Triggers cannot reference a remote table.

In the following excerpt of a CREATE TRIGGER statement, the trigger event is defined as an update of the quantity column in the items table:

CREATE TRIGGER upqty
UPDATE OF quantity ON items     -- an UPDATE trigger event

This portion of the statement identifies the table on which you create the trigger. If the trigger event is an insert or delete, only the type of statement and the table name are required, as the following example shows:

CREATE TRIGGER ins_qty
INSERT ON items             -- an INSERT trigger event