ISYS 365 - Triggers

1
ISYS 365 - Triggers
2
Agenda

• Triggers
• Review
• Correlation identifiers (pseudo records)
• Restrictions on triggers
• Trigger usage
• Mutating tables
• Enabling and disabling triggers
• Data Dictionary

3
Triggers Syntax

• CREATE OR REPLACE TRIGGER trigger_Name
• BEFOREAFTER INSERTUPDATE DELETE
• OF column_name ON table_name
• FOR EACH ROW
• WHEN trigger_condition
• DECLARE
• -- variable declaration goes here
• BEGIN
• IF INSERTING THEN
• -- statements here
• ELSIF UPDATING THEN
• -- statements here
• ELSIF DELETING THEN
• -- statements here
• END IF
• END trigger_Name

4
Recommended Naming Convention

• Table_Name_ABIUDSR
• AB AFTER or BEFORE
• IUD INSERT, UPDATE, DELETE
• SR statement-level or row-level
• Example
• Employee_AIUS
• Employee_BUR
• Department_BIUDR

5
Triggers Correlation Identifiers

• Correlation identifiers
• Row-level triggers can access individual column
  values
• old and new (called pseudo records)
• Syntactically treated as records of type
• triggering_tableROWTYPE
• Reference fields within the pseudorecords using
  dot notation (just like implicit records)
• old.salary new.salary

6
Triggers Correlation Identifiers

• old.column_name
• Contains the value prior to the change
• NULL for INSERT statements
• new.column_name
• Contains the value after the change
• NULL for DELETE statements

7
Triggers Correlation Identifiers

• Testing old value against new value
• will only work for UPDATE statement
• old.salary ltgt new.salary
• INSERT has only new value
• DELETE has only old value

8
Example of correlation identifiers

• / Log any changes to employee salary where the
  increase is greater than 10 /
• CREATE OR REPLACE TRIGGER employee_BUR
• BEFORE UPDATE ON employee
• FOR EACH ROW
• WHEN (NEW.amount/OLD.amount gt 1.1)
• BEGIN
• INSERT INTO Employee_Big_Change
• VALUES (NEW.actionDate,OLD.sal,NEW.sal)
• END employee_BUR

9
Triggers Correlation Identifiers
Approach 2
Approach 1
CREATE OR REPLACE TRIGGER employee_BUR BEFORE
UPDATE OF SALARY ON EMPLOYEE FOR EACH ROW WHEN
(OLD.SALARY ltgt NEW.SALARY) DECLARE v_Sal_Differen
ce NUMBER BEGIN -- do something here END
employee_BUR / NOTE When using Approach 2, do
NOT include the colons before the words OLD and
NEW in the WHEN clause.

• CREATE OR REPLACE TRIGGER employee_BUR
• BEFORE UPDATE OF SALARY ON EMPLOYEE
• FOR EACH ROW
• DECLARE
• v_Sal_Difference NUMBER
• BEGIN
• IF old.salary ltgt new.salary THEN
• -- do something here
• END IF
• END employee_BUR
• /

10
Restrictions on Triggers

• Cannot have any transaction control statements
  (e.g. COMMIT, ROLLBACK or SAVEPOINT)
• Cannot call procedures or functions that issue
  any transaction control statements
• Cannot declare any LONG or LONG RAW variables
• new and old cannot refer to LONG or LONG RAW
  column

11
Trigger Usage Summary data use a
statement-level trigger

• CREATE OR REPLACE TRIGGER patient_AIDUS
• AFTER INSERT OR DELETE OR UPDATE ON patient
• DECLARE
• CURSOR c_stat IS
• SELECT doctor_ID, COUNT() total_patients
• FROM patient
• GROUP BY doctor_ID
• BEGIN
• FOR v_StatRec IN c_stat LOOP
• UPDATE doctor_stats
• SET total_patients v_StatRec.total_patients
• WHERE Doctor_ID v_StatRec.Doctor_ID
• IF SQLNOTFOUND THEN
• INSERT INTO doctor_stats
  VALUES(v_StatRec.Doctor_ID, v_StatRec.total_patien
  ts)
• END IFEND LOOP
• END patient_AIDUS
• /
• Another example would be in an order processing
  system.define the trigger on the orderDetail
  table to update the total on the OrderHeader
  table.

12
Trigger Usage Overriding the values supplied in
an INSERT/UPDATE statement by changing the new
correlation value

• Use BEFORE INSERT or BEFORE UPDATE row-level
  trigger
• CREATE OR REPLACE TRIGGER patient_BIR
• BEFORE INSERT ON PATIENT
• FOR EACH ROW
• BEGIN
• SELECT sequence_patientID.NEXTVAL
• INTO new.patientID
• FROM DUAL
• END patient_BIR
• /
• INSERT INTO patient (Fname, lname) VALUES
  (Bob, Smith)
• CREATE SEQUENCE SEQUENCE_PATIENTID INCREMENT BY 1
  START WITH 1
• / Note that Patient ID is automatically
  generated by the trigger. /

13
Trigger Usage

• Auditing Use AFTER Row-level trigger

14
Auditing Example

• CREATE OR REPLACE TRIGGER CALC_DOCTOR_STATS_AUIDS
  
  
• AFTER INSERT OR UPDATE OR DELETE ON PATIENT
• DECLARE
• v_Dname VARCHAR2(30)
• v_Tot_Patients NUMBER
• CURSOR C_DOCTOR_STATS IS
• SELECT PHY_LNAME, COUNT(SSN)
• FROM PATIENT, PHYSICIAN
• WHERE PHY_ID PRIMARY_PHY_ID
• GROUP BY PHY_LNAME
• BEGIN
• DELETE FROM DOCTOR_STATS -- wipe doctor_stats
  records
• OPEN c_DOCTOR_STATS
• LOOP -- walk through cursor
• FETCH c_DOCTOR_STATS INTO v_Dname,
  v_tot_patients
• EXIT WHEN c_DOCTOR_STATSNOTFOUND
• -- create new doctor stat record for each
  doctor
• INSERT INTO DOCTOR_STATS VALUES(v_Dname,
  v_Tot_Patients)
• END LOOP

15
Using Raise Application Error in Triggers

• CREATE OR REPLACE TRIGGER patient_bds
• BEFORE DELETE ON patient
• DECLARE
• e_weekend_error EXCEPTION
• e_not_supervisor EXCEPTION
• BEGIN
• IF TO_CHAR(SYSDATE, 'DY') 'SAT' OR
• TO_CHAR(SYSDATE, 'DY') 'SUN' THEN
• RAISE e_weekend_error
• END IF
• IF SUBSTR(user, 1, 3) ltgt 'SUP' THEN
• RAISE e_not_supervisor
• END IF
• EXCEPTION
• WHEN e_weekend_error THEN
• RAISE_APPLICATION_ERROR (-20001,
• 'Deletions allowed Mon thru Fri only')
• WHEN e_not_supervisor THEN
• -- INSERT INTO SECURITY_VIOLATIONS
  VALUES(USER,SYSDATE)

16
Exercise

• Suppose we have a Worker table as follows
• worker(workerID, lname, gender, salary,
  commission, deptID)
• Declare a sequence for workerID that begins from
  1.
• Write a trigger that automatically inserts the
  primary key with a sequential number when
  inserting a record in the worker table.

17
Exercise Trigger

• Suppose we have the following two tables
• OrderHeader(OrderID, Odate, CustID, Total)
• Order_Item(OrderID,ItemID, Qty, Subtotal)
• Write a statement-level trigger that updates the
  Total in the orderHeader table with the total
  value of the order_item records whenever an
  insert, update or delete event occurs on the
  order_item table. For any update error, raise an
  exception.

18
Enabling Disabling Triggers

• ALTER TRIGGER trigger_name
• ENABLE DISABLE
• Ex ALTER TRIGGER Employee_BIUR DISABLE
• ALTER TABLE table_name
• ENABLE DISABLE ALL TRIGGERS
• Ex ALTER TABLE emp ENABLE ALL TRIGGERS
• DROP TRIGGER trigger_name
• Ex DROP TRIGGER Employee_BIUR

19
Example

• - In order to disable, substitute DISABLE for
  ENABLE
• - By default, all triggers are enabled when they
  are created
• - When a trigger is disabled, it still exists in
  the DD but is never fired
• - The STATUS column of USER_TRIGGERS contain
  either DISABLED or ENABLED value

20
Dropping Triggers

• Dropping Triggers
• DROP TRIGGER emp_bur
• Trigger Dependencies
• - When a table is dropped, all triggers for that
  table are deleted from the DD
• ( When a table is dropped, all packages are
  disabled)

21
Data Dictionary

• user_triggers view
• trigger_type, table_name, triggering_event,
  status
• To list all of the triggers that you have
  created
• SELECT trigger_type, table_name,
  triggering_event, status
• FROM user_triggers
• Other views that list triggers
• all_triggers ? list triggers that are accessible
  to current user (but might be owned by a
  different user)
• dba_triggers ? list all triggers in the database

22
USER_TRIGGERS

• SQLgt select trigger_name, trigger_type, status
  from user_triggers
• TRIGGER_NAME TRIGGER_TYPE
  STATUS
• ------------------------------ ----------------
  --------
• LOGEMPCHANGE AFTER EACH ROW
  ENABLED
• SQLgt select trigger_name, table_name from
  user_triggers
• TRIGGER_NAME TABLE_NAME
• ------------------------------ -------------------
  -----------
• LOGEMPCHANGE EMPLOYEE
• SQLgt select description from user_triggers
• DESCRIPTION
• --------------------------------------------------
  ------------------------------
• LogEmpChange
• AFTER INSERT OR DELETE OR UPDATE ON
  Employee
• FOR EACH ROW

23
Mutating Tables

• A table is mutating when
• It is being modified by a DML statement (the
  table on which the trigger is defined)
• It is being updated to enforce DELETE CASCADE
  constraints
• It is being read to enforce referential integrity
  (RI) constraints
• Constraining table
• A table that may need to be read from for RI

24
Mutating Tables

• SQL statements in Row-level trigger may NOT
• Read from or modify any mutating table
• Read from or modify the PK, UNIQUE or FK of a
  constraining table. They can, however, modify
  other columns in the constraining table(s).
• Restrictions above apply to
• all Row-level triggers
• Statement-level triggers ONLY when the trigger
  would be fired as a result of DELETE CASCADE
• Single row INSERT statements do not have this
  mutating table problem