Thank's Pak Benyamin, atas solusinya, saya sudah berhasil create, tapi
dari trigger yang anda contohkan saya ada perbedaan dimana dalam IF
saya tidak memakai to_number karena ada kesalahan pembacaan logic
karena jika dlm number maka bulan februari 2004 dianggap > dari bulan
jan 2005 dst, maka saya gunakan to_date sehingga saat if condition
bulan feb 2004 adalah < dari jan 2005, karena saat perbandingan tetap
dianggap sebagai tanggal.
Thank's
Regard's
OKA
On 7/4/05, Johanes Benyamin <[EMAIL PROTECTED]> wrote:
> Halo,
>
> Saya mencoba membantu menjawab persoalan anda,
>
> 1. Misalkan table invoice dibuat berdasarkan script berikut :
> Sintaks :
> create table invoice_data_trx
> (
> no_invoice varchar2(100),
> tanggal_invoice date
> )
>
> 2. Trigger untuk proses insert,update,delete ke table invoice_data_trx
> Sintaks :
> CREATE OR REPLACE TRIGGER INVOICE_DATA_TRX_T BEFORE INSERT OR UPDATE
> OR DELETE ON INVOICE_DATA_TRX FOR EACH ROW
> DECLARE
> v_bulan_invoice_old number:=to_number(to_char(:OLD.TANGGAL_INVOICE,'mm'));
> v_tahun_invoice_old number:=to_number(to_char(:OLD.TANGGAL_INVOICE,'rrrr'));
> v_bulan_invoice_new number:=to_number(to_char(:NEW.TANGGAL_INVOICE,'mm'));
> v_tahun_invoice_new number:=to_number(to_char(:NEW.TANGGAL_INVOICE,'rrrr'));
> v_bulan_sysdate number:=to_number(to_char(SYSDATE,'mm'));
> v_tahun_sysdate number:=to_number(to_char(SYSDATE,'rrrr'));
> BEGIN
>
> IF (to_number(to_char(:OLD.TANGGAL_INVOICE,'rrrrmmdd')) <
> to_number(to_char(SYSDATE,'rrrrmm')||'06') ) then
> IF to_number(v_tahun_invoice_old||v_bulan_invoice_old) <
> to_number(v_tahun_sysdate||(v_bulan_sysdate-1)) then
> /*pesan error dapat dimodifikasi sesuai kebutuhan */
> RAISE_APPLICATION_ERROR(-20001,'Cannot insert, update or delete to
> table invoice_data_trx because your data is incorrect .');
> END IF;
> END IF;
>
> IF (to_number(to_char(:NEW.TANGGAL_INVOICE,'rrrrmmdd')) <
> to_number(to_char(SYSDATE,'rrrrmm')||'06') ) then
> IF to_number(v_tahun_invoice_new||v_bulan_invoice_new) <
> to_number(v_tahun_sysdate||(v_bulan_sysdate-1)) then
> /*pesan error dapat dimodifikasi sesuai kebutuhan */
> RAISE_APPLICATION_ERROR( -20001,'Cannot insert, update or delete to
> table invoice_data_trx because your data is incorrect .');
> END IF;
> END IF;
> END;
>
> Contoh Kasus :
>
> Asumsi sysdate adalah tanggal 4 Juli
>
> Isi Data Invoice_Data_TRX adalah :
> --------------------------------------------
> | No_Invoice | Tanggal_Invoice |
> --------------------------------------------
> | 11 | 8/5/2005 |
> --------------------------------------------
> | 12 | 5/5/2001 |
> --------------------------------------------
>
> 1. Proses insert untuk bulan mei 2005 ke bawah, termasuk data2 tahun
> 2000,2001, 2002,2003,2004, akan gagal dengan mengeluarkan pesan
> sebagai berikut :
>
> Sintaks :
> INSERT INTO INVOICE_DATA_TRX (NO_INVOICE, TANGGAL_INVOICE)
> VALUES ('1','05-JULY-2000');
>
> Error :
> ORA-20001: Cannot insert, update or delete to table invoice_data_trx
> because your data is incorrect .)*
> ORA-06512: at "INVOICE_DATA_TRX_T", line 17
> ORA-04088: error during execution of trigger 'INVOICE_DATA_TRX_T
>
> 2. Proses delete INVOICE_DATA_TRX untuk bulan mei 2005 ke bawah,
> termasuk data2 tahun 2000,2001, 2002,2003,2004, akan gagal dengan
> mengeluarkan pesan sebagai berikut :
>
> Sintaks :
> delete INVOICE_DATA_TRX where NO_INVOICE=12;
>
> Error :
> ORA-20001: Cannot insert, update or delete to table invoice_data_trx
> because your data is incorrect .)*
> ORA-06512: at "INVOICE_DATA_TRX_T", line 17
> ORA-04088: error during execution of trigger 'INVOICE_DATA_TRX_T
>
> 3. Proses update untuk data-data dimana tanggal invoice-nya, bulan mei
> 2005 ke bawah , termasuk data2 tahun 2000,2001, 2002,2003,2004
>
> Sintaks :
> Update INVOICE_DATA_TRX
> set TANGGAL_INVOICE='05-AUG-2000'
> where NO_INVOICE=11;
>
> Error :
> ORA-20001: Cannot insert, update or delete to table invoice_data_trx
> because your data is incorrect .)*
> ORA-06512: at "INVOICE_DATA_TRX_T", line 17
> ORA-04088: error during execution of trigger 'INVOICE_DATA_TRX_T
>
> Note :
> )*ORA-20001: Cannot insert, update or delete to table invoice_data_trx
> because your data is incorrect .<-- pesan ini didapat dapat
> dimodifikasi berdasarkan remark trigger diatas
>
> Semoga bisa membantu,
>
>
> On 7/4/05, Kang Oyib <[EMAIL PROTECTED]> wrote:
> > dear's all,
> >
> > Saya punya problem dlm membuat trigger database, saya ingin agar user
> > tidak dapat insert, update dan delete data setelah melewati tanggal 5
> > untuk bulan sebelumnya. misal, setelah tanggal 5 juli maka user tidak
> > dapat Insert, Update atau delete transaksi bulan juni, mei dst.
> >
> > Terimakasi atas solusinya....
> >
> > regard's
> >
> >
> > K.O
>
>
> --
> -----------I.N.D.O - O.R.A.C.L.E---------------
> Keluar: [EMAIL PROTECTED]
> Website: http://indo-oracle.blogspot.com
> -----------------------------------------------
>
> Bergabung dengan Indonesia Thin Client User Groups,
> Terminal Server, Citrix, New Moon Caneveral, di:
> http://indo-thin.vze.com
> Yahoo! Groups Links
>
>
>
>
>
>
>
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [EMAIL PROTECTED]
Website: http://indo-oracle.blogspot.com
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.vze.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
