Skip to Main Content

PLS-00049: bad bind variable in the Database Trigger

994122Apr 3 2014 — edited Apr 3 2014

Hi all,

If the ZIPCODE table has no corresponding record, the trigger should create a new record for the given value of zip before adding a new record to the STUDENT table

SQL> CREATE TABLE student

      (

         zip           VARCHAR2(5),

         student_id    NUMBER,

         created_by    VARCHAR2(10),

         created_date  DATE,

         modified_by   VARCHAR2(10),

         modified_date DATE

      ); 

SQL> CREATE TABLE zipcode

      (

         zip           VARCHAR2(5),

         zip_id        NUMBER,

         created_by    VARCHAR2(10),

         created_date  DATE,

         modified_by   VARCHAR2(10),

         modified_date DATE

      );

SQL> CREATE VIEW student_v

    AS

      SELECT *

      FROM   student;

View created.

SQL> CREATE OR replace TRIGGER student_ins

     instead OF INSERT ON student_v

     FOR EACH ROW

    DECLARE

          v_zip VARCHAR2(5);

    BEGIN

            BEGIN

                SELECT zip

                INTO   v_zip

               FROM   zipcode

               WHERE  zip = :NEW.zip;

           EXCEPTION

               WHEN no_data_found THEN

                 INSERT INTO zipcode

                             (zip,

                              zip_id,

                              created_by,

                              created_date,

                              modified_by,

                              modified_date)

                 VALUES      (:NEW.zip,

                                     :NEW.zip_id,  -->Not commented

                                      USER,

                                     SYSDATE,

                                     USER,

                                    SYSDATE);

           END;

           INSERT INTO student

                       (zip,

                        student_id,

                        created_by,

                        created_date,

                        modified_by,

                        modified_date)

           VALUES      (:NEW.zip,

                              :NEW.student_id,

                              USER,

                              SYSDATE,

                              USER,

                              SYSDATE);

    end;

Waning: Trigger created with compilation errors.

SQL> show error

Errors for TRIGGER STUDENT_INS:

LINE/COL ERROR

-------- -------------------------------------------

19/21    PLS-00049: bad bind variable 'NEW.ZIP_ID'

SQL> ed

  CREATE OR replace TRIGGER student_ins

   instead OF INSERT ON student_v

   FOR EACH ROW

  DECLARE

        v_zip VARCHAR2(5);

  BEGIN

          BEGIN

              SELECT zip

              INTO   v_zip

              FROM   zipcode

              WHERE  zip = :NEW.zip;

          EXCEPTION

              WHEN no_data_found THEN

                INSERT INTO zipcode

                            (zip,

                             zip_id,

                             created_by,

                             created_date,

                             modified_by,

                             modified_date)

                VALUES      (:NEW.zip,

                                ---:NEW.zip_id,  --> commented Replace with NULL

                                    NULL,

                                    USER,

                                   SYSDATE,

                                   USER,

                                    SYSDATE);

          END;

          INSERT INTO student

                      (zip,

                       student_id,

                       created_by,

                       created_date,

                       modified_by,

                       modified_date)

          VALUES      (:NEW.zip,

                             :NEW.student_id,

                             USER,

                             SYSDATE,

                            USER,

                            SYSDATE);

   end;

SQL> /

SQL> insert into student_v(zip) values('111');

1 row created.

SQL> select * from student_v

  2  /

ZIP   STUDENT_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_

----- ---------- ---------- --------- ---------- ---------

111              APPS       03-APR-14 APPS       03-APR-14

SQL> select * from student

  2  /

ZIP   STUDENT_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_

----- ---------- ---------- --------- ---------- ---------

111              APPS       03-APR-14 APPS       03-APR-14

SQL> select * from zipcode

  2  /

ZIP       ZIP_ID CREATED_BY CREATED_D MODIFIED_B MODIFIED_

----- ---------- ---------- --------- ---------- ---------

111              APPS       03-APR-14 APPS       03-APR-14

Comments
Post Details
Added on Apr 3 2014
9 comments
1,712 views