CODEWITHSUNDEEP
sqloracle-databasetriggerssysdate
leprejohn
leprejohn

Reputation: 3

Using Sysdate in trigger

Hello guys I'm a bit of a noob when it comes to triggers so I'm just looking for some advice on how to do the follow trigger.

I have created a trigger that will throw an error message if someone was to delete a record during office hours however I would like to create another trigger that uses SYSDATE will not delete records from say today and future dates.

I was thinking of maybe using >=SYSDATE but I'm not sure if that is a valid sql statement.

CREATE OR REPLACE TRIGGER records_delete 
BEFORE DELETE 
ON RECORDS FOR EACH ROW 
BEGIN 
    IF TO_CHAR(SYSDATE, 'HH24MI') NOT >= sysdat 
       RAISE_APPLICATION_ERROR(-20669, 'You can not delete current or future records'); 
    END IF; 
END records_delete;

Thanks, leprejohn

Upvotes: 0

Views: 2038

Answers (1)

PlanItMichael
PlanItMichael

Reputation: 334

The problem here is that you're not referencing any of the fields of the table that you're deleting from.

If your table had a column called record_date then you could rewrite this as:

CREATE OR REPLACE TRIGGER records_delete 
BEFORE DELETE 
ON RECORDS FOR EACH ROW 
BEGIN 
    IF (:old.record_date >= SYSDATE) THEN
       RAISE_APPLICATION_ERROR(-20669, 'You can not delete current or future records'); 
    END IF; 
END records_delete;

The syntax :old. and :new. are how you reference the columns of the current record being acted upon by the trigger, with :old. being the prefix for inspecting the values before the trigger acts on it and :new. being for values after the trigger is done. This syntax allows you to see the values before/after the trigger updates the data, which in your case doesn't matter since you're deleting records.

If you want to disregard the hours, mins, seconds of the date fields, use this in the IF statement

trunc(:old.record_date) >= trunc(SYSDATE)

If record_date is actually stored as a string instead of a date, you would convert it to a date before comparison:

to_date(:old.record_date, 'DDMMYYYY') >= trunc(SYSDATE)

Update the format mask 'DDMMYYYY" to the format your date is actually stored in. Check the Oracle documentation for description of to_date and date format models.

Give this a shot and see if it works.

Upvotes: 1

Related Questions