Using a column from :new into a trigger if, multiple errors-CodePudding

I'm trying to use a column from :new (the incoming new row) to compare with a date in another table and then Raise an error if the conditions are met.

I have tried two solutions:

CREATE OR REPLACE TRIGGER trigger_example
BEFORE INSERT ON STATION
FOR EACH ROW
DECLARE
    d1 DATE;
    d2 DATE;
BEGIN
    SELECT JOUR INTO d1 FROM ELECTION;
    SELECT DATEOUVERTURE INTO d2 FROM NEW;
    IF d1 = d2 THEN
        RAISE_APPLICATION_ERROR(-20001,'damn');
    end if;
end;

This is the one where I select DATEOUVERTURE from NEW and I get this error.

[2021-12-09 01:33:45] 6:39:PL/SQL: ORA-00942: table or view does not exist
[2021-12-09 01:33:45] 6:5:PL/SQL: SQL Statement ignored

But the table exists and I have the appropriate rights to read/write

Second solution:

CREATE OR REPLACE TRIGGER trigger_example
BEFORE INSERT ON STATION
FOR EACH ROW
DECLARE
    d1 DATE;
    d2 DATE;
BEGIN
    SELECT JOUR INTO d1 FROM ELECTION;
    SELECT DATEOUVERTURE INTO d2 FROM :NEW;
    IF d1 = d2 THEN
        RAISE_APPLICATION_ERROR(-20001,'damn');
    end if;
end;

This is the one where I select from :NEW instead of NEW. But I get this error.

[2021-12-09 01:36:56] 6:39:PLS-00049: bad bind variable 'NEW'
[2021-12-09 01:36:56] 6:43:PL/SQL: ORA-00903: invalid table name
[2021-12-09 01:36:56] 6:5:PL/SQL: SQL Statement ignored

Any ideas where it went wrong? I've been looking for hours and can't find a single thing.

CodePudding user response：

You cannot use :NEW like this. Following example should work if you replace

date_column_on_station_table to the column you want to compare with inside STATION table

CREATE OR REPLACE TRIGGER trigger_example
BEFORE INSERT ON STATION
FOR EACH ROW
DECLARE
    d1 DATE;
    d2 DATE;
BEGIN
    SELECT JOUR INTO d1 FROM ELECTION;
    IF d1 = :NEW.date_column_on_station_table THEN
        RAISE_APPLICATION_ERROR(-20001,'damn');
    end if;
end;

Edit: If DATEOUVERTURE is the columns name to compare , then it should be like this:

CREATE OR REPLACE TRIGGER trigger_example
BEFORE INSERT ON STATION
FOR EACH ROW
DECLARE
    d1 DATE;
BEGIN
    SELECT JOUR INTO d1 FROM ELECTION;
    IF d1 = :NEW.DATEOUVERTURE THEN
        RAISE_APPLICATION_ERROR(-20001,'damn');
    end if;
end;