当前位置: 动力学知识库 > 问答 > 编程问答 >

oracle - SQL Error: ORA-04098: trigger

问题描述:

I wrote the following trigger:

CREATE OR REPLACE TRIGGER check_exprdate_tgr

BEFORE INSERT OR UPDATE ON coupon

FOR EACH ROW

BEGIN

IF(:new.Expiry_date<= SYSDATE)

THEN

RAISE_APPLICATION_ERROR(404, 'Coupon Expiry date is not valid. Expiry date must be set to a later date.');

END IF;

END;

The tables are as follows:

CREATE TABLE customer (

Id INTEGER

CONSTRAINT customer_id_nn NOT NULL,

Name VARCHAR2(50)

CONSTRAINT cutomer_name_nn NOT NULL,

Surname VARCHAR2(50)

CONSTRAINT customer_surname_nn NOT NULL,

Dob DATE

CONSTRAINT customer_date_nn NOT NULL,

Email VARCHAR2(50),

Gender VARCHAR2(10)

CONSTRAINT customer_gender_nn NOT NULL,

Residence_number Integer

CONSTRAINT customer_residenceno_nn NOT NULL,

Street VARCHAR2(50)

CONSTRAINT customer_street_nn NOT NULL,

Town_id INTEGER,

CONSTRAINT customer_id_pk PRIMARY KEY(Id),

CONSTRAINT customer_townId_fk FOREIGN KEY(Town_id)

REFERENCES TOWN(Id)

);

CREATE TABLE coupon (

Id INTEGER,

Expiry_date DATE

CONSTRAINT coupon_date_nn NOT NULL,

Discount_percentage NUMBER(4,2)

CONSTRAINT coupon_discperc_nn NOT NULL,

Details VARCHAR2(50),

Customer_id INTEGER

CONSTRAINT coupon_customerid_nn NOT NULL,

CONSTRAINT coupon_id_pk PRIMARY KEY(Id),

CONSTRAINT coupon_customerid_fk FOREIGN KEY(Customer_id)

REFERENCES customer(Id)

);

The aim of the trigger is to check that the expiry date input is set at a later date then the SYSDATE. I compiled the trigger and it compiled correctly so I really don't know what wrong with the code.

Thanks :)

网友答案:

The number in RAISE_APPLICATION_ERROR() must be within the range set aside for user-defined errors, that is -20999 to -20000. It's in the documentation.

RAISE_APPLICATION_ERROR(-20404
           , 'Coupon Expiry date is not valid. Expiry date must be set to a later date.');

It's worth noting that this line will be evaluated every time you update any column on the COUPON table, regardless of whether you have touched EXPIRY_DATE itself:

  IF(:new.Expiry_date<= SYSDATE)

This means you cannot update the table after the coupon has expired. This may be what you want. Otherwise you should consider a more nuanced test, say

   IF ( INSERTING or :new.Expiry_date != :old.Expiry_date )
   AND :new.Expiry_date<= SYSDATE

Triggers are covered in the PL/SQL Reference .

分享给朋友:
您可能感兴趣的文章:
随机阅读: