Experiment No 26: Implement triggers for given database

 Experiment No 26: Implement triggers for given database

Result:
PL/SQL triggers were successfully implemented on the database. They executed automatically on the specified events, maintained data integrity, and handled errors as intended. The experiment demonstrated automation of database operations and enforcement of business rules.

---

1. Difference between Row-Level Trigger and Statement-Level Trigger

Feature	Row-Level Trigger	Statement-Level Trigger
Execution	Fires once for each row affected by the DML operation	Fires once for the entire DML statement, regardless of number of rows affected
Syntax	FOR EACH ROW is used	No FOR EACH ROW clause
Use case	When you need to perform actions on individual rows (e.g., auditing changes to each row)	When you want to perform an action once per DML statement (e.g., logging the DML operation)
OLD and NEW references	Can use :OLD and :NEW to refer to column values	Cannot use :OLD or :NEW references
Example	AFTER INSERT ON emp FOR EACH ROW	AFTER INSERT ON emp

Summary: Row-level triggers act per row; statement-level triggers act per DML statement.

---

2. Purpose of using WHEN clause in a trigger

• The WHEN clause specifies a condition that must be true for the trigger to fire.

• It allows conditional execution of triggers, so the trigger only executes for specific rows or situations.

• Example in Oracle:

CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE UPDATE ON emp
FOR EACH ROW
WHEN (NEW.salary > 50000)
BEGIN
   DBMS_OUTPUT.PUT_LINE('Salary exceeds 50,000 for employee: ' || :NEW.emp_name);
END;

Explanation: Here, the trigger fires only if the new salary exceeds 50,000.

---

Exercise

1. Trigger on EMP table when salary is below 5000

Objective: Fire a trigger before inserting or updating a record in EMP table if the salary is below 5000.

CREATE OR REPLACE TRIGGER trg_emp_salary_check
BEFORE INSERT OR UPDATE ON EMP
FOR EACH ROW
WHEN (NEW.salary < 5000)
DECLARE
BEGIN
    DBMS_OUTPUT.PUT_LINE('Warning: Salary of employee ' || :NEW.emp_name || ' is below 5000.');
END;
/

here:

• BEFORE INSERT OR UPDATE ensures the trigger checks salary before data modification.

• FOR EACH ROW makes it a row-level trigger.

• WHEN (NEW.salary < 5000) ensures it only fires when salary is below 5000.

• DBMS_OUTPUT.PUT_LINE prints a message to alert the user.

Test Example:

INSERT INTO EMP (emp_id, emp_name, salary, dept_no)
VALUES (101, 'Rahul Sharma', 4500, 10);

UPDATE EMP
SET salary = 4000
WHERE emp_id = 101;

Expected Output:

Warning: Salary of employee Rahul Sharma is below 5000.

---

2. Trigger on Department table for updates

Objective: Fire a trigger after any update on the DEPARTMENT table to log or notify changes.

CREATE OR REPLACE TRIGGER trg_dept_update
AFTER UPDATE ON DEPARTMENT
FOR EACH ROW
DECLARE
BEGIN
    DBMS_OUTPUT.PUT_LINE('Department updated. Old Name: ' || :OLD.dept_name || 
                         ', New Name: ' || :NEW.dept_name);
END;
/

here:

• AFTER UPDATE ensures the trigger fires after the record is updated.

• FOR EACH ROW makes it row-level to capture changes per department.

• :OLD and :NEW are used to track changes in department name.

Test Example:

UPDATE DEPARTMENT
SET dept_name = 'Finance & Accounts'
WHERE dept_no = 10;

Expected Output:

Department updated. Old Name: Finance, New Name: Finance & Accounts

---