Anda tidak akan bisa melihat apa yang berubah dengan cara membaca USER_TAB_COLS. Oracle otomatis akan menyimpan data yang berubah pada NEW atau OLD variabel sesuai apa yang didefinisikan dengan "Referencing NEW as NEW and OLD as OLD", sehingga bentuk trigger anda akan menjadi seperti ini
CREATE OR REPLACE TRIGGER TG_Customer AFTER UPDATE OF COLUMN_A, COLUMN_B ON Customer REFERENCING NEW AS New OLD AS Old FOR EACH ROW DECLARE l_field_nm Varchar2(10) ; BEGIN proc_ins_data(:new.customer_id,:new.tgl_registrasi,...); -- atau insert into table_data(customer_id, tgl_registrasi, ....) values(:new.customer_id,:new.tgl_registrasi,...); END; Jika anda menggunakan store procedure untuk melakukan insert data ke table lain maka proc_ins_data harus dibuat lebih dulu. Tapi jika langsung statement DML seperti insert into, maka tidak diperlukan stored procedure jika data yang digunakan dari data yang di update. Akan lebih mudah bagi anda memahami pemrograman db oracle jika anda membaca dokumen sql dan plsql. Beberapa referensi diantaranya: - http://www.oracle.com/technology/tech/pl_sql/index.html - http://otn.oracle.com/pls/db92/db92.sql_keywords Cheers, Wadi AChmed 2010/1/15 <[email protected]> > Dear P' Wadi dan P' Ipang, > > Berati harus di buat specifik-ya utk SP, Func, dtt. > wah saya baru ngerti kalo bisa Mutating datanya. Thx vm. > > Btw. > Ttg pertanyaan saya sebelumnya. > Jika kita lihat pada tabel USER_TAB_COLS. > Nah... pada table tersebut kan terdapat Table_name, Column_Name, > dst..dst.... > > Jadi saat salah satu Column pada Table Customer ter-Update, > Trigger Update pada table Customer akan segera jalan dan mencari sendiri > Data pada Column yang berubah. > Sehingga akan terbentuk :Old.{column_name} dan :New.{column_name} (plz > see my "Contoh Trigger" below) > > Bagaimana ya... penulisannya :Old.{column_name} dan :New.{column_name} > sehingga oracle bisa baca statement itu ? > > Thanks in advance. > Laurens > > > > > From: > Wadi Achmed <[email protected]> > To: > [email protected] > Date: > 01/15/2010 04:17 PM > Subject: > Re: [indo-oracle] DML Trigger (After Update on table) > Sent by: > [email protected] > > > > > Halo Pak Ipang, > > Sedikit koreksi, dalam trigger bisa memanggil Stored Procedure, stored > Function, built-in Function, atau Package. > Tetapi perlu dihindari pemanggilan dari Trigger terhadap SP, Function, > atau > Package yang melakukan query ke table/materialized view yang sama. Karena > akan menyebabkan Mutating. > > Cheers, > Wadi Achmed > > On Thu, Jan 14, 2010 at 7:14 AM, ipank Aji <[email protected] > >wrote: > > > maaf.. sebelumnya saya ingin bertanya. Apakah didalam body trigger > tersebut > > memanggil prosedur proc_ins_data? Karena setahu saya trigger tidak dapat > > memanggil procedure ataupun function. Terima Kasih. > > > > --- On Thu, 14/1/10, Wadi Achmed <[email protected]> wrote: > > > > From: Wadi Achmed <[email protected]> > > Subject: Re: [indo-oracle] DML Trigger (After Update on table) > > To: [email protected] > > Date: Thursday, 14 January, 2010, 5:42 > > > > > > > > Hi Laurens, > > > > > > > > Di dalam trigger cara baca data dari table lain tidak membutuhkan :NEW > atau > > > > :OLD > > > > Karena anda coba membuat validasinya secara referensi, maka di dalam > > trigger > > > > harus disediakan if terhadap column yang dimaksud > > > > > > > > CREATE OR REPLACE TRIGGER TG_Customer > > > > AFTER UPDATE ON Customer > > > > REFERENCING NEW AS New OLD AS Old > > > > FOR EACH ROW > > > > DECLARE > > > > l_field_nm Varchar2(10) ; > > > > > > > > Cursor Rec_Data IS > > > > SELECT column_name > > > > FROM table_A > > > > WHERE table_name = 'Customer' ; > > > > > > > > BEGIN > > > > > > > > FOR Rec IN Rec_Data LOOP > > > > l_field_nm := Rec.column_name; > > > > > > > > IF l_field_nm = 'COLUMN_A' THEN > > > > IF :NEW.COLUMN_ A <> :OLD.COLUMN_ A THEN > > > > proc_ins_data; > > > > END IF; > > > > END IF > > > > END LOOP ; > > > > END ; > > > > > > > > Mungkin akan lebih baik anda mendefinisikan triggernya dengan memberikan > > > > batasan column apa aja yang harus diperiksa dan data seperti apa yang > > > > menyebabkan trigger dieksekusi. Contohnya > > > > CREATE OR REPLACE TRIGGER TG_Customer > > > > AFTER UPDATE OF COLUMN_A, COLUMN_B > > > > ON Customer > > > > REFERENCING NEW AS New OLD AS Old > > > > FOR EACH ROW > > > > WHEN ((NEW.COLUMN_ A <> OLD.COLUMN_A) OR (NEW.COLUMN_ B <> OLD.COLUMN_B) > ) > > > > DECLARE > > > > l_field_nm Varchar2(10) ; > > > > > > > > Cursor Rec_Data IS > > > > SELECT column_name > > > > FROM table_A > > > > WHERE table_name = 'Customer' ; > > > > > > > > BEGIN > > > > proc_ins_data; > > > > END ; > > > > > > > > Cheers, > > > > Wadi > > > > > > > > On Wed, Jan 13, 2010 at 6:23 PM, <laurens_kaseger@ manulife. com> wrote: > > > > > > > > > Dear all, > > > > > > > > > > Mohon bantuan sharingnya, > > > > > > > > > > Bagaimana agar :New.l_field_ nm dan :Old.l_field_ nm > > > > > dapat terbaca 'nilai'-nya. > > > > > > > > > > Contoh Trigger: > > > > > CREATE OR REPLACE TRIGGER TG_Customer > > > > > AFTER UPDATE ON Customer > > > > > REFERENCING NEW AS New OLD AS Old > > > > > FOR EACH ROW > > > > > DECLARE > > > > > l_field_nm Varchar2(10) ; > > > > > > Cursor Rec_Data IS > > > SELECT column_name > > > FROM table_A > > > WHERE table_name = 'Customer' ; > > > > > > BEGIN > > > > > > FOR Rec IN Rec_Data LOOP > > > l_field_nm := Rec.column_name ; > > > > > > IF :New.l_field_ nm <> :Old.l_field_ nm Then > > > -> proc ins_data > > > END IF > > > > > > END LOOP ; > > > > > > END ; > > > > > > > > > Thanks vm > > > > > Laurens > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > ------------ --------- --------- ------ > > > > > > > > > > -- > > > > > -----------I. N.D.O - O.R.A.C.L.E- --------- ----- > > > > > Keluar: indo-oracle- unsubscribe@ yahoogroups. com > > > > > Website: http://indooracle. wordpress. com > > > > > http://www.facebook .com/group. php?gid=51973053 515 > > > > > ------------ --------- --------- --------- -------- > > > > > > > > > > Bergabung dengan Indonesia Thin Client User Groups, > > > > > Terminal Server, Citrix, New Moon Caneveral, di: > > > > > http://indo- thin.blogspot. comYahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > ------------------------------------ > > > > -- > > -----------I.N.D.O - O.R.A.C.L.E--------------- > > Keluar: [email protected] > > Website: http://indooracle.wordpress.com > > http://www.facebook.com/group.php?gid=51973053515 > > ----------------------------------------------- > > > > Bergabung dengan Indonesia Thin Client User Groups, > > Terminal Server, Citrix, New Moon Caneveral, di: > > http://indo-thin.blogspot.comYahoo! Groups Links > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > [Non-text portions of this message have been removed] > > > > ------------------------------------ > > -- > -----------I.N.D.O - O.R.A.C.L.E--------------- > Keluar: [email protected] > Website: http://indooracle.wordpress.com > http://www.facebook.com/group.php?gid=51973053515 > ----------------------------------------------- > > Bergabung dengan Indonesia Thin Client User Groups, > Terminal Server, Citrix, New Moon Caneveral, di: > http://indo-thin.blogspot.comYahoo! Groups Links > > > > [Non-text portions of this message have been removed]
