yah kalo mau simple pake stored procedure :)) 2008/7/21 Oscar Wilyanto <[EMAIL PROTECTED]>:
> Thanks atas koreksinya. dan pragma memang biasa saya pakai untuk > logging, belum coba untuk kasus yang perlu rollback. > > duh.. ribet juga yah kalo gini, musti pakai array2 segala untuk masalah > kecil, versi oracle sesudah ini bakal ada pragma yang bisa dependent gak > yah? bakal butuh neh hehehe.. > > ----- Original Message ----- > From: m Ilhami > To: [email protected] <indo-oracle%40yahoogroups.com> > Sent: Monday, July 21, 2008 9:28 AM > Subject: Re: [indo-oracle] error mutating trigger > > misal begini > > table A ada trigger > > create triger ... > PRAGMA AUTONOMOUS TRANSACTION > begin > insert into b select sum(...) from A > commit; > end; > ---- > > Di aplikasi > -------- > try > { > Insert into A(x, y , z); > Call proc_lainnnya ----Opss ada error disini > } > catch (Exception ex) > { > rollback; > } > --------- > > Table A rollback > Proc_lainnya rollback > Tabel B commit, tidak rollback karena autonomous transaction > > Lalu apakah data di tabel B konsisten, jika B berasal dari sum A, > padahal A sudah ter-rollback. > > CMIIW juga. > > 2008/7/21 Oscar Wilyanto <[EMAIL PROTECTED] <oscar%40mkdgroup.com>>: > > sepengetahuan saya COMMIT pada trigger dengan AUTONOMOUS TRANSACTION > hanya berlaku untuk operasi2 didalam trigger tersebut. jadi jika setelah > trigger selesai diekesekusi kita rollback, perubahan didalam trigger juga > ikut ter- rollback. > > > > mungkin ada yang pernah coba, jadi bisa CMIIW. > > > > > > ----- Original Message ----- > > From: m Ilhami > > To: [email protected] <indo-oracle%40yahoogroups.com> > > Sent: Friday, July 18, 2008 1:10 PM > > Subject: Re: [indo-oracle] error mutating trigger > > > > > > Saya kurang setuju pakai AUTONOMOUS TRANSACTION. Seandainya transaksi > > insert row tersebut digabung dengan operasi di tabel lain, jika ada > > sesuatu, saya tidak bisa me rollback seluruh transaksi tersebut. > > Record tersebut tetap terinsert ke dalam tabel. > > > > Saya lebih setuju dengan membuat procedure untuk operasi insert + > > summary tersebut. > > > > AUTONOMOUS TRANSACTION biasanya hanya saya gunakan untuk logging saja. > > > > 2008/7/17 Oscar Wilyanto <[EMAIL PROTECTED] <oscar%40mkdgroup.com>>: > > > Kalau solusinya menggunakan Application Code tentu saja tidak menjawab > pertanyaan awal, karena error mutating table yang ditanyakan pak Risty tidak > akan pernah terjadi. > > > > > > Jika menggunakan application code untuk menjaga integritas database > juga memiliki kelemahan tersendiri, yakni database tidak mandiri, misalnya: > saat menulis Applikasi baru dengan bahasa program yang berbeda, semua > integritas database harus ditulis ulang, menurut saya ini kelemahan besar. > Jauh lebih baik menulis trigger saja. > > > > > > Salah....statement trigger juga tidak bisa di pakai. > > > gimana anda bisa mendapatkan transaction ID yang di process kalo tidak > ada > > > :NEW ?? > > > > > > Pakai statement trigger tentu saja bisa, tetapi sifatnya mengupdate > tabel tersebut secara keseluruhan (UPDATE .. WHERE...) dengan menggunakan > timming AFTER, jadi tidak diperlukan nilai :NEW, dan masalah mutating table > yang ditanyakan tetap akan terjadi, oleh karena itu cara termudah menurut > saya pribadi tetap menggunakan PRAGMA AUTONOMOUS_TRANSACTION seperti yang > saya kemukakan dari awal. > > > > > > Mengenai menggunakan statement atau row trigger, dalam kasus ini saya > pribadi lebih prefer menggunakan row trigger. > > > > > > ----- Original Message ----- > > > From: Yoel Susanto > > > To: [email protected] <indo-oracle%40yahoogroups.com> > > > Sent: Wednesday, July 16, 2008 2:02 PM > > > Subject: Re: [indo-oracle] error mutating trigger > > > > > > > > > Salah....statement trigger juga tidak bisa di pakai. > > > gimana anda bisa mendapatkan transaction ID yang di process kalo tidak > ada > > > :NEW ?? > > > > > > Paleng bagus yah pake procedure yang dipanggil oleh application secara > > > explicit setelah transaksi selesai (COMMIT). > > > karena application pasti tau transaksi ID nya. > > > > > > 2008/7/16 Oscar Wilyanto <[EMAIL PROTECTED] <oscar%40mkdgroup.com>>: > > > > > > > ok, saya sudah menangkap maksudnya. jadi dalam kasus ini lebih baik > > > > menggunakan 'statement trigger' daripada 'row trigger'. dan bukan > tidak > > > > menggunakan trigger sama sekali. > > > > > > > > mudah2an kasus pak Risty ini terjawab. > > > > > > > > > > > > ----- Original Message ----- > > > > From: Yoel Susanto > > > > To: [email protected] > > > > <indo-oracle%40yahoogroups.com><indo-oracle% > 40yahoogroups.com> > > > > Sent: Wednesday, July 16, 2008 12:05 PM > > > > Subject: Re: [indo-oracle] error mutating trigger > > > > > > > > untuk kasus seperti ini, saya rasa tidak ada. > > > > cara paleng simple dan menguntungkan yah di application code nya. > > > > > > > > transaksi > > > > -- insert record 1 > > > > -- insert record 2 > > > > -- insert record 3 > > > > -- insert record 4 > > > > end transaksi (Commit) > > > > > > > > call procedure; > > > > > > > > Skali lagi untuk kasus ini, tidak ada gunanya melakukan computasi > setiap > > > > selesai insert record baru. > > > > Yang benar adalah setiap selesai transaksi, dari contoh yang di > berikan TS > > > > , > > > > 1 transaksi bisa terdiri dari beberapa record tergantung package > barang > > > > nya. > > > > > > > > 2008/7/16 Oscar Wilyanto <[EMAIL PROTECTED] > > > > <oscar%40mkdgroup.com><oscar% > 40mkdgroup.com>>: > > > > > > > > > Selain menggunakan trigger, apakah ada cara lain untuk > mengotomatisasi > > > > > pengeksekusian sebuah SQL statement atau memanggil procedure dengan > > > > timing > > > > > setiap kali selesai insert baru? > > > > > > > > > > ----- Original Message ----- > > > > > From: Yoel Susanto > > > > > To: [email protected] > > > > > <indo-oracle%40yahoogroups.com><indo-oracle% > 40yahoogroups.com><indo-oracle% > > > > 40yahoogroups.com> > > > > > Sent: Wednesday, July 16, 2008 10:12 AM > > > > > Subject: Re: [indo-oracle] error mutating trigger > > > > > > > > > > cara paleng mudah yah tidak perlu trigger. > > > > > ketika Anda mau melihat Summary transaksi, lakukan dengan SQL > statement > > > > :)) > > > > > > > > > > ATAU create PLSQL Procedure untuk menggantikan trigger tersebut. > > > > > panggil procedure tersebut setelah selesai insert detail transaksi > > > > > > > > > > Thanks. > > > > > > > > > > 2008/7/16 risty <[EMAIL PROTECTED]<deny_risty%40kudus.puragroup.com> > <deny_risty%40kudus.puragroup.com> > > > > <deny_risty%40kudus.puragroup.com> > > > > > >: > > > > > > > > > > > pak Yoel... > > > > > > pada prinsipnya table_a adalah tabel detil utk menampung data > packing > > > > > suatu > > > > > > produk yang mana yang membedakan selain no urut adalah jumlah BOX > dan > > > > ISI > > > > > > sehigga akan mendapat total TOT_QTY ( box x isi). Dari situ data > akan > > > > > > otomatis masuk ke table_b lewat trigger dalam bentuk rekap > > > > quantitasnya. > > > > > > Adakah struktur yg harus di pisah lagi ?? Apakah harus > menggunakan > > > > > variable > > > > > > array dalam triggernya utk menampung summary qty nya?? Mohon > solusi dan > > > > > > bimbingannya > > > > > > thq sebelumnya....... > > > > > > > > > > > > > > > > > > ----- Original Message ----- > > > > > > From: Yoel Susanto > > > > > > To: [email protected] > > > > > > <indo-oracle%40yahoogroups.com><indo-oracle% > 40yahoogroups.com><indo-oracle% > > > > 40yahoogroups.com><indo-oracle% > > > > > 40yahoogroups.com> > > > > > > Sent: Tuesday, July 15, 2008 5:51 PM > > > > > > Subject: Re: [indo-oracle] error mutating trigger > > > > > > > > > > > > Hi Risty, > > > > > > > > > > > > Saya mau tanya mengenai business procedurenya., karena mungkin > anda > > > > > > melakukan sesuatu yang tidak perlu. > > > > > > Apa yang membedakan record berikut, sehingga mereka harus dipisah > > > > > > berdasarkan no urutnya?? > > > > > > Saya liat no_trans, tgl trans, item sama semua, kenapa kalau ini > adalah > > > > > > satu > > > > > > transaksi mereka harus dipisah recordnya > > > > > > > > > > > > 55 01/05/08 1 A 2 100 200 > > > > > > 55 01/05/08 2 A 1 50 50 > > > > > > > > > > > > 2008/7/15 risty <[EMAIL PROTECTED]<deny_risty%40kudus.puragroup.com> > <deny_risty%40kudus.puragroup.com> > > > > <deny_risty%40kudus.puragroup.com> > > > > > <deny_risty%40kudus.puragroup.com> > > > > > > >: > > > > > > > > > > > > > Dear all masters... > > > > > > > > > > > > > > saya adalah pemula oracle,bagaimana menghindari error mutating > > > > trigger > > > > > > > bilamana triggenya mengakumulasi qty dari table_a itu sendiri > > > > > > > > > > > > > > mis: table detil_a > > > > > > > > > > > > > > no_trans tgl_tans no_urut item box isi tot_qty > > > > > > > ------------ ------------ ----------- -------- ------ ---- > --------- > > > > > > > 55 01/05/08 1 A 2 100 200 > > > > > > > 55 01/05/08 2 A 1 50 50 > > > > > > > 56 01/05/08 1 B 1 200 200 > > > > > > > > > > > > > > akan otomatis di insert ke table_b lewat trigger > > > > > > > > > > > > > > no_trans tgl_tans item tot_qty > > > > > > > ------------ ------------ ------- ------- > > > > > > > 55 01/05/08 A 250 > > > > > > > 56 01/05/08 B 200 > > > > > > > > > > > > > > Harus seperti apa trigger yg harus dibuat, mohon solusinya?? > > > > > > > > > > > > > > thx > > > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > > Cheers, > > > > > > Yoel Susanto > > > > > > > > > > > > www.indo-oracletech.com > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > > Cheers, > > > > > Yoel Susanto > > > > > > > > > > www.indo-oracletech.com > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > <br> > > > > > <br> > > > > > <table width="100%"> > > > > > <tr> > > > > > <td> > > > > > ----------------------------------------------------------<br> > > > > > This message contains confidential information and is intended only > for > > > > the > > > > > individual named. If you are not the named addressee you should not > > > > > disseminate, distribute or copy this e-mail. Please notify the > sender > > > > > immediately by e-mail if you have received this e-mail by mistake > and > > > > delete > > > > > this e-mail from your system. E-mail transmission cannot be > guaranteed to > > > > be > > > > > secure or error-free as information could be intercepted, > corrupted, > > > > lost, > > > > > destroyed, arrive late or incomplete, or contain viruses. MKD Group > > > > > therefore does not accept liability for any errors or omissions in > the > > > > > contents of this message, which arise as a result of e-mail > transmission. > > > > If > > > > > verification is required please request a hard-copy version. > > > > > <br><br> > > > > > MKD Group, www.mkdgroup.com<br> > > > > > ----------------------------------------------------------<br> > > > > > </td> > > > > > </tr> > > > > > </table> > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > > > > > > -- > > > > Cheers, > > > > Yoel Susanto > > > > > > > > www.indo-oracletech.com > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > <br> > > > > <br> > > > > <table width="100%"> > > > > <tr> > > > > <td> > > > > ----------------------------------------------------------<br> > > > > This message contains confidential information and is intended only > for the > > > > individual named. If you are not the named addressee you should not > > > > disseminate, distribute or copy this e-mail. Please notify the sender > > > > immediately by e-mail if you have received this e-mail by mistake and > delete > > > > this e-mail from your system. E-mail transmission cannot be > guaranteed to be > > > > secure or error-free as information could be intercepted, corrupted, > lost, > > > > destroyed, arrive late or incomplete, or contain viruses. MKD Group > > > > therefore does not accept liability for any errors or omissions in > the > > > > contents of this message, which arise as a result of e-mail > transmission. If > > > > verification is required please request a hard-copy version. > > > > <br><br> > > > > MKD Group, www.mkdgroup.com<br> > > > > ----------------------------------------------------------<br> > > > > </td> > > > > </tr> > > > > </table> > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > > > > -- > > > Cheers, > > > Yoel Susanto > > > > > > www.indo-oracletech.com > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > > > > > > > <br> > > > <br> > > > <table width="100%"> > > > <tr> > > > <td> > > > ----------------------------------------------------------<br> > > > This message contains confidential information and is intended only for > the individual named. If you are not the named addressee you should not > disseminate, distribute or copy this e-mail. Please notify the sender > immediately by e-mail if you have received this e-mail by mistake and delete > this e-mail from your system. E-mail transmission cannot be guaranteed to be > secure or error-free as information could be intercepted, corrupted, lost, > destroyed, arrive late or incomplete, or contain viruses. MKD Group > therefore does not accept liability for any errors or omissions in the > contents of this message, which arise as a result of e-mail transmission. If > verification is required please request a hard-copy version. > > > <br><br> > > > MKD Group, www.mkdgroup.com<br> > > > ----------------------------------------------------------<br> > > > </td> > > > </tr> > > > </table> > > > > > > > > > > > > [Non-text portions of this message have been removed] > > > > > > > > > ------------------------------------ > > > > > > -- > > > -----------I.N.D.O - O.R.A.C.L.E--------------- > > > Keluar: [EMAIL PROTECTED]<indo-oracle-unsubscribe%40yahoogroups.com> > > > Website: http://indooracle.wordpress.com > > > ----------------------------------------------- > > > > > > Bergabung dengan Indonesia Thin Client User Groups, > > > Terminal Server, Citrix, New Moon Caneveral, di: > > > http://indo-thin.blogspot.comYahoo! Groups Links > > > > > > > > > > > > > > > > > > > > <br> > > <br> > > <table width="100%"> > > <tr> > > <td> > > ----------------------------------------------------------<br> > > This message contains confidential information and is intended only for > the individual named. If you are not the named addressee you should not > disseminate, distribute or copy this e-mail. Please notify the sender > immediately by e-mail if you have received this e-mail by mistake and delete > this e-mail from your system. E-mail transmission cannot be guaranteed to be > secure or error-free as information could be intercepted, corrupted, lost, > destroyed, arrive late or incomplete, or contain viruses. MKD Group > therefore does not accept liability for any errors or omissions in the > contents of this message, which arise as a result of e-mail transmission. If > verification is required please request a hard-copy version. > > <br><br> > > MKD Group, www.mkdgroup.com<br> > > ----------------------------------------------------------<br> > > </td> > > </tr> > > </table> > > > > > > > > [Non-text portions of this message have been removed] > > > > > > ------------------------------------ > > > > -- > > -----------I.N.D.O - O.R.A.C.L.E--------------- > > Keluar: [EMAIL PROTECTED]<indo-oracle-unsubscribe%40yahoogroups.com> > > Website: http://indooracle.wordpress.com > > ----------------------------------------------- > > > > Bergabung dengan Indonesia Thin Client User Groups, > > Terminal Server, Citrix, New Moon Caneveral, di: > > http://indo-thin.blogspot.comYahoo! Groups Links > > > > > > > > > > <br> > <br> > <table width="100%"> > <tr> > <td> > ----------------------------------------------------------<br> > This message contains confidential information and is intended only for the > individual named. If you are not the named addressee you should not > disseminate, distribute or copy this e-mail. Please notify the sender > immediately by e-mail if you have received this e-mail by mistake and delete > this e-mail from your system. E-mail transmission cannot be guaranteed to be > secure or error-free as information could be intercepted, corrupted, lost, > destroyed, arrive late or incomplete, or contain viruses. MKD Group > therefore does not accept liability for any errors or omissions in the > contents of this message, which arise as a result of e-mail transmission. If > verification is required please request a hard-copy version. > <br><br> > MKD Group, www.mkdgroup.com<br> > ----------------------------------------------------------<br> > </td> > </tr> > </table> > > [Non-text portions of this message have been removed] > > > -- Cheers, Yoel Susanto www.indo-oracletech.com [Non-text portions of this message have been removed]
