DML Triggers with Example and video

November 05, 2020

My Tech Channel link:- https://www.youtube.com/oracleshooter

select * from XX_TRIGGERS_TL

BILL_NUMBER	BILL_DATE	PARTY_NUMBER	CREATION_DATE	CREATED_BY
27014461346	06-Jun-18	Sb86d0	25-10-2020 21:15	-1
26871824314	06-Jun-18	Sb86d0	25-10-2020 21:15	-1
26838744879	06-Jun-18	Sb86d0	25-10-2020 21:15	-1
26841632051	06-Jun-18	Sff0be	25-10-2020 21:15	-1
26869441968	06-Jun-18	S2f869	25-10-2020 21:15	-1
26995126887	06-Jun-18	S0df06	25-10-2020 21:15	-1
26898688758	06-Jun-18	S2f869	25-10-2020 21:15	-1
26910294820	06-Jun-18	S0c3b0	25-10-2020 21:15	-1
26844575229	06-Jun-18	S209c8	25-10-2020 21:15	-1
26812437391	06-Jun-18	Sf8c3e	25-10-2020 21:15	-1

--====================================================

--Syntax--

CREATE OR REPLACE TRIGGER TRIGGER_NAME

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] UPDATE [OR] DELETE }

[OF col_name]

ON table_name

--[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

--====================================================

CREATE OR REPLACE TRIGGER XX_B_U_TRIG

BEFORE

UPDATE

--OF BILL_NUMBER

ON XX_TRIGGERS_TL

--REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

BEGIN

dbms_output.put_line ('********* Row Level - Before Update Trigger Fired ***********');

END;

--====================================================

CREATE OR REPLACE TRIGGER XX_A_U_TRIG

AFTER

UPDATE

--OF BILL_NUMBER

ON XX_TRIGGERS_TL

--REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

BEGIN

dbms_output.put_line ('********* Row Level - After Update Trigger Fired ***********');

END;

--====================================================

CREATE OR REPLACE TRIGGER XX_B_U_STMT_TRIG

BEFORE

UPDATE

--OF BILL_NUMBER

ON XX_TRIGGERS_TL

--REFERENCING OLD AS OLD NEW AS NEW

--FOR EACH ROW

BEGIN

dbms_output.put_line ('********* Statement Level - Before Update Trigger Fired ***********');

END;

--====================================================

CREATE OR REPLACE TRIGGER XX_A_U_STMT_TRIG

AFTER

UPDATE

--OF BILL_NUMBER

ON XX_TRIGGERS_TL

--REFERENCING OLD AS OLD NEW AS NEW

--FOR EACH ROW

BEGIN

dbms_output.put_line ('********* Statement Level - After Update Trigger Fired ***********');

END;

--====================================================

select * from XX_TRIGGERS_TL where PARTY_NUMBER = 'Sb86d0'

update XX_TRIGGERS_TL

set BILL_NUMBER = '12345'

where PARTY_NUMBER = 'Sb86d0'

Output:-

********* Statement Level - Before Update Trigger Fired ***********

********* Row Level - Before Update Trigger Fired ***********

********* Row Level - After Update Trigger Fired ***********

********* Statement Level - After Update Trigger Fired ***********