The following trigger was created properly and it gets fired before UPDATE and INSERT statements. But it does not display anything when DELETE stmt is issued. Please help me identify the problem here.
create or replace trigger tr_emp_saldiff
BEFORE INSERT OR UPDATE OR DELETE
ON emp
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (new.emp_no > 0)
declare
sal_diff number;
begin
sal_diff := :new.salary - :old.salary;
dbms_output.put(' Old salary: ' || :old.salary);
dbms_output.put(' New salary: ' || :new.salary);
dbms_output.put_line(' Difference ' || sal_diff);
END;
/
UPDATE emp set salary = salary + 500 where dept_no = 10;
Output :
1 row(s) updated.
Old salary: 20500 New salary: 21000 Difference 500
INSERT INTO EMP VALUES(1000,'Alice',15000,'Fiona',30);
Output :
1 row(s) inserted.
Old salary: New salary: 20000 Difference
DELETE FROM emp where emp_name='Jane';
Output :
1 row(s) deleted.
No values displayed after delete statement.
WHEN (new.emp_no > 0)
That cannot be true for a DELETE, new.emp_no is null then.
Remove that condition or use a separate trigger on DELETE. (As it is given it does only make sense for an UPDATE anyway. There is no change in salary on an INSERT or a DELETE.)