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
<*> 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/
