Chapter 20 – Trigger Functions

“Ideas pull the trigger, but instinct loads the gun.”

- Don Maquis

The Fundamentals of Triggers

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.

CREATING A Trigger

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);

image

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.

Checking To See If the Trigger Works

image

The trigger worked perfectly.

FOR EACH STATEMENT vs. FOR EACH ROW

image

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.

Checking To See If Our New Trigger Worked

image

The trigger worked perfectly.

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset