Triggers in Oracle
Triggers are special kind of procedure which is fired automatically don’t need to call by user explicitly. It automatically fired on some event happen insert update delete operation on which it built or code.
Syntax:
CREATE [OR REPLACE] TRIGGER schema.trigger-name
{BEFORE | AFTER} dmlevent ON tablename
[FOR EACH ROW]
[DECLARE ...]
BEGIN
-- PL/SQL code goes here.
[EXCEPTION ...]
END;
/
AFTER: defines the trigger is executed after the statement executed on table.
ROW LEVEL TRIGGER:FOR EACH ROW Clause is used for defined, trigger is executed at each row modification.
STATEMENT LEVEL TRIGGER: without clause FOR EACH ROW, executed at start and end at table level.
Example of ROW-LEVEL or STATEMENT-LEVEL trigger:
Before statement-level trigger executes
Before row-level trigger executes
One row is updated
After row-level trigger executes
...
Before row-level trigger executes
Millionth row is updated
After row-level trigger executes
After statement-level trigger executes
Check trigger information in database
SELECT TRIGGER_NAME FROM ALL_TRIGGERS WHERE TABLE_NAME = 'SALES'
SELECT * FROM DBA_TRIGGERS WHERE TRIGGER_NAME = 'AW_DROP_TRG';
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TRIGGER';
SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME = 'TRIGGER_NAME';
Get source or code for trigger
set heading off;
set echo off;
Set pages 999;
set long 20000;
select dbms_metadata.get_dependent_ddl('TRIGGER','EMP','SCOTT') output from dual;
Enable or Disable the Triggers
--Enable or disable single trigger
ALTER TRIGGER triggername DISABLE;
ALTER TRIGGER triggername ENABLE;
--Enable or disable all trigger at table level
ALTER TABLE tablename DISABLE ALL TRIGGERS;
ALTER TABLE tablename ENABLE ALL TRIGGERS;
--Enable or disable all trigger at schema level
select 'ALTER TABLE '||table_name||' DISABLE ALL TRIGGERS;' from user_triggers;
select 'ALTER TABLE '||table_name||' ENABLE ALL TRIGGERS;' from user_triggers;
Errors:
ORA-04082: NEW or OLD references not allowed in table level triggers
Solutions:
1. Eliminate the use of NEW OR OLD your statement in trigger.
2. Another option make add FOR EACH ROW clause in trigger.
Example of ROW LEVEL Trigger with OLD OR NEW keyword
We have table hr.sales for keep current value of sales data and hr.sales_bkp for taking backup or modification going in sales for keep track. We created trigger on sales table for taking backup.
OLD : old is pointing to UPDATE or DELETE old records.
NEW : New is pointing to INSERT or UPDATE new records.
Note: Update statement is having both OLD or NEW data pointer.
CREATE OR REPLACE TRIGGER t_test
BEFORE INSERT OR UPDATE OR DELETE ON hr.sales
FOR EACH ROW
BEGIN
IF inserting THEN
insert into hr.sales_bkp values ( :new.id, :new.name, :new.sales, sysdate ,'I');
END IF;
-- always set the modified date to now
IF updating THEN
insert into hr.sales_bkp values ( :old.id, :old.name, :old.sales, sysdate ,'U');
END IF;
IF DELETING THEN
insert into hr.sales_bkp values( :old.id, :old.name, :old.sales, sysdate ,'D');
END IF;
END;
/
Trigger created.
SQL> insert into sales values (1,'LG WM',25000);
1 row created.
SQL> update sales set name='SONY WM' where id = '1';
1 row updated.
SQL> update sales set sales='35000' where id = '1';
1 row updated.
SQL> delete from sales;
1 row deleted.
SQL> select * from sales_bkp;
ID NAME SALES CREATED_D F ---- ---------- ---------- --------- - 1 LG WM 25000 11-SEP-18 I 1 LG WM 25000 11-SEP-18 U 1 SONY WM 25000 11-SEP-18 U 1 SONY WM 35000 11-SEP-18 D
Example of STATMENT LEVEL TRIGGERS
--Create Statment Level Trigger
CREATE OR REPLACE TRIGGER trigger_Statement_test
BEFORE INSERT OR UPDATE OR DELETE ON hr.sales
BEGIN
IF inserting THEN
insert into hr.sales_bkp values ( '10','INSERTED',0,sysdate,'I');
END IF;
-- always set the modified date to now
IF updating THEN
insert into hr.sales_bkp values ( '10','UPDATED',0,sysdate,'U');
END IF;
IF DELETING THEN
insert into hr.sales_bkp values ( '10','DELETED',0,sysdate,'D');
END IF;
END;
/
--In this bulk operation of insert statement, Only one entry due to statement trigger.
insert into sales select * from sales_history;
4 rows inserted
--Check SALES_BKP table having STATEMENT LEVEL trigger entry and also have entries for ROW LEVEL trigger created in above example.
select * from sales_bkp;
ID NAME SALES CREATED_D F
----- ---------- ---------- --------- -
10 INSERTED 0 11-SEP-18 I
1 LG 20000 11-SEP-18 I
2 SONY 20000 11-SEP-18 I
3 DELL 25000 11-SEP-18 I
4 LENOVO 40000 11-SEP-18 I
--Now Sales table has two triggers
SQL> select trigger_name from all_triggers where table_name = 'SALES';
TRIGGER_NAME
-----------------------
T_TEST
TRIGGER_STATEMENT_TEST
