|
The syntax for trigger operation conditions is as follows:
trigger-operation:
INSERTING | DELETING
| UPDATING [ ( column-name-string ) ] | UPDATE( column-name )
Trigger-operation conditions can be used only in triggers, to carry out actions depending on the kind of action that caused the trigger to fire.
The argument for UPDATING is a quoted string (for example, UPDATING( 'mycolumn' )). The argument for UPDATE is an identifier (for example, UPDATE( mycolumn )). The two versions are interoperable, and are included for compatibility with SQL dialects of other vendors' DBMS.
If you supply an UPDATING or UPDATE function, you must also supply a REFERENCING clause in the CREATE TRIGGER statement to avoid syntax errors.
The following trigger displays a message showing which action caused the trigger to fire.
CREATE TRIGGER tr BEFORE INSERT, UPDATE, DELETE
ON sample_table
REFERENCING OLD AS t1old
FOR EACH ROW
BEGIN
DECLARE msg varchar(255);
SET msg = 'This trigger was fired by an ';
IF INSERTING THEN
SET msg = msg || 'insert'
ELSEIF DELETING THEN
set msg = msg || 'delete'
ELSEIF UPDATING THEN
set msg = msg || 'update'
END IF;
MESSAGE msg TO CLIENT
END