Oracle Database PLSQL Triggers

by admin

What is a Trigger

  • Is a PL/SQL block or PL/SQL procedure associated with a table, view, schema, or the database
  • Executes implicitly whenever a particular event takes place
  • Can be either :
    • – DML Trigger : Fires whenever an DML event occurs
    • – Non-DML Trigger : Fires whenever any DDL event or Database Event occurs

DML Triggers

  • A triggering statement contains :
  • Trigger timing
    • – for table : before , after
    • – for view : instead of
  • Triggering event : INSERT, UPDATE, or DELETE
  • Table Name : On table or view
  • Trigger Type : Row or Statement
  • WHEN clause : Restricting condition
  • Trigger body : PL/SQL block

Trigger Timing

  • Trigger timing : Specifies the timing of the trigger firing..
  • BEFORE : Execute the trigger body before the triggering DML event on a table.
  • AFTER : Execute the trigger body after the triggering DML event on a table
  • INSTEAD OF : Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.
  • Trigger user event : Specifies the DML statement causing the trigger to execute. It can be :
  • INSERT
  • UPDATE
  • DELETE

Trigger Type

  • Trigger type : Specifies the execution type of trigger . It can be :
  • Statement Trigger:
  • -executes once for the triggering event.
  • -This is the default.
  • – It fires once, even if no rows are affected at all.
  • – useful when action does not depend on the number of rows returned
  • Row Trigger :
  • – executes once for each row affected by triggering event. – It is not executed if the triggering event affects no rows
  • – useful if action depends on data of rows that affected

Trigger Body

  • Trigger body :
  • – The action performed by triggers
  • – It is a PL/SQL block or call to a procedure
Trigger body : 
	- The action performed by triggers
	- It is a PL/SQL block or call to a procedure

Syntax :
		CREATE [OR REPLACE] TRIGGER trigger_name
			timing
			event1 [or event 2 or event3]
				ON table_name
		trigger_body

Example

CREATE OR REPLACE TRIGGER  check_user
	BEFORE INSERT ON emp
BEGIN
	if(USER <> 'SCOTT') then
		raise_application_error(-20003, 'not a valid user');
	end if;
END;


insert into emp (empno) values (1) ; --DML Statement

If the DML Statement is executed with logged in user different than SCOTT, below error will be raised

Example : 

CREATE OR REPLACE TRIGGER DEPT_TRIG
BEFORE INSERT or UPDATE or DELETE on dept
BEGIN
   IF INSERTING  THEN
	 IF (TO_CHAR(SYSDATE,'DY') IN('SAT', 'SUN') )
		THEN RAISE_APPLICATION_ERROR( -20345,'You  can insert data in business days only');
     END IF;
   ELSIF UPDATING THEN
         IF (TO_CHAR(SYSDATE,'DY') IN('SAT', 'SUN') )
		THEN RAISE_APPLICATION_ERROR( -20355, 'You  can change data in business days only');
     END IF;
   ELSIF DELETING THEN
         IF (TO_CHAR(SYSDATE,'DY') NOT IN('SAT', 'SUN') )
	THEN RAISE_APPLICATION_ERROR( -20355, 'You cannot delete data on business day');     
  END IF;
END IF;  
END ; 

delete from dept where deptno=10;

Row Level Trigger

Syntax :
		CREATE [OR REPLACE] TRIGGER trigger_name
			timing
			  event1[OR event2 OR event3]
			 [REFERENCING OLD AS old/NEW AS new ]
		FOR EACH ROW
			[WHEN (condition)]
		trigger_body
  • In row triggers, there are two qualifiers
    • – OLD : that refers to old rows before data change
    • – NEW : that refers to new rows after data change
  • Prefix these qualifiers with a colon(:) everywhere except in WHEN condition
--Table Script
create table EMP_AUDIT_TABLE
 (
    USER_NAME varchar2(100), CDATE date,
    id      number, OLD_SAL number, NEW_SAL number
  ) 

--Example
set DEFINE off;
CREATE OR REPLACE TRIGGER audit_emp_values
	AFTER DELETE OR INSERT OR UPDATE ON emp
	FOR EACH ROW
begin
	IF updating or inserting or deleting THEN
		insert into EMP_AUDIT_TABLE 
     (USER_NAME,CDATE,id,OLD_SAL, 	NEW_SAL ) values 
     (USER, SYSDATE , :OLD.empno, 	:OLD.sal, :NEW.sal ) ;
	END IF;
end;
/
show error

Restricting a Row Trigger – By Specifying WHEN Condition

set DEFINE OFF; 
CREATE OR REPLACE TRIGGER Print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON emp
  FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE
    sal_diff number;
BEGIN
    sal_diff  := :new.sal  - :old.sal;
    dbms_output.put('Old salary: ' || :old.sal);
    dbms_output.put('  New salary: ' || :new.sal);
    dbms_output.put_line('  Difference ' || sal_diff);
END;
/

Instead of Triggers

  • A view can not be modified by normal DML statements if the view query contains set operators, group functions or joins
  • In such cases we need INSTEAD OF triggers
  • They used to perform INSERT, UPDATE,or DELETE operations directly in the underlying tables.
Syntax :
		CREATE [OR REPLACE] TRIGGER trigger_name
		         	INSTEAD OF  event1 [ OR event2 OR event3]
			ON view_name
			[REFERENCING OLD AS old/NEW AS new]
		[FOR EACH ROW]
		trigger_body

--View Script
CREATE VIEW emp_dept_vu IS
	SELECT empno,ename,e.deptno, dname, loc FROM emp e,dept e
	WHERE e.deptno=d.deptno ;

--Trigger Script
CREATE OR REPLACE TRIGGER  new_emp_dept
	INSTEAD OF INSERT ON emp_dept_vu
	FOR EACH ROW  BEGIN
		IF INSERTING THEN
			INSERT INTO dept(deptno,dname,loc) 
			VALUES(:NEW.deptno , :NEW.dname , :NEW.loc);
			INSERT INTO emp(empno,ename,deptno) 
			VALUES(:new.empno,:new.ename, :new.deptno); 
		END IF;
	END ;

Triggers vs Procedures

TRIGGERSPROCEDURES
Defined with CREATE TRIGGERDefined with CREATE PROCEDURE
Source code : USER_TRIGGERSSource code : USER_SOURCE
Implicitly invokedExplicitly invoked
COMMIT, SAVEPOINT and ROLLBACK are not allowedCOMMIT, SAVEPOINT and ROLLBACK are allowed

Managing Triggers

  • Disabling or re enabling a database trigger
  • ALTER TRIGGER trigger_name DISABLE | ENABLE
  • Disable or re enable all triggers for a table
  • ALTER TABLE table_name DISABLE|ENABLE ALL TRIGGERS
  • Recompile a trigger for a table
  • ALTER TRIGGER trigger_name COMPILE
  • Remove a trigger
  • DROP TRIGGER trigger_name
  • Note : If table is dropped all relevant triggers are also dropped

Trigger Execution Model and  Constraint Checking

  • Execute all BEFORE STATEMENT triggers
  • Loop for each row affected :
    • – execute all BEFORE ROW triggers
    • – execute all AFTER ROW triggers
  • Execute the DML statement and perform integrity constraint checking
  • Execute all AFTER STATEMENT triggers

Non DML Triggers – DDL Events and System Events

  • Triggering User event :
    • – CREATE, ALTER or DROP
    • – Logging on or off
  • Triggering database or system event :
    • – Shutting down or starting up the database
    • – A specific error (or any error) being raised
  • Execute the below GRANTS in SYS Schema
  • GRANT CREATE TRIGGER TO HR;
  • GRANT CREATE ANY TRIGGER TO HR;
  • GRANT ADMINISTER DATABASE TRIGGER TO HR
Syntax :
	CREATE [OR REPLACE] TRIGGER trigger_name
		timing   [database_event1 [OR database_event2 OR …]]
			ON { DATABASE | SCHEMA }
	trigger_body 

Example :

create table logon_details(userid varchar2(100), cdate date, action varchar2(1000))

	CREATE OR REPLACE TRIGGER logon_details_trig
		AFTER LOGON ON SCHEMA
	begin
		INSERT INTO logon_details(userid, cdate, action)
		VALUES (USER, SYSDATE, 'LOG ON');
	end ;

	create or replace trigger LOGOFF_DETAILS_TRIG
		BEFORE LOGOFF ON SCHEMA
	begin
		insert into LOGON_DETAILS(USERID, CDATE, ACTION)
		VALUES (USER, SYSDATE, 'LOG OFF');
	end ;

Benefits of Triggers

  • Improved data security :
    • – Provide enhanced and complex security checks
    • – Provide enhanced and complex auditing
  • Improved data integrity :
  • – Enforce dynamic data integrity constraints
  • – Enforce complex referential integrity constraints
  • – Ensure that related operations are performed together implicitly

Guidelines for Designing Triggers

  • Design triggers to :
    • – perform related actions
    • – centralize global operations
  • Do not design triggers :
    • – where functionality is already built into the Oracle server
    • – that duplicate other triggers
  • Create, store procedures and invoke them in a trigger, if the PL/SQL code is very lengthy.
  • The excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in large applications.

Managing Triggers

  • The following system privileges are required to manage triggers:
  • The CREATE / ALTER / DROP (ANY) TRIGGER
  • privilege enables you to create a trigger in any schema
  • The ADMINISTER DATABASE TRIGGER privilege enables to create a trigger on DATABASE
  • The EXECUTE privilege
  • Note :
  • Statements in the trigger body operate under the privilege of the trigger owner, not the trigger user.

Viewing Trigger Information

  • Below are the metadata views/tables which has the trigger information
  • USER_OBJECTS for object information
  • USER_TRIGGER for the text of the tiger
  • USER_ERRORS for compilation errors
  • Example :
  • SELECT trigger_body from USER_TRIGGERS
  • WHERE trigger_name = ‘WISH_TRIG’ ;

Summary

  • In this lesson, you have learned :
  • Different types of triggers
  • Creating and managing application triggers
  • Use of row level, table level, instead of triggers
  • Trigger execution model
  • Creating and managing database triggers
  • CALL statement
  • Benefits of triggers
  • View trigger information

Comments |0|

Legend *) Required fields are marked
**) You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>
Category: Database