Welcome

PL SQL Tutorial


PL SQL Triggers

    PL SQL Triggers are named stored procedures in the Database.But Associated with Events. Events can be DML Operations or DDL Operations. Using Traiggers all operations on the database can be captured and can be recorded, and also Complex Data Validations can be done using triggers. Apart from that Security check ,Auditing,logging etc. can be done using Triggers.

PL/SQL Triggers can be
  • DML Events
  • Compound DML Events
  • DDL Events
  • Database Events

Triggers can be enabled or disabled.

DML Events

DML Triggers are fired when DML operations are performed.

INSERT
UPDATE
DELETE

			There 2 types of DML triggers
			1.Statement Level Trigger
			2.Row Level Trigger
			

DML Statement Level Triggers: are fired only once per SQL Statement. for ex: UPDATE can update single or multiple rows, but fired only once . This type of triggers are used ofr auditing or security purpose

DML Row Level Triggers: are fired for every row that gets updated or deleted. This type of triggers can be used for incorporatng more business logic, data validations etc.,

BEFORE Trigger A trigger that executes before certain event occurs, for ex: before insert

AFTER Trigger A trigger that executes after certain event occurs, for ex: after insert



Pseudo columns in Triggers

  • :new
  • :old

:new and :old pseudo columns are of type %ROWTYPE. each field accessed using . operator, it should precede with :,

for INSERT :old is not valid
for DELETE :new is not valid
for UPDATE :new and :old columns are used.

WHEN Clause The portion of the DML trigger that is run to determine whether or not the trigger code should be executed.

Conditional Predicates

Triggers use conditional predicates to detect triggering statement

INSERTING for DML INSERT
UPDATING for DML UPDATE
UPDATING ('column')
DELETING for DML DELETE

Order of the Triggers

  • BEFORE STATEMENET level TRIGGER
  • BEFORE ROW-LEVEL TRIGGER
  • TRIGGERING STATEMENT
  • AFTER EACH ROW STATEMENT
  • AFTER STATEMENT LEVEL TRIGGER
Create Trigger Syntax:
		Create or REPLACE Tigger Trigger_Name
		{BEFORE | AFTER}
		{INSERT | UPDATE | DELETE}  ON table_name
		[FOR EACH ROW]
		
		[DECLARE]
		
		BEGIN
		     --executable statements
		[EXCEPTION]
		END;
		
		
Create a BEFORE INSERT Trigger -- ROW-LEVEL Trigger
			create or replace trigger beforeInsert
			BEFORE
			INSERT  ON HR.EMPLOYEES
			BEGIN
				DBMS_OUTPUT.PUT_LINE('BEFORE INSERT TRIGGER FIRED');
			END;
		
Insert into hr schema employees table, as shown below
		
SQL>insert into employees(employee_id,last_name,email,hire_date,job_id) 
 values(100000,'trigger1','trigger@test.com',sysdate,'PR_REP');
 
BEFORE INSERT TRIGGER FIRED

1 row created.

		
		
Note: Column level Constraints(primary key, foregin key, column level validations etc.) are checked first, if they are valid, Oracle performs INSERT operation, Before INSERT Operation,BEFORE INSERT trigger will be fired. i.e sequence is :
  • BEFORE INSERT Trigger
  • Actual INSERT Operation

Enable/Disable Triggers

    Oracle supports Enabling ,disabling triggers, using ALTER TRIGGER DDL command. Programmer can enable or disable individual triggers, or he can enable or disable all triggers on a table using single statement, as shown below.

Alter specific trigger
			ALTER TRIGGER TRIGGER_NAME {ENABLE | DISABLE};
		
Alter all trigger
			ALTER TRIGGER TABLE_NAME {ENABLE|DIABLE}  ALL TRIGGERS;
		

Mutating Table Errors

THIS PROBLEM OCCURS WHEN ROW-LEVEL TRIGGERS ARE FIRED,WHEN USER SELECTS/QUERY DATA FROM WHICH IT HAS BEEN FIRED

PRAGMA AUTONOMOUS TRANSACTION ALLOWS US TO QUERY THE FIRING TABLE

ADS