Reputation: 29
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
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