trigger in pl/sql.

Syntax of TRIGGER :

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]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 

here we have a customer table :

SQL> select *from customer;

     AC_NO       B_NO NAME       CITY          BALANCE      COUNT CHK_DATE
---------- ---------- ---------- ---------- ---------- ---------- ---------
       111        101 biku       surat            7990          1 02-SEP-19
       112        103 sunil      vapi            11000          1 02-SEP-19
       113        102 balram     valsad          19000          3 01-JAN-19
       114        104 jenna      surat           18000          2 01-JAN-19
       115        105 sagar      mumbai           9000          1 01-JAN-19
       116        101 chirag     surat             800          3 01-JAN-19
       117        102 aditya     vapi              500          5 01-JAN-19
       118        103 rahul      valsad          14000          2 01-JAN-19
       119        103 bhavesh    mumbai           5000          1 01-JAN-19
       120        104 jay        surat             400          4 01-JAN-19
       121        105 bhola      vapi            11000          4 01-JAN-19

     AC_NO       B_NO NAME       CITY          BALANCE      COUNT CHK_DATE
---------- ---------- ---------- ---------- ---------- ---------- ---------
       122        101 sima       valsad           8000          2 01-JAN-19
       123        102 montu      surat            9000          2 01-JAN-19

13 rows selected.

program of trigger :

create table show_track_customer(      -- table to insert the trigger infromation
 ac_no int,
 operation varchar2(15),
 when_date date,
 which_user varchar2(15)
);
create or replace trigger track_customer after insert or update or delete on customer for each row     -- cursor name

declare 
 m_oper varchar2(15);
begin
 case
  when inserting then
   m_oper:='insert';
  when deleting then 
   m_oper:='delete';
  when updating then
   m_oper:='update';
 end case;

 insert into show_track_customer values(:old.ac_no,m_oper,sysdate,user);    -- insert intp show_track_customer table for any event.
end;
/

when update the customer table

SQL> update customer set name='rani' where ac_no=122;

1 row updated.

Result :

SQL> select *from show_track_customer;

     AC_NO OPERATION       WHEN_DATE WHICH_USER
---------- --------------- --------- ---------------
       122 update          22-SEP-19 SYSTEM

If you like code with vibhu  and would like to contribute, you can also write an article using   This link :


https://docs.google.com/forms/d/e/1FAIpQLScAmvlPvNUz35R-G0nc_zpRVP3o8xlhtFgC3aKPyLetX_RyXg/viewform?pli=1


OR  mail your article to codewithvibhu@gmail.com.  See your article appearing on the code with vibhu main page and help other students. 

❮ Previous                                                      Next ❯