Q

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."

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;
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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close