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]
