CODEWITHSUNDEEP
sqloracle-databaseplsql
Dab Sauce
Dab Sauce

Reputation: 59

PL/SQL - Creating a trigger that prevents the update/insertion of a value with a date after the current date

I need to figure out a way to create a trigger that prevents an accident from being inserted or updated if that accident has a day/time past the current system date.

Table code:

create table Accident
(Report_nr  varchar2(4),
 Accident_date  date,
 Location   varchar2(20),
 primary key (Report_nr));

My attempt (incomplete obviously) with pseudocode

CREATE OR REPLACE TRIGGER trigger1
    BEFORE INSERT OR UPDATE ON accident
    FOR EACH ROW
DECLARE
    cur_date DATE := SYSDATE;
    e EXCEPTION;
BEGIN
    IF :new.accident_date <= cur_date THEN
        [allow insertion/update]
    ELSE
        [dont allow]
    END IF;
END;

I might not be anywhere close, to be honest this is not my strong suit. Any help or guidance would be appreciated. I'm running this on an Oracle server.

Upvotes: 0

Views: 195

Answers (1)

Alex Poole
Alex Poole

Reputation: 191580

You are making it a bit more complicated than it needs to be. The default is for the insert to happen, so you just need to identify when it should not, and throw an exception at that point that causes the inert to be rejected; something like:

CREATE OR REPLACE TRIGGER trigger1
    BEFORE INSERT OR UPDATE ON accident
    FOR EACH ROW
BEGIN
    IF :new.accident_date > SYSDATE THEN
        RAISE_APPLICATION_ERROR(-20001, 'Accident date cannot be in the future');
    END IF;
END;
/

Today's date works:

insert into accident values ('0001', date '2017-11-15', 'X');

1 row inserted.

Tomorrow's does not:

insert into accident values ('0002', date '2017-11-16', 'Y');

Error starting at line : 18 in command -
insert into accident values ('0002', date '2017-11-16', 'Y')
Error report -
ORA-20001: Accident date cannot be in the future
ORA-06512: at "MY_SCHEMA.TRIGGER1", line 3
ORA-04088: error during execution of trigger 'MY_SCHEMA.TRIGGER1'

Read more about raising exceptions.

Upvotes: 1

Related Questions