SP2-0552: Bind variable "NEW" not declared and END Error report - Unknown Command

Question

I have to write a trigger for the tables I made and in insert update, I have to record a separate log table for those that are updated or inserted.

Columns in the log table will be like;

• Done_process (will write update, insert)
• Person (student number of the person treated)
• Before (previous value for update, blank for insert)
• After (new value for update, new value for insert)

This is my student_info table,

CREATE TABLE student_info (
    school_id NUMBER,
    id_no NUMBER NOT NULL UNIQUE,
    name VARCHAR2(50) NOT NULL,
    surname VARCHAR2(50) NOT NULL,
    city VARCHAR2(50) NOT NULL,
    birth_date DATE NOT NULL,
    CONSTRAINT student_info_pk PRIMARY KEY(school_id )
);

CREATE TABLE og_log(
        done_process VARCHAR2(30),
        person VARCHAR2(30),
        before VARCHAR2(30),
        after VARCHAR2(30)
    );

CREATE OR REPLACE TRIGGER og_trigger
BEFORE INSERT OR UPDATE OR DELETE ON student_info
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
ENABLE
DECLARE
BEGIN
   IF INSERTING THEN 
      INSERT INTO og_log(done_process, person, before, after)
       VALUES ('Insert',:new.school_id,:old.name,:new.name);
   ELSIF UPDATING THEN
     INSERT INTO og_log(done_process, person, before, after)
       VALUES ('Update',:new.school_id,:old.name,:new.name);
   END IF;
END;
/

When I try to run the code it gave an error as follows;

> Trıgger OG_TRIGGER created.
> 
> 
> Error starting at line : 280 in command - ELSIF UPDATING THEN Error
> report - Unknown Command
> 
> SP2-0552: Bind variable "NEW" not declared.
> 
> 0 rows inserted.
> 
> 
> Error starting at line : 283 in command - END IF Error report -
> Unknown Command
> 
> SP2-0044: For a list of known commands enter HELP and to leave enter
> EXIT.
> 
> Error starting at line : 284 in command - END Error report - Unknown
> Command

Answer 1

I believe you are creating this trigger for learning purpose and not something a real use case because what you do in trigger doesn't really making any sense.

The trigger you have mentioned is not compiling due to syntactical problems like where v_id := 20201033.

Where clause is used to compare the value and thus you should use = instead := which is an assignment operator.

Besides this problem few points which still needs to be taken care

1. Give a explicit convention for creating local variables. e.g. you have created a local variable v_id and the same column is also available in student_info table. Though it is not a problem in this case but it's good practice to keep the local variable specific like let's say l_v_id.
2. You have used a select statement inside trigger which could leads to NO_DATA_FOUND error and you should handle it by either in the exception section or another way would be using aggregate function like max() if obviously v_id is primary key. I doubt why you need this select statement ( you could use between old and new using something like coalesce(:old.school_id,:new_schoold_id) if I understood you) but I would leave it open to you to decide and act accordingly.

Considering above points final code will be,

CREATE OR REPLACE TRIGGER og_trigger
BEFORE INSERT OR UPDATE OR DELETE ON student_info
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
ENABLE
DECLARE
BEGIN
   IF INSERTING THEN 
      INSERT INTO og_log(done_process, person, before, after)
       VALUES ('Insert',:new.school_id,:old.city,:new.city);
   ELSIF UPDATING THEN
     INSERT INTO og_log(done_process, person, before, after)
       VALUES ('Update',:new.school_id,:old.city,:new.city);
   END IF;
END;
/

Find demo db<>fiddle

---

---

EDITED: Solving probably tool issue

I doubt the issue is with SQL Developer tool usage , however last try i would like to make,

Step1: Drop both the tables used by issuing drop command

drop table STUDENT_INFO;
drop table og_log;

Step2: Open another SQL worksheet using alt+F10 and do as I have shown in the following image. Please try and let me know.