Oracle, Web, Script, SQLserver, Tips & Trick

July 14, 2013

How to generate dynamic trigger INSERT, UPDATE & DELETE in oracle

Filed under: oracle — sikathabis @ 6:50 am
Tags: ,
Step 1 : User must have access to create TRIGGER, for example GRANT CREATE ANY TRIGGER TO DBADMIN;

Step 2 : Create Procedure GEN_TGR which default to generate INSERT, UPDATE, DELETE trigger automaticly

CREATE OR REPLACE procedure DBADMIN.gen_tgr (tname varchar2,dblink varchar2) is
   F1 VARCHAR2(500);
   F2 VARCHAR2(32625);
   F3 VARCHAR2(32625);
   F4 VARCHAR2(32625);
   F5 VARCHAR2(1000);
   F6 VARCHAR2(500);
begin
--Generate Header 
F1 := 'create or replace trigger '||tname ||'_TRG_IUD'||chr(10)||
                            ' after update or delete or insert on '||tname ||chr(10)||
                            ' for each row ' ||chr(10)||
                            ' begin ' ||chr(10) ||
                            ' if inserting then ' ||chr(10) ||
--Generate Insert                             
                            ' Insert into '||tname||dblink||chr(10)||'( ';
for I_do in
( select  column_name from user_tab_columns
        where table_name=tname ) loop
        F2:= F2 ||chr(9)|| I_DO.column_name || ','||chr(10);
        F3:= F3 ||chr(9)|| ':NEW.'||I_DO.column_name || ','||chr(10);
end loop;                            
F2:=rtrim(F2,','||chr(10))||' ) '||chr(10)||' values '||chr(10)||' ( ';
--Generate Update                             
F3:=rtrim(F3,','||chr(10))||'); '||chr(10)||' elsif UPDATING then ';
F3:=F3||chr(10)||' UPDATE '||tname||dblink||' SET '||chr(10); 
for I_do in
( select column_name 
from user_tab_columns
where table_name=tname and 
column_name not in 
( 
select a.column_name 
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name 
and a.table_name = upper(tname) and b.uniqueness='UNIQUE'
) ) loop
        F4:= F4 || chr(9)||I_DO.column_name || '=:NEW.'||I_DO.column_name||','||chr(10);
end loop;    
F4:=rtrim(F4,','||chr(10))||chr(10)||' WHERE '||chr(10);
--Generate Update  (Where condition depent on unique index) 
for I_do in
(
select a.column_name 
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name 
and a.table_name = upper(tname) and b.uniqueness='UNIQUE'
order by a.table_name, a.index_name, a.column_position 
) loop
        F5:= F5 || chr(9)||chr(9)||I_DO.column_name || '=:OLD.'||I_DO.column_name||' AND '||chr(10);
end loop;                  
F5:=rtrim(F5,' AND '||chr(10))||' ; ' ||CHR(10);
--Generate Delete (Where condition depent on unique index)
F6 := ' else '||chr(10)||chr(9)||'DELETE '||tname||dblink||' where '||chr(10);
for I_do in
(
select a.column_name 
from all_ind_columns a, all_indexes b
where a.index_name=b.index_name 
and a.table_name = upper(tname) and b.uniqueness='UNIQUE'
order by a.table_name, a.index_name, a.column_position 
) loop
        F6:= F6 || chr(9)|| chr(9)||I_DO.column_name || '=:OLD.'||I_DO.column_name||' AND '||chr(10);
end loop;                  
F6:=rtrim(F6,' AND '||chr(10))||' ; ' ||CHR(10);
F6 := F6||' end if; end;';
--Execute (Generate New Trigger) 
execute immediate F1||F2||F3||F4||F5||F6;
end;
/

Step 3 : Generate TABLE, for example table name DD_MASTER and destination table DD_MASTER_TEMP 
--using this pl/sql for generate the trigger
begin
    gen_tgr('DD_MASTER','_TEMP');
end;

Step 4 : Result

CREATE OR REPLACE TRIGGER DBADMIN.DD_MASTER_TRG_IUD
 after update or delete or insert ON DBADMIN.DD_MASTER  for each row
begin 
 if inserting then 
 Insert into DD_MASTER_TEMP
(     NO_KUASA,
    KD_PMLK_XBI,
    SFT_DANA ) 
 values 
 (     :NEW.NO_KUASA,
    :NEW.KD_PMLK_XBI,
    :NEW.SFT_DANA ); 
 elsif UPDATING then 
 UPDATE DD_MASTER_TEMP SET 
    NO_KUASA=:NEW.NO_KUASA,
    KD_PMLK_XBI=:NEW.KD_PMLK_XBI,
    SFT_DANA=:NEW.SFT_DANA
 WHERE 
        KD_KNTR=:OLD.KD_KNTR AND 
        KD_PRODUK=:OLD.KD_PRODUK AND 
        KD_SUB_PRODUK=:OLD.KD_SUB_PRODUK AND 
        KD_VAL=:OLD.KD_VAL AND 
        NO_REK=:OLD.NO_REK ; 
 else 
    DELETE DD_MASTER_TEMP where 
        KD_KNTR=:OLD.KD_KNTR AND 
        KD_PRODUK=:OLD.KD_PRODUK AND 
        KD_SUB_PRODUK=:OLD.KD_SUB_PRODUK AND 
        KD_VAL=:OLD.KD_VAL AND 
        NO_REK=:OLD.NO_REK ; 
 end if; end;
/


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

Teknologi Pendidikan

Just another WordPress.com weblog

KATAKAMI

Jurnalisme Yang Dapat Dipercaya

Tonk Kosonk Baonk Bunyinya

Penampungan Serapah Tanpa Sumpah

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Bodoh Yang Tak Kunjung Pandai

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server

neinframe

Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --

DRK™

This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa

TuanSUFI

Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku

angkasa13

Ceritanya saya, Pikirannya saya, Hatinya saya

sikathabis

sikathabis semua informasi disini

%d bloggers like this: