Oracle error when trying to create a trigger

September 8, 2023

I am new to databases and I know this is a simple question but I cannot find an answer anywhere, so I appreciate the help.

I have 2 tables, each with the same structure.

Table1
CaseNum Number(9,0)
CaseStat Number(5,0)
OpenDate Date

Table2
CaseNum Number(9,0)
CaseStat Number(5,0)
OpenDate Date

Then I am trying to create this trigger, which will insert a new row into table2 every time table1 is inserted or updated. The point is to make Table2 a history of changes in table1:

CREATE OR REPLACE TRIGGER TABLE1_HIST
AFTER INSERT OR UPDATE ON TABLE1
FOR EACH ROW

BEGIN
   INSERT INTO TABLE2
   VALUES (CaseNum, CaseStat, sysdate);
END;

When I try to create it, I get the following errors:

Error(2,3): PL/SQL: SQL Statement ignored
Error(3,20): PL/SQL: ORA-00984: column not allowed here

But I have checked and re-checked the column types, and they look to me to all be the same.

The thing is, when I replace VALUES (CaseNum, CaseStat, sysdate); with hard-coded numbers, it allows me to create the trigger!

This works:

CREATE OR REPLACE TRIGGER TABLE1_HIST
AFTER INSERT OR UPDATE ON TABLE1
FOR EACH ROW

BEGIN
   INSERT INTO TABLE2
   VALUES (123456789, 12345, sysdate);
END;

However, that is obviously not useful.

Any suggestions at all are appreciated.

>Solution :

You must specify values in the insert statement. PL/SQL allows you to use default bind variables to reference the row-level values of the update or insert within the trigger, like this:

CREATE OR REPLACE TRIGGER TABLE1_HIST
AFTER INSERT OR UPDATE ON TABLE1
FOR EACH ROW

BEGIN
   INSERT INTO TABLE2 (casenum, casestat, opendate)
   VALUES (:new.casenum, :new.casestat, sysdate);
END;

Also, it is generally better to specify things like column order explicitly rather than trust to the column order in the table, which can be altered.