Link to home
Start Free TrialLog in
Avatar of chezbrgrs
chezbrgrsFlag for United States of America

asked on

Trigger comparing columns on UPDATE

Hi Experts -
I'm trying to write an UPDATE trigger for an Oracle database.  The goal is to check an existing date column to make sure the newly updated column is later.
Here's what I have:

CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE date1 ON sometable1
FOR EACH ROW
BEGIN
  IF (_______date2 < _______date1) THEN
        RAISE_APPLICATION_ERROR(-20001, Date2 must be greater than Date1);
    END IF;
END;

When I do an insert with a date2 that is before date1, the trigger is not firing.  I don't know what to put in the underlined fields above (:new., :old, etc.).

Thanks for any insight you can provide.

chezbrgrs
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image


CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE date1 ON sometable1
FOR EACH ROW
BEGIN
IF (:new.date1 < :old.date1) THEN
   RAISE_APPLICATION_ERROR(-20001, New Date must be greater than Old Date); 
END IF; 
END;

Open in new window

Avatar of jwahl
do you have a NOT NULL constraint on date1?
if not, you have to compare with NVL() or do an additional check.


CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE date1 ON sometable1
FOR EACH ROW
BEGIN
  IF :NEW.date1 < :OLD.date1 THEN
     RAISE_APPLICATION_ERROR(-20001, 'New Date must be greater than old Date!');
  END IF;
END;

Open in new window

Avatar of chezbrgrs

ASKER

Sorry, I wasn't clear.  The dates are in two different columns (startdate and enddate).  Wouldn't this solution just be looking at one column?
Then, you dont need a trigger at all. Just create a CHECK constraint on the table as below : -

ALTER TABLE sometable1 ADD CONSTRAINT someconstraint1 CHECK (start_date <= end_date) ENABLE VALIDATE;

Above would throw error if the existing data in the table does not follow the constraint rule. In that case, u may use the NOVALIDATE option to ignore validation of existing data.
in this case you also can define a check constraint:





ALTER TABLE my_table ADD 
CONSTRAINT date_test
 CHECK (startdate < enddate) ENABLE
 VALIDATE;

Open in new window

jinesh_kamdar is always faster ... ;-)
@jwahl - Guess my TOAD works a bit faster ;-)
... i don't think it's only the TOAD  ... ;-)
The CHECK constraint would be much easier but I've been asked to use a TRIGGER and not alter the table.  Can it be done with a TRIGGER?
ASKER CERTIFIED SOLUTION
Avatar of jwahl
jwahl
Flag of Austria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think the IF condition should be IF :NEW.enddate < :NEW.startdate THEN
jwahl -

Thanks I'll try this this evening and will let you know the results.  Thanks.

chezbrgrs
@jinesh_kamdar: you are right when the trigger is inserting then :OLD.startdate is null.

i think he should do different checks depending on insert/update:

...
   IF UPDATING THEN
       ....
   ELSIF INSERTING THEN
       ...
   END IF;
...
The :old value worked because startdate was not NULL.  Thanks for contributing.

chezbrgrs