BCS403 Program 4

4. Create a row level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old & new Salary. CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)

Step 1: Create the CUSTOMERS Table:

CREATE TABLE CUSTOMERS (
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(100),
    AGE NUMBER,
    ADDRESS VARCHAR2(200),
    SALARY NUMBER
);

Step 2: Create the Trigger:

CREATE OR REPLACE TRIGGER salary_difference_trigger
AFTER INSERT OR UPDATE OR DELETE
ON CUSTOMERS
FOR EACH ROW
DECLARE
    v_old_salary CUSTOMERS.SALARY%TYPE;
    v_new_salary CUSTOMERS.SALARY%TYPE;
    v_salary_diff CUSTOMERS.SALARY%TYPE;
BEGIN
    IF INSERTING THEN
        v_old_salary := 0;
        v_new_salary := :NEW.SALARY;
    ELSIF UPDATING THEN
        v_old_salary := :OLD.SALARY;
        v_new_salary := :NEW.SALARY;
    ELSIF DELETING THEN
        v_old_salary := :OLD.SALARY;
        v_new_salary := 0;
    END IF;

    v_salary_diff := v_new_salary - v_old_salary;

    DBMS_OUTPUT.PUT_LINE('Salary difference: ' || v_salary_diff);
END;

Step 3: Test the Trigger: Now, perform INSERT, UPDATE, and DELETE operations on the CUSTOMERS table to see the trigger in action.

Insert Operation:

INSERT INTO CUSTOMERS 
VALUES (1, 'Braham Kumar', 30, 'Bangalore', 8000);

Update Operation:

UPDATE CUSTOMERS
SET SALARY = 6000
WHERE ID = 1;

Delete Operation:

DELETE FROM CUSTOMERS
WHERE ID = 1;

Leave a Reply

Your email address will not be published. Required fields are marked *