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