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]>:
> 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]
> 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]>:
> > 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]
> > 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]>:
> >
> > > 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>
> > > 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>>:
> > >
> > > > 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>
> > > > 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>
> > > > >:
> > > >
> > > > > 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>
> > > > > 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>
> > > > > >:
> > > > >
> > > > > > 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]
> > 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]
> 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
>
>
>
>
