Oracle Trigger error: Bad bind variable 'NEW'

Question

I am trying to create a trigger for checking capacity, inputs of 2 function get_max_capacity and get_current_capacity have OBJECT_U5 type (My code is below)

CREATE OR REPLACE TYPE object_u5 AS OBJECT (
        student_id  VARCHAR2(1000),
        course_id   VARCHAR2(1000),
        sec_id      VARCHAR2(1000),
        semester    VARCHAR2(1000),
        year        NUMBER(4,0),
        grade       VARCHAR2(3)
);
CREATE OR REPLACE FUNCTION get_current_capacity (
        course_register IN object_u5
) RETURN NUMBER IS
        current_capacity NUMBER(38);
BEGIN
        SELECT
                COUNT(*)
        INTO current_capacity
        FROM
                takes
        WHERE
                        course_id = course_register.course_id
                AND sec_id = course_register.sec_id
                AND year = course_register.year
                AND semester = course_register.semester;
END;
CREATE OR REPLACE TRIGGER check_capacity BEFORE
        INSERT ON takes
        FOR EACH ROW
ENABLE DECLARE
        max_capacity  NUMBER(4, 0);
        current_capacity  NUMBER(4, 0);
BEGIN
        max_capacity := get_max_capacity(:new);
        current_capacity := get_current_capacity(:new);
        IF ( max_capacity > cur_capacity ) THEN
                dbms_output.put_line('Insert successfully');
        ELSE
                dbms_output.put_line('The classroom is full! Choose the others');
        END IF;
END;

2 errors which were returned: PLS-00049: bad bind variable 'NEW'

You can't reference the entire row in a trigger in a single variable. :NEW does not exist, it is a prefix for a column in the table the trigger is on. You can do something like :NEW.course_id — Koen Lostrie
– Koen Lostrie, Commented Jul 11, 2021 at 10:43

Littlefoot · Accepted Answer · 2021-07-11 10:43:09Z

Exactly; :new what? Should've been e.g.

    current_capacity := get_current_capacity
          (object_u5(:new.student_id, 
                     :new.course_id, 
                     :new.sec_id,
                     :new.semester, 
                     :new.year, 
                     :new.grade));

When fixed, trigger compiles:

SQL> CREATE OR REPLACE TRIGGER check_capacity BEFORE
  2          INSERT ON takes
  3          FOR EACH ROW
  4  ENABLE DECLARE
  5          max_capacity  NUMBER(4, 0);
  6          current_capacity  NUMBER(4, 0);
  7  BEGIN
  8          max_capacity :=
  9            get_max_capacity(object_u5(:new.student_id,
 10                                       :new.course_id,
 11                                       :new.sec_id,
 12                                       :new.semester,
 13                                       :new.year,
 14                                       :new.grade));
 15          current_capacity :=
 16            get_current_capacity(object_u5(:new.student_id,
 17                                       :new.course_id,
 18                                       :new.sec_id,
 19                                       :new.semester,
 20                                       :new.year,
 21                                       :new.grade));
 22
 23          IF ( max_capacity > current_capacity ) THEN
 24                  dbms_output.put_line('Insert successfully');
 25          ELSE
 26                  dbms_output.put_line('The classroom is full! Choose the others');
 27          END IF;
 28  END;
 29  /

Trigger created.

SQL>

Collectives™ on Stack Overflow

Oracle Trigger error: Bad bind variable 'NEW'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related