CODEWITHSUNDEEP
oracle-databaseplsql
user10575490
user10575490

Reputation:

error in log trigger before delete, i can't find error

Where is the mistake, it seems to me good, but I get the error.

I tried and this referencing old as old new as new.

Maybe the error is in the variables.

SQL> create or replace trigger before_delete
      2         before delete
      3     on accounts
      4  declare 
      5     v_username varchar2(20);
      6   begin
      7     select user into v_username
      8     from dual;
      9     insert into accounts_history
     10     (
     11         id, 
     12             new_name,
     13             old_name,
     14             new_amount,
     15             old_amount,
     16             change_date,
     17         delted_by
     18     )
     19     values
     20     (      
     21         :old.acc_id
     22         :old.acc_name
     23         :old.acc_name
     24         :old.acc_amount
     25         :old.acc_amount
     26         sysdate,
     27         v_username
     28   );
     29   end;
     30   /
        insert into accounts_history
            *
    ERROR at line 9:
    ORA-04082: NEW or OLD references not allowed in table level triggers

Upvotes: 0

Views: 34

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

The error seems fairly clear here - you have a statement level trigger, not a row level trigger (no for each row clause), and you can't use the old/new pseudorecords at statement level.

As it looks like that is actually want you want, you need to add that clause:

create or replace trigger before_delete
before delete
on accounts
for each row
begin
    insert into accounts_history
    (
        id,
        new_name,
        old_name,
        new_amount,
        old_amount,
        change_date,
        delted_by
    )
    values
    (
        :old.acc_id,
        :old.acc_name,
        :old.acc_name,
        :old.acc_amount,
        :old.acc_amount,
        sysdate,
        user
   );
end;
/

You don't need to query from dual to get the user into a variable, you can assign it directly; but you don't need that variable at all; you can refer to user call directly in the values clause.

Upvotes: 3

Related Questions