CODEWITHSUNDEEP
oracle-databaseplsqltriggerscursor
Thapipo
Thapipo

Reputation: 311

Oracle PL/SQL Cursor in Trigger

I got this trigger, seems to be alright but i got the error :

Trigger :

CREATE OR REPLACE TRIGGER TRG_TABLE_BI
BEFORE INSERT
ON TABLE
FOR EACH ROW
DECLARE
    CURSOR cur_list IS
        SELECT emp, name, day, salary, id,phone
        FROM TABLE NATURAL JOIN OTHERTABLE
        WHERE emp = :NEW.emp AND name = :NEW.name AND id = :NEW.id;

    curChoice cur_list%ROWTYPE;

BEGIN
    OPEN cur_list;
    LOOP
        FETCH cur_list INTO curChoice;
        EXIT WHEN cur_list%NOTFOUND;
        dbms_output.put_line(curchoice.id);
        dbms_output.put_line(curChoice.emp);
        dbms_output.put_line(curchoice.name);
    END LOOP;
    CLOSE cur_list;

END;
/

I got those error : 

PLS-00049: bad bind variable 'NEW.emp'

If i remove the ':' in the cursor declaration in my WHERE clause, i got a lot more error

Thank you.

Upvotes: 1

Views: 13466

Answers (1)

Thapipo
Thapipo

Reputation: 311

Ok, i found the error, in my cursor declaration in the WEHRE clause, i had to remove 

emp = :NEW.emp AND name = :NEW.name

because there's no such column in the table that im currently inserting.

Thank !

Upvotes: 0

Related Questions