PL SQL Tutorial
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 beINSERT
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 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
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 :
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 triggerALTER 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