|
Define a trigger.
Syntax
<trigger definition> ::=
<trigger event> ::=
<trigger column list> ::= column-name [ { , column-name }... ]
<transition variable> ::=
|
|
|
|
OLD [ ROW ] [ AS ] correlation-name
NEW [ ROW ] [ AS ] correlation-name
|
<triggered action> ::=
<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 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.
|
|
