Mendukung apa yang sudah di sampaikan pak Ilhami, ini saya ambil dari Oracle
Doc
*Autonomous transactions are useful for implementing actions that need to be
performed independently, regardless of whether the calling transaction
commits or rolls back, such as transaction logging and retry counters.*
Jadi calling transaction mau rollback atau commit, autonomous transaction
juga gak peduli.
2008/7/21 m Ilhami <[EMAIL PROTECTED]>:
> 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
> >
> >
> >
> >
>
>
--
Cheers,
Yoel Susanto
www.indo-oracletech.com
[Non-text portions of this message have been removed]
