create or replace trigger t1
before update of price on book
declare
vdif number;
begin
vdif:=:new.price-:old.price;
dbms_output.put_line('Price Diff is '||vdif);
end;
I'm getting this error:
NEW or OLD references not allowed in table level triggers
As the error message says, you can't use :new or :old in a table-level trigger. You need a row-level trigger. So you'd need to add FOR EACH ROW to your declaration
create or replace trigger t1
before update of price on book
for each row
declare
vdif number;
begin
vdif:=:new.price-:old.price;
dbms_output.put_line('Price Diff is '||vdif);
end;
Of course, in reality, you'd never write a trigger that just wrote to the dbms_output buffer (nor would you write production code that depended on anyone seeing anything written to dbms_output). But I assume that you are a student and you're just doing this as part of a homework assignment.
Trigger you wrote is a table level trigger, and table level triggers fire once for each operation on the table. So, for example, if you had a query that updates multiple rows, the trigger would be called just once so :new and :old doesn't know what row to affect.
What you actually want is to add a FOR EACH ROW clause in the trigger definition (under "before update..."), which will make your trigger a row level trigger and it will fire for each row you are updating.
