Dear Ester, Saya cek dan coba script untuk create triggernya sudah betul. Coba dicek lagi pengetikannya. Mungkin ada salah ketik. Berdasarkan output atau show error: pada trigger 1, kurang tanda kurung pada trigger 2, kurang statement update sebelum " of " (yang diberi tanda *.
Berikut output trigger dari email pertama. SQL> CREATE OR REPLACE TRIGGER update_STOCK_brg_ord 2 BEFORE INSERT OR UPDATE OF QTY ON order_detail 3 FOR EACH ROW 4 BEGIN 5 IF NVL(:OLD.QTY,0) < NVL(:NEW.QTY,0) THEN 6 UPDATE MASTER_BARANG 7 SET STOCK = STOCK + (NVL(:NEW.QTY,0)-NVL(:OLD.QTY,0)) 8 WHERE KODE_BRG = :NEW.KODE_BRG; 9 ELSE 10 UPDATE MASTER_BARANG 11 SET STOCK = STOCK - (NVL(:OLD.QTY,0)-NVL(:NEW.QTY,0)) 12 WHERE KODE_BRG = :NEW.KODE_BRG; 13 END IF; 14 END; 15 / Trigger created. SQL> CREATE OR REPLACE TRIGGER update_STOCK_brg_sales 2 BEFORE INSERT OR UPDATE OF QTY ON sales_detail 3 FOR EACH ROW 4 BEGIN 5 IF NVL(:OLD.QTY,0) < NVL(:NEW.QTY,0) THEN 6 UPDATE MASTER_BARANG 7 SET STOCK = STOCK - (NVL(:NEW.QTY,0)-NVL(:OLD.QTY,0)) 8 WHERE KODE_BRG = :NEW.KODE_BRG; 9 ELSE 10 UPDATE MASTER_BARANG 11 SET STOCK = STOCK + (NVL(:OLD.QTY,0)-NVL(:NEW.QTY,0)) 12 WHERE KODE_BRG = :NEW.KODE_BRG; 13 END IF; 14 END; 15 / Trigger created. Trigger created - artinya trigger success di-create tanpa error ataupun warning. Cheers, Wadi Achmed 2010/2/11 Del Ester <[email protected]> > Terima kasih atas tanggapannya mas Wadi. > aku udh coba lagi > 1. kalau di tulis seperti ini CREATE OR REPLACE TRIGGER update_STOCK_ > brg_ord > erornya warning: Trigger created with compilation errors. > kemudian aku tulis SHOW ERROR, pesannya adalah > Error for TRIGGER UPDATE_STOCK_BRG_SALES: > LINE/COL ERROR > ------------ > > ----------------------------------------------------------------------------------- > 7/1 PL/SQL: Statement ignored > 8/56 PL/SQL: ORA-00907: missing right parenthesis > > 2. kalau di tulis seperti ini CREATE TRIGGER update_STOCK_ brg_ord > pesan errornya > before insert of qty on order_detail > * > ERROR at line 2: > ORA-04073: column list not valid for this trigger type > > kalau diketik SHOW ERROR > pesannya NO ERROR. > > Aku benar2 ngak mengerti salahnya dimana karena aku baru belajar, kalau > ngak keberatan tolong aku di kasih solusi ya Mas. > > Terima kasih banyak sebelumnya. > > > > > > > ________________________________ > From: Wadi Achmed <[email protected]> > To: [email protected] > Sent: Wed, February 10, 2010 3:17:53 AM > Subject: Re: {Disarmed} Re: [indo-oracle] Tanya Trigger n save > > > Hi, > > Kalau compile nya di sqlplus, coba lihat errornya dengan ketik show error > Dari output show error itu, baru bisa diketahui salahnya di mana. > > Umumnya kurang tanda koma, titik koma ( ; ), kutip, sintaks nya. > > Cheers, > Wadi Achmed > > On Wed, Feb 10, 2010 at 10:28 AM, Del Ester <del.es...@yahoo. com> wrote: > > > Terima kasih atas bantuannya mas Joey. > > maaf, saya ada 1 pertanyaan lagi, sewaktu saya run 2 trigger tersebut > > keluar error > > Warning: Trigger created with compilation errors. > > > > Atas bantuannya saya ucapkan terima kasih. > > > > > > > > > > > > ____________ _________ _________ __ > > From: inijoey <inij...@gmx. com> > > To: indo-oracle@ yahoogroups. com > > Sent: Mon, February 8, 2010 7:56:13 PM > > Subject: {Disarmed} Re: [indo-oracle] Tanya Trigger n save > > > > > > sepertinya cuman kurang grant create trigger untuk user bro ... > > coba login dari system : grant create trigger to <user>; > > > > regards, > > joey > > > > Del Ester wrote: > > > > > > > > > Hallo para senior Oracle. > > > Saya baru belajar Oracle dapat kesulitan, mudahan2 para senior oracle > > > ada yang bisa memberikan penyegaran.. ...... > > > aku mau membuat trigger dari contoh di bawah ini. > > > > > > 1. untuk create table sih gak ada masalah, semuanya "table created", > > > 2. setelah nyoba create dua trigger, ke dua duanya keluar pesan > > > "ORA-01031: insufficient privilages". > > > kenapa yah.... apa memang ada yang salah dari dua sintak trigger > > tersebut. > > > 3. dalam penulisannya apa memang harus di ikutkan kata2 replace untuk > > > create (baru). > > > 4. sintak2 seperti ini apa bisa di simpan (dalam satu file/tempat) dan > > > sewaktu2 bisa di panggil untuk modifikasi, > > > > > > Sekian dari saya atas kebaikan hati para senior Oracle untuk > > > memberikan tanggapan dan pencerahannya saya ucapkan terima kasih. > > > > > > NB. > > > Berikut adalah sintak yang saya coba > > > > > > 1. Buat tabel dan mendefinisikan constraint > > > SQL> CREATE TABLE MASTER_BARANG > > > 2 (KODE_BRG NUMBER(4) PRIMARY KEY, > > > 3 NAMA_BRG VARCHAR2(20) NOT NULL, > > > 4 HARGA NUMBER(10), > > > 5 STOCK NUMBER(4)); > > > Table created. > > > > > > SQL> CREATE TABLE ORD > > > 2 (NO_ORDER NUMBER(4) PRIMARY KEY, > > > 3 ID_SUPPLIER NUMBER(4) NOT NULL, > > > 4 TGL_ORDER DATE, > > > 5 RP_TOTAL NUMBER(12)); > > > Table created. > > > > > > SQL> CREATE TABLE ORDER_DETAIL > > > 2 (NO_ORDER NUMBER(4) REFERENCES ORD(NO_ORDER) , > > > 3 NO_URUT NUMBER(3), > > > 4 KODE_BRG NUMBER(4) REFERENCES MASTER_BARANG( KODE_BRG) , > > > 5 HARGA NUMBER(10), > > > 6 QTY NUMBER(4), > > > 7 SUBTOTAL NUMBER(10)); > > > Table created. > > > > > > SQL> CREATE TABLE SALES > > > 2 (NO_SALES NUMBER(4) PRIMARY KEY, > > > 3 ID_CUST NUMBER(4) NOT NULL, > > > 4 TGL_SALES DATE, > > > 5 RP_TOTAL NUMBER(12)); > > > Table created. > > > > > > SQL> CREATE TABLE SALES_DETAIL > > > 2 (NO_SALES NUMBER(4) REFERENCES SALES(NO_SALES) , > > > 3 NO_URUT NUMBER(3), > > > 4 KODE_BRG NUMBER(4) REFERENCES MASTER_BARANG( KODE_BRG) , > > > 5 HARGA NUMBER(10), > > > 6 QTY NUMBER(4), > > > 7 SUBTOTAL NUMBER(10)); > > > Table created. > > > > > > 2. Membuat Trigger Database > > > > > > SQL> CREATE OR REPLACE TRIGGER update_STOCK_ brg_ord > > > 2 BEFORE INSERT OR UPDATE OF QTY ON order_detail > > > 3 FOR EACH ROW > > > 4 BEGIN > > > 5 IF NVL(:OLD.QTY, 0) < NVL(:NEW.QTY, 0) THEN > > > 6 UPDATE MASTER_BARANG > > > 7 SET STOCK = STOCK + (NVL(:NEW.QTY, 0)-NVL(:OLD. QTY,0)) > > > 8 WHERE KODE_BRG = :NEW.KODE_BRG; > > > 9 ELSE > > > 10 UPDATE MASTER_BARANG > > > 11 SET STOCK = STOCK - (NVL(:OLD.QTY, 0)-NVL(:NEW. QTY,0)) > > > 12 WHERE KODE_BRG = :NEW.KODE_BRG; > > > 13 END IF; > > > 14 END; > > > 15 / > > > ORA-01031: insufficient privilages > > > > > > SQL> CREATE OR REPLACE TRIGGER update_STOCK_ brg_sales > > > 2 BEFORE INSERT OR UPDATE OF QTY ON sales_detail > > > 3 FOR EACH ROW > > > 4 BEGIN > > > 5 IF NVL(:OLD.QTY, 0) < NVL(:NEW.QTY, 0) THEN > > > 6 UPDATE MASTER_BARANG > > > 7 SET STOCK = STOCK - (NVL(:NEW.QTY, 0)-NVL(:OLD. QTY,0)) > > > 8 WHERE KODE_BRG = :NEW.KODE_BRG; > > > 9 ELSE > > > 10 UPDATE MASTER_BARANG > > > 11 SET STOCK = STOCK + (NVL(:OLD.QTY, 0)-NVL(:NEW. QTY,0)) > > > 12 WHERE KODE_BRG = :NEW.KODE_BRG; > > > 13 END IF; > > > 4 END; > > > 15 / > > > ORA-01031: insufficient privilages > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > -- > > This message has been scanned for viruses and > > dangerous content by MailScanner, and is > > believed to be clean. > > > > [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: 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]
