“Ideas pull the trigger, but instinct loads the gun.”
- Don Maquis
A Trigger is an Event driven maintenance operation (Object Type 'G')
The event is caused (Trigger Fired) by one or more modifications to a table
The Original Modification, the TRIGGER, and all subsequent triggers constitute a Single Transaction
The SQL and all Triggers associated must each work or all are Rolled Back
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)
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.
CREATE TABLE sql01.Trig_Table_XYZ
(UserUpdating Char(30)
,DateTime Timestamp
,Employee_No Integer
,Oldsal decimal(10,2)
,newsal decimal(10,2)
)Primary index(Employee_No);
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.
The trigger worked perfectly.
CREATE TRIGGER SQL01.AuditCheck_XYZ
AFTER UPDATE OF (Salary) ON SQL01.Employee_XYZ
FOR EACH STATEMENT
(INSERT INTO SQL01.Aud_Tbl_XYZ
VALUES(USER, CURRENT_TIMESTAMP););
UPDATE SQL01.Employee_XYZ
SET salary = Salary * 1.1 ;
SELECT *
FROM SQL01.Aud_Tbl_XYZ;
Does AuditCheck Fire for each row updated or once per statement? It fires once for the statement.
The trigger worked perfectly.
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.