Ask the Expert

ORA-04082: NEW or OLD references not allowed in table level triggers

I am calling a stored procedure from a trigger. It shows the error: "ORA-04082: NEW or OLD references not allowed in table level triggers." The procedure is created with no error. But the trigger is not created. The trigger is:
CREATE OR REPLACE TRIGGER my_test_trigger
BEFORE INSERT ON my_table
DECLARE
   v_out   number ;
BEGIN
   my_procedure(:new.column1, :new.column2, v_out) ;
   :new.column3 := v_out ;
END;

    Requires Free Membership to View

There are two types of DML triggers: statement level and row level. The statement level trigger fires once per transaction, while the row level trigger fires for each record effected, per transactions. In order to use :new or :old, the trigger must be a row level trigger. The trigger you have created is a statement trigger. To change your trigger to a row level trigger, you must add the following line:
        FOR EACH ROW
Your trigger will then look like:
CREATE OR REPLACE TRIGGER my_test_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
DECLARE
   v_out   number ;
BEGIN
   my_procedure(:new.column1, :new.column2, v_out) ;
   :new.column3 := v_out ;
END;

This was first published in April 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: