Define a trigger.

 

Syntax

 

<trigger definition> ::=

 

 

 

CREATE TRIGGER [ schema-name. ] trigger-name

{ BEFORE | AFTER } <trigger event> [ { , <trigger event> }... ]

ON [ schema-name. ] table-name [ REFERENCING <transition variable>... ]

[ DESCRIPTION <character string literal> ]

[ AS ] <triggered action>

 

<trigger event> ::=

 

|

|

 

INSERT

DELETE

UPDATE [ OF <trigger column list> ]

 

<trigger column list> ::= column-name [ { , column-name }... ]

 

<transition variable> ::=

 

|

 

OLD [ ROW ] [ AS ] correlation-name

NEW [ ROW ] [ AS ] correlation-name

 

<triggered action> ::=

 

 

 

[ FOR EACH { ROW } ]

[ WHEN ( <search condition> ) ]

<triggered SQL statement>

 

<triggered SQL statement> ::= <SQL procedure statement>

 

 

Usage

 

The CREATE TRIGGER statement creates a trigger that is stored in the database and associated with the subject table identified by table-name.

 

 

Notes

 

The schema and trigger names shall conform to the format rules for identifiers and schema-qualified names in NexusDB SQL. The trigger is created in the database specified by schema-name, which must be the same database as where the associated table is stored. The current database is implicit if a schema name is not specified.



 

The subject table of the trigger definition shall be a persistent base table.



 

The BEFORE and AFTER keywords define whether the trigger is fired immediately before or immediately after the effects of the triggering SQL statement are applied to the table.



 

The trigger event specifies which data-change statements that will cause the trigger to fire. The trigger event can specify INSERT, DELETE or UPDATE, or any combination of the event types separated by a comma. If the optional OF clause is specified for an UPDATE event, then the event is only triggered when the specified columns are updated.



 

The REFERENCING clause can be specified to give correlation names to the OLD and NEW transition variables. OLD is a reference to the original row values that existed before an UPDATE or DELETE operation. NEW is a reference to the row values that will exist after an INSERT or UPDATE operation. The NEW variable can be used in a BEFORE trigger to assign column values to the new or modified row, else both variables are read-only.



 

DESCRIPTION is a free text attribute used to store a comment in the trigger descriptor.



 

FOR EACH ROW, which is implicit if not specified, means that the trigger is fired for each row being processed by a data-change statement.



 

The optional WHEN clause is used to specify a condition that must evaluate to true before the triggered SQL statement is executed.



 

The triggered SQL statement can be any valid SQL procedure statement, including the Compound statement that allows multiple SQL statements to be specified.



 

The special INSERTING, UPDATING and DELETING predicates can be referenced in the WHEN clause or the triggered SQL statement to check the current trigger event type.



 

 

Examples

 

1)

The following example creates a trigger that deletes related rows in the order_details table after a row in the orders table has been deleted:



 


CREATE TRIGGER orders_after_delete


AFTER DELETE ON orders


WHEN ( DELETING )


DELETE FROM order_details WHERE orderID = OLD.orderID;



 

2)

The following example creates a multi-event trigger that stores information about changes to the enrolls table in the enrolls_log table:



 


CREATE TRIGGER enrolls_changes


AFTER INSERT, DELETE, UPDATE ON enrolls


REFERENCING OLD AS o NEW AS n


BEGIN




IF INSERTING THEN




INSERT INTO enrolls_log ( action, n1, n2, n3, n4, stamp )


VALUES ( 'Insert', n.courseID, n.sectionID, n.studentID, n.grade, CURRENT_TIMESTAMP );




ELSEIF UPDATING THEN




INSERT INTO enrolls_log ( action, o1, n1, o2, n2, o3, n3, o4, n4, stamp )


VALUES ('Update', o.courseID, n.courseID, o.sectionID, n.sectionID,


o.studentID, n.studentID, o.grade, n.grade, CURRENT_TIMESTAMP );




ELSEIF DELETING THEN




INSERT INTO enrolls_log ( action, o1, o2, o3, o4, stamp )


VALUES ( 'Delete', o.courseID, o.sectionID, o.studentID, o.grade, CURRENT_TIMESTAMP );




END IF;




END



 

 


 

Conformance

 

SQL:2003 standard

 

NexusDB extensions

-

 

-

-

-

Feature T211 "Basic trigger capability"

 

DESCRIPTION

More than one trigger event can be specified

AS clause before the triggered action specification.