CODEWITHSUNDEEP
sqloracle-databaseoracle11gtriggers
Adrian Vella
Adrian Vella

Reputation: 29

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

Upvotes: 0

Views: 272

Answers (1)

APC
APC

Reputation: 146349

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 .

Upvotes: 1

Related Questions