Chapter Nineteen Triggers

1
Chapter NineteenTriggers

• Objective
• Understanding Triggers
• Types of Triggers
• Applications of Triggers
• How to work with Triggers

2
Triggers

• Introduction
• PL/SQL programs that run automatically when an
  event occurs
• Triggers are used for
• Maintaining complex integrity constraints
• Auditing information in a table by recording the
  changes made and who made them
• Automatically signaling to other programs that
  action needs to take place.

3
Types of Triggers

• Row-level
• Statement-level
• BEFORE
• AFTER
• INSTEAD OF

4
Types of Events that can have Trigger Code
Attached to Them

• DML
• DDL
• Database events (System Triggers)
• INSTEAD OF

5
Types of Triggers

• 1 - DML Triggers
• Fired by a DML statement
• Defined for INSERT, UPDATE, DELETE
• Fired before or after the operation
• Fired on row or statement operations

6
Example

• CREATE TABLE Student_Stat( Major
  VARCHAR2(20),Total_cr NUMBER,
• Total_Student NUMBER)
• CREATE OR REPLACE TRIGGER exam1
• AFTER INSERT OR DELETE OR UPDATE ON Students
• DECLARE
• CURSOR S_Cursor IS
• SELECT Major, count() Total_Student, sum(cr)
  Total_cr
• FROM Students
• GROUP BY Major
• BEGIN
• DELETE FROM student_Stat
• FOR I IN S_Cursor LOOP
• INSERT INTO Student_Stat (Major, Total_Student,
  Total_cr)
• VALUES (I.Major, I.Total_Student, I.Total_cr)
• END LOOP
• END

7
Creating DMLTriggers

• Syntax
• CREATE OR REPLACE TRIGGER TriggerName BEFORE
  AFTER
• INSERT DELETE UPDATE OF columns
• ON tableName
• FOR EACH ROW
• WHEN ()
• DECLARE
• Body

8
Example

• CREATE TABLE date_Stat(
• User_id VARCHAR(30),
• Object_Type VARCHAR2(20),
• Object_Name VARCHAR2(30),
• Object_Owner VARCHAR2(30),
• Creation_Date Date)
• CREATE OR REPLACE TRIGGER DD_Stat
• AFTER CREATE ON SCHEMA
• BEGIN
• INSERT INTO date_Stat (User_id, Object_Type,
  Object_Name, Object_Owner,
• Creation_Date)
• VALUES (USER, SYS.DICTIONARY_OBJ_TYPE,
• SYS.DICTIONARY_OBJ_NAME, SYS.DICTIONARY_OBJ_OW
  NER, SYSDATE)
• END

9
Creating DML Triggers

• Possible types of triggers

10
Order of DML Trigger Firing

• Execute the before Statement_level Trigger
• For each row
• Execute the before row_level triggers
• Execute the statement itself
• Execute the after row_level
• Execute the after statement_level triggers

11
Example

• CREATE SEQUENCE Trig_Seq
• START WITH 1
• INCREMENT BY 1
• CREATE OR REPLACE PACKAGE Trig_Pack AS
• V_counter NUMBER
• END
• CREATE OR REPLACE TRIGGER classBase_Trig
• BEFORE UPDATE ON classes
• BEGIN
• Trig_Pack.V_counter 0
• INSERT INTO Temp (Num_col, Char_col)
• VALUES (Trig_Seq.NEXTVAL, Before statement
  counter
• Trig_Pack.V_counter)
• Trig_Pack.V_counter Trig_Pack.V_counter 1
• END

12
Example(Continued)

• CREATE OR REPLACE TRIGGER class1_Trig
• AFTER UPDATE ON classes
• BEGIN
• INSERT INTO Temp (Num_col, Char_col)
• VALUES (Trig_Seq.NEXTVAL, After statement 1
• counter Trig_Pack.V_counter)
• Trig_Pack.V_counter Trig_Pack.V_counter1
• END

13
Example(Continued)

• CREATE OR REPLACE TRIGGER class2_Trig
• AFTER UPDATE ON classes
• BEGIN
• INSERT INTO Temp (Num_col, Char_col)
• VALUES (Trig_Seq.NEXTVAL, After Row 2
• counter Trig_Pack.V_counter)
• Trig_Pack.V_counter Trig_Pack.V_counter1
• END

14
Example(Continued)

• CREATE OR REPLACE TRIGGER classBRow1_Trig
• BEFORE UPDATE ON classes
• FOR EACH ROW
• BEGIN
• INSERT INTO Temp (Num_col, Char_col)
• VALUES (Trig_Seq.NEXTVAL, Before Row 1
• counter Trig_Pack.V_counter)
• Trig_Pack.V_counter Trig_Pack.V_counter1
• END

15
Example(Continued)

• CREATE OR REPLACE TRIGGER classBRow2_Trig
• BEFORE UPDATE ON classes
• FOR EACH ROW
• BEGIN
• INSERT INTO Temp (Num_col, Char_col)
• VALUES (Trig_Seq.NEXTVAL, Before Row 2
• counter Trig_Pack.V_counter)
• Trig_Pack.V_counter Trig_Pack.V_counter1
• END

16
Example(Continued)

• CREATE OR REPLACE TRIGGER classBRow3_Trig
• BEFORE UPDATE ON classes
• FOR EACH ROW
• BEGIN
• INSERT INTO Temp (Num_col, Char_col)
• VALUES (Trig_Seq.NEXTVAL, Before Row 3
• counter Trig_Pack.V_counter)
• Trig_Pack.V_counter Trig_Pack.V_counter1
• END

17
Example(Continued)

• CREATE OR REPLACE TRIGGER classARow_Trig
• AFTER UPDATE ON classes
• FOR EACH ROW
• BEGIN
• INSERT INTO Temp (Num_col, Char_col)
• VALUES (Trig_Seq.NEXTVAL, After Row
• counter Trig_Pack.V_counter)
• Trig_Pack.V_counter Trig_Pack.V_counter1
• END

18
Example(Continued)

• UPDATE classes
• SET Num_cr 4
• WHERE dept IN (COSC, MATH)

19
Example(Continued)

• SQLgt SELECT
• FROM Temp
• ORDER BY num_col

20
Example(Continued)
21
NEW OLD

• When a row level Trigger is fired, two data
  structures (like records) are produced
• NEW contains new values
• OLD stores original values
• Structure is the same as ROWTYPE in records
• No OLD structure for INSERT operation
• No NEW structure for DELETE operation
• ROWID is the same for NEW OLD

22
NEW OLD

• Field of OLD can not be changed
• OLD NEW structure cannot be used as a function
  or procedure parameters (a field of OLD NEW can
  be used)
• Use Prefix colon if you use this in a block
• OLD
• NEW
• Cannot perform the record_level operations

23
NEW OLD

• Kind of bind variable
• Known as pseudocode
• Trigger_TableROWTYPE
• Referenced by
• NEW.field
• OLD.field

24
Row level Triggers
25
Example of DMLTriggers

• CREATE or REPLACE TRIGGER check_faculty
• AFTER INSERT or UPDATE
• ON faculty
• FOR EACH ROW
• BEGIN
• Check_degree(NEW.Type_Degree)
• Check_degree_year(NEW.D_Year)
• END

26
Example of DMLTriggers

• CREATE or REPLACE TRIGGER student_major
• AFTER INSERT or UPDATE
• ON student
• FOR EACH ROW
• BEGIN
• INSERT INTO student_log
• VALUES(NEW.Name, OLD.Major, NEW.Major,
  SYSDATE)
• COMMIT
• END

27
Example of DMLTriggers

• CREATE OR REPLACE TRIGGER student_major
• AFTER UPDATE
• ON student
• FOR EACH ROW
• WHEN ((OLD.Major ! NEW.Major) OR
• (OLD.Major IS NULL AND NEW.Major IS NULL))
• BEGIN
• UPDATE student_log
• VALUES ( ..
• WHERE (
• COMMIT
• END

28
Example of Using OLD NEW

• CREATE OR REPLACE TRIGGER Generate_ID
• BEFORE INSERT OR UPDATE ON Students
• FOR EACH ROW
• BEGIN
• SELECT Student_sequence.NEXTVAL
• INTO NEW.id
• FROM DUAL
• END
• ..
• INSERT INTO Students (First, Last)
• VALUES (Shawn, Sham)
• INSERT INTO Students (id, First, Last)
• VALUES (-2, Shawn, Sham)

29
Pseudorecodes

• CREATE OR REPLACE TRIGGER Temp_Trig
• BEFORE DELETE ON Temp
• FOR EACH ROW
• DECLARE
• V_Temp TempROWTYPE
• BEGIN
• .
• V_Temp OLD -- not legal

30
Referencing Clause

• REFERENCING OLD AS oldname
• NEW AS newname
• Example
• CREATE OR REPLACE TRIGGER Grad_id
• BEFORE INSERT OR UPDATE ON Students
• REFERENCING NEW AS new_student
• FOR EACH ROW
• BEGIN
• SELECT Student_Seq.NEXTVAL
• INTO new_student.ID
• FROM DUAL
• END

31
WHEN Clause

• Syntax
• WHEN Trigger_condition
• Example
• CREATE OR REPLACE TRIGGER ch_credit
• BEFORE INSERT OR UPDATE OF current_cr ON
  Students
• FOR EACH ROW
• WHEN (NEW.current_cr gt 50)
• BEGIN
• .
• END

32
Trigger Predicate
33
Example

• CREATE OR REPLACE TRIGGER New_grade
• BEFORE INSERT OR DELETE OR UPDATE ON Student_Reg
• FOR EACH Row
• DECLARE
• V_Change CHAR(1)
• BEGIN
• IF INSERTING THEN
• V_Change I
• ELSIF DELETING THEN
• V_Change D
• ELSE
• V_Change U
• END IF

34
2 DDL Triggers

• Create or modify database object such as Index or
  table
• CREATE TABLE
• ALTER TABLE
• CREATE INDEX
• CREATE TRIGGER
• DROP TRIGGER

35
DDL Trigger Syntax

• CREATE OR REPLACE TRIGGER name
• BEFORE AFTER DDL event
• ON DATABASE SCHEMA
• DECLARE
• ..
• BEGIN
• ..
• END

36
Example 1

• CREATE OR REPLACE TRIGGER example1
• AFTER CREATE ON SCHEMA
• BEGIN
• DBMS_OUTPUT.PUT_LINE(A ORA_DICT_OBJ_TYPE
  is created called ORA_DICT_OBJ_NAME)
• END

37
Example 1 (continued)

• SET SERVEROUTPUT ON
• CREATE TABLE student (Name VARCHAR2(50),
• GPA NUMBER)
• CREATE INDEX stud_index ON student (Name)
• CREATE FUNCTION stud_func (IN a VARCHAR2)
  RETURN NUMBER
• AS
• DECLARE Temp NUMBER
• BEGIN
• SELECT GPA INTO Temp
• FROM student
• WHERE Name UPPER(a)
• RETURN Temp
• END

38
Example 1 (continued)

• Output
• A TABLE is created called STUDENT
• A INDEX is created called STUD_INDEX
• A FUNCTION is created called STUD_FUNC

39
DDL Trigger Event and Attribute Functions

• ORA_SYSEVENT
• ORA_DES_ENCRYPTED_PASSWORD
• ORA_DICT_OBJ_TYPE
• ORA_DICT_OBJ_NAME
• ORA_DICT_OBJ_OWNER
• ORA_DATABASE_NAME
• ORA_LOGIN_USER
• ORA_IS_DROP_COLUMN(col_name)
• ORA_IS_ALTER_COLUMN(col_name)

40
3- Database Event Trigger Syntax(System Triggers)

• CREATE OR REPLACE TRIGGER name
• BEFOREAFTER
• Database event
• ON TABLE SCHEMA
• DECLARE
• BEGIN
• .
• END

41
System Triggers
42
Common Sense Use of Triggers

• BEFORE STARTUP
• AFTER SHUTDOWN
• BEFORE LOGON
• AFTER LOGOFF
• BEFORE SERVEREERROR

43
Example 1

• CREATE OR REPLACE TRIGGER collectData
• BEFORE SHUTDOWN ON DATABASE
• BEGIN
• call_your_procedure
• END

44
Example 2

• CREATE OR REPLACE TRIGGER cal_error
• AFTER SERVERERROR ON SCHEMA
• DECLARE
• s_ErrorNum NUMBER
• s_date DATE SYSDATE
• s_counter NUMBER 1
• BEGIN
• LOOP --use error stack
• s_ErrorNum ORA_SERVER_ERROR(s_counter)
• EXIT WHEN s_ErrorNum 0
• DBMS_OUTPUT.PUT_LINE(USER TO_CHAR(s_ErrorNum)
  TO_CHAR(s_counter) TO_CHAR(s_Date))
• s_counter s_counter 1
• END LOOP
• END

45
Example

• FOR errorNum IN 20000 .. 20050
• LOOP
• IF ORA_IS_SERVERERROR(errorNum) THEN
• ..
• END IF
• END LOOP

46
Alter Delete Triggers

• DROP TRIGGER name
• ALTER TRIGGER name DISABLE ENABLE
• Example
• SQLgt ALTER TRIGGER grad_student DISABLE
• SQLgt ALTER TABLE Students DISABLE ALL TRIGGERS

47
Data Dictionary Triggers

• USER_TRIGGERS
• TRIGGER_TYPE
• TRIGGER_EVENT
• TRIGGER_NAME

48
4-INSTEAD OF TRIGGERS

• Control INSERT, DELETE, UPDATE operation on views
• Defined on view
• Fired at row level

49
INSTEAD OF TRIGGERS

• Syntax
• CREATE OR REPLACE TRIGGER name INSTEAD OF
  operation ON View_Name FOR EACH ROW
• BEGIN
• ..
• END

50
Example

• CREATE OR REPLACE VIEW class_room AS
• SELECT dept, course, building, room_no
• FROM Room, Classes
• WHERE Room.id Classes.id
• CREATE TRIGGER class_room_Insert INSTEAD OF
  INSERT ON class_room
• DECLARE
• V_room_id Room.idTYPE
• BEGIN
• SELECT id INTO V_room_id
• FROM Room
• WHERE building New.building AND Room_no
  New.room_no
• UPDATE Classes
• SET id V_room.id
• WHERE dept New.dept AND Course New.course
• END

51
Data Dictionary Views

• DBA_TRIGGERS
• ALL_TRIGGERS
• USER_TRIGGERS

52

• TRIGGER_NAME
• TRIGGER_TYPE
• TRIGGER_EVENT
• TABLE_OWNER
• BASE_OBJECT_TYPE
• TABLE_NAME
• WHEN_CLAUSE
• STATUS
• TRIGGER_BODY

53
Example

• SELECT Object_Name,
• Object_Type,
• Status
• FROM USER_OBJECTS
• WHERE Object_Name INVALID_TRIGGER

54
Multiple Triggers of the Same Type

• CREATE or REPLACE TRIGGER status_trigger
• BEFORE INSERT ON student_status
• FOR EACH ROW
• BEGIN
• IF NEW.Total_Cr lt 30 THEN
• NEW.Status Freshman
• ELSIF NEW.Total_Cr gt 30 AND NEW.Total_Cr lt 60
  THEN
• NEW.Status Sophmore
• ELSIF NEW.Total_Cr gt 60 AND NEW.Total_Cr lt 90
  THEN
• NEW.Status Junior
• ELSE
• NEW.Status Senior
• END