The CREATE TRIGGER statement defines a trigger in the database.
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Group privileges are not considered for any table or view specified in the CREATE TRIGGER statement.
To replace an existing trigger, the authorization ID of the statement must be the owner of the existing trigger (SQLSTATE 42501).
>>-CREATE--+------------+--TRIGGER--trigger-name----------------> '-OR REPLACE-' .-NO CASCADE-. >--+-+------------+--BEFORE-+--| trigger-event |----------------> +-AFTER------------------+ '-INSTEAD OF-------------' >--ON--+-table-name-+-------------------------------------------> '-view-name--' >--+----------------------------------------------------------------+--> | .-----------------------------------------------. | | V (1) (2) .-AS-. | | '-REFERENCING----------------+-OLD--+----+--correlation-name-+-+-' | .-AS-. | +-NEW--+----+--correlation-name-+ | .-AS-. | +-OLD TABLE--+----+--identifier-+ | .-AS-. | '-NEW TABLE--+----+--identifier-' >--+-FOR EACH ROW-------------+--| triggered-action |---------->< | (3) | '-------FOR EACH STATEMENT-' trigger-event .-OR--------------------------------------. V (4) | |----+-INSERT--------------------------+-----+------------------| +-DELETE--------------------------+ '-UPDATE--+---------------------+-' | .-,-----------. | | V | | '-OF----column-name-+-' triggered-action |--+------------------------------------+-----------------------> | (5) | '-------WHEN--(--search-condition--)-' >--+--------+--| SQL-procedure-statement |----------------------| '-label:-' SQL-procedure-statement |--+-CALL----------------------------------------------+--------| | (6) | +-Compound SQL (compiled)---------------------------+ +-Compound SQL (inlined)----------------------------+ +-FOR-----------------------------------------------+ +-+-----------------------------------+--fullselect-+ | | .-,-----------------------. | | | | V | | | | '-WITH----common-table-expression-+-' | +-GET DIAGNOSTICS-----------------------------------+ +-IF------------------------------------------------+ +-INSERT--------------------------------------------+ +-ITERATE-------------------------------------------+ +-LEAVE---------------------------------------------+ +-MERGE---------------------------------------------+ +-searched-delete-----------------------------------+ +-searched-update-----------------------------------+ +-SET Variable--------------------------------------+ +-SIGNAL--------------------------------------------+ +-FOR-----------------------------------------------+ '-WHILE---------------------------------------------'
The trigger event predicates DELETING, INSERTING, and UPDATING can be specified anywhere inside the triggered action. If the triggered action does not contain any compound SQL (compiled), then no event predicate can be specified (42601).
The WHEN clause cannot be specified for INSTEAD OF triggers (SQLSTATE 42613).
A reference to a transition variable with an XML data type can be used only in a VALIDATED predicate.
Only the FOR statement, WHILE statement, and the compound SQL statement can include a label.
The triggered action of a BEFORE trigger on a column of type XML can invoke the XMLVALIDATE function through a SET statement, leave values of type XML unchanged, or assign them to NULL using a SET statement.
The SQL-procedure-statement must not contain a statement that is not supported (SQLSTATE 42987).
The SQL-procedure-statement cannot reference an undefined transition variable (SQLSTATE 42703), a federated object (SQLSTATE 42997), or a declared temporary table (SQLSTATE 42995).
In its simplest form, this means that a BEFORE DELETE trigger on a table with a self-referencing referential constraint and a delete rule of CASCADE should not include any references to the table in the triggered-action.
In practical terms, an inoperative trigger is one in which a trigger definition has been dropped as a result of cascading rules for DROP or REVOKE statements. For example, when a view is dropped, any trigger with an SQL-procedure-statement that contains a reference to that view is made inoperative.
When a trigger is made inoperative, all packages with statements performing operations that were activating the trigger will be marked invalid. When the package is rebound (explicitly or implicitly) the inoperative trigger is completely ignored. Similarly, applications with dynamic SQL statements performing operations that were activating the trigger will also completely ignore any inoperative triggers.
The trigger name can still be specified in the DROP TRIGGER and COMMENT ON TRIGGER statements.
An inoperative trigger may be recreated by issuing a CREATE TRIGGER statement using the definition text of the inoperative trigger. This trigger definition text is stored in the TEXT column of the SYSCAT.TRIGGERS catalog view. Note that there is no need to explicitly drop the inoperative trigger in order to recreate it. Issuing a CREATE TRIGGER statement with the same trigger-name as an inoperative trigger will cause that inoperative trigger to be replaced with a warning (SQLSTATE 01595).
Inoperative triggers are indicated by an X in the VALID column of the SYSCAT.TRIGGERS catalog view.
The SQL-procedure-statement could include a SIGNAL SQLSTATE statement or a RAISE_ERROR function. In both these cases, the SQLSTATE returned is the one specified in the SIGNAL SQLSTATE statement or the RAISE_ERROR condition.
When a trigger is activated, its transition variables (OLD, NEW, OLD TABLE and NEW TABLE) may contain rows of subtables. However, they will contain only columns defined on the table to which they are attached.
CREATE TRIGGER NEW_HIRED
AFTER INSERT ON EMPLOYEE
FOR EACH ROW
UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1
CREATE TRIGGER FORMER_EMP
AFTER DELETE ON EMPLOYEE
FOR EACH ROW
UPDATE COMPANY_STATS SET NBEMP = NBEMP - 1
The trigger will interact with the PARTS table with these columns: PARTNO, DESCRIPTION, ON_HAND, MAX_STOCKED, and PRICE.
CREATE TRIGGER REORDER
AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
REFERENCING NEW AS N
FOR EACH ROW
WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED)
BEGIN ATOMIC
VALUES(ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO));
END
CREATE TRIGGER REORDER
AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
REFERENCING NEW TABLE AS NTABLE
FOR EACH STATEMENT
BEGIN ATOMIC
SELECT ISSUE_SHIP_REQUEST(MAX_STOCKED - ON_HAND, PARTNO)
FROM NTABLE
WHERE (ON_HAND < 0.10 * MAX_STOCKED);
END
CREATE TRIGGER RAISE_LIMIT
AFTER UPDATE OF SALARY ON EMPLOYEE
REFERENCING NEW AS N OLD AS O
FOR EACH ROW
WHEN (N.SALARY > 1.1 * O.SALARY)
SIGNAL SQLSTATE '75000' SET MESSAGE_TEXT='Salary increase>10%'
Tables: CURRENTQUOTE (SYMBOL, QUOTE, STATUS)
QUOTEHISTORY (SYMBOL, QUOTE, QUOTE_TIMESTAMP)
When
the QUOTE column of CURRENTQUOTE is updated, the new quote should
be copied, with a timestamp, to the QUOTEHISTORY table. Also, the
STATUS column of CURRENTQUOTE should be updated to reflect whether
the stock is: CREATE TRIGGER STOCK_STATUS
NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
FOR EACH ROW
BEGIN ATOMIC
SET NEWQUOTE.STATUS =
CASE
WHEN NEWQUOTE.QUOTE >
(SELECT MAX(QUOTE) FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'High'
WHEN NEWQUOTE.QUOTE <
(SELECT MIN(QUOTE) FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(QUOTE_TIMESTAMP) = YEAR(CURRENT DATE) )
THEN 'Low'
WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
THEN 'Rising'
WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
THEN 'Dropping'
WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
THEN 'Steady'
END;
END
CREATE TRIGGER RECORD_HISTORY
AFTER UPDATE OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO QUOTEHISTORY
VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);
END
CREATE TRIGGER LOCATION_TRIGGER
NO CASCADE
BEFORE UPDATE ON ORG
REFERENCING
OLD AS PRE
NEW AS POST
FOR EACH ROW
WHEN (POST.LOCATION = 'Toronto')
SET POST.LOCATION = 'Los Angeles';
END
CREATE TRIGGER NEWPROD NO CASCADE BEFORE INSERT ON PRODUCT
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
SET (N.DESCRIPTION) = XMLVALIDATE(N.DESCRIPTION
ACCORDING TO XMLSCHEMA ID product);
END