Chapter 33
Trigger Functions
“Ideas pull the trigger, but instinct loads the gun.”
Table of Contents Chapter 33 – Trigger Functions
– The Fundamentals of Triggers
– FOR EACH STATEMENT vs. FOR EACH ROW
– Using ORDER when Similar Triggers Exist
The Fundamentals of Triggers
1) A Trigger is an Event driven maintenance operation (Object Type ‘G’)
2) The event is caused (Trigger Fired) by one or more modifications to a table
3) The Original Modification, the TRIGGER, and all subsequent triggers constitute a Single Transaction
4) The SQL and all Triggers associated must each work or all are Rolled Back
5) The user's initial SQL maintenance request that causes a row to change in a table and then causes a trigger to fire (execute) can be:
INSERT, UPDATE, DELETE or INSERT/SELECT
(SELECT will NOT work to fire a Trigger)
6) Once a Trigger fires it can perform SQL such as INSERT, UPDATE, DELETE, INSERT/SELECT, ABORT/ROLLBACK, EXEC
Triggers fire automatically when a Triggering Event occurs. A Trigger can even fire and EXEC a Macro.
CREATING A Trigger
CREATE TABLE EmpLog_Table
(UserUpdating Char(30)
,DateTime Timestamp
,Employee_No Integer
,Oldsal decimal(10,2)
,newsal decimal(10,2)
)Primary index(Employee_No);
CREATE TRIGGER EmpRaise
AFTER UPDATE OF (Salary) ON Employee_Table
REFERENCING OLD AS oldrow NEW AS newrow
FOR EACH ROW
(INSERT INTO EmpLog_Table
VALUES (USER, CURRENT_TIMESTAMP,
oldrow.employee_no, oldrow.salary, newrow.salary););
The Table EmpLog_Table needs to exist before the Trigger EmpRaise can successfully be created. The great thing about a trigger is that users can see the OLD Row and the New Row after the UPDATE and both can be recorded for auditing purposes. Above is an AFTER Trigger because it fires AFTER an UPDATE, vs. some triggers that fire just BEFORE a change happens.
You can also have a Trigger fire once for an event or fire for every Row that changes. The statement FOR EACH ROW specifies to fire for every ROW change.
FOR EACH STATEMENT vs. FOR EACH ROW
CREATE TABLE Audit_Table CREATE TRIGGER AuditCheck
(UserUpdating Char(30) AFTER UPDATE OF (Salary) ON Employee_Table
,DateTime Timestamp FOR EACH statement
)Primary index(UserUpdating); (INSERT INTO Audit_Table
VALUES(USER, CURRENT_TIMESTAMP););
UPDATE Employee_Table
SET salary = Salary * 1.1
Does AuditCheck Fire for each row updated or once per statement? For each statement.
Using ORDER when Similar Triggers Exist
CREATE TRIGGER INSTrig1
AFTER INSERT ON CasTbl_1
ORDER 100
REFERENCING NEW AS afterrow
FOR EACH ROW
( INSERT INTO CasTbl_2 values
(afterrow.col1+1, beforerow.col2*2) ; );
CREATE TRIGGER INSTrig2
AFTER INSERT ON CasTbl_1
ORDER 200
REFERENCING NEW AS afterrow
FOR EACH ROW
( INSERT INTO CasTbl_3 values
(afterrow.col1+2, beforerow.col2*4) ; );
When two different triggers implemented on the same table (that are similar) and they don't have an ORDER statement you can't tell which will fire first (random). In the example above which Trigger will fire first? The one with ORDER 100, not 200.