user3441417 user3441417 - 6 months ago 16
SQL Question

SQL trigger to show salary change

I am trying to create a simple trigger in sql developer to display the change in salary when it is changed

CREATE OR REPLACE TRIGGER salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON FACULTY
FOR EACH ROW

DECLARE
sal_diff NUMBER;

BEGIN
sal_diff := :NEW.F_SALARY - :OLD.F_SALARY;

DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
END;


when I attempt to run the trigger it prompts be to enter binds for NEW and OLD and when i try run an update to see if it works it states the trigger failed. So how am i using the old and new tags incorrectly? or is that not the issue

Answer

There are several issues with your code.

  1. You need to create a after trigger instead of the before trigger.

  2. You are trying to write a trigger that performs an operation for insert,delete or update. So you should do the conditional checks (such as if inserting,deleting or updating) clause.

  3. Also, when you do delete, there is no new value, but just the old value.

I would change your trigger as below..

CREATE OR REPLACE TRIGGER salary_changes
AFTER DELETE OR INSERT OR UPDATE ON FACULTY
FOR EACH ROW

DECLARE
  sal_diff  NUMBER;
BEGIN
 If (INSERTING or UPDATING) then 
     sal_diff  :=  :NEW.F_SALARY  - :OLD.F_SALARY;
     DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
  END IF;

 IF DELETING THEN 
    DBMS_OUTPUT.PUT_LINE('The deleted value is:' || :OLD.F_SALARY);
 END IF;   
END;
Comments