SQL Triggers

Triggers: These are special types of stored procedural blocks that define actions to be executed automatically when specific database events (like INSERT, DELETE, or UPDATE on a table) occur and a certain condition is met.

• They are defined using CREATE TRIGGER and specify the triggering event, the table it applies to, and the action to be taken.
• Triggers are crucial for tasks such as maintaining database consistency, monitoring updates, or implementing business rules.

Components of a Trigger

Every trigger has three core components:

Component	Description
Event	The DML operation that fires the trigger (INSERT, UPDATE, DELETE)
Condition	Optional WHEN clause that determines whether the trigger action should be executed
Action	The SQL or PL/SQL block that runs when the event and condition are satisfied

Why Use Triggers?

• Enforce complex business rules

• Automatically maintain derived attributes (e.g., totals, averages)

• Perform data validation before accepting changes

• Maintain audit trails of database operations

• Prevent invalid transactions

• Execute cascading changes across related tables

Types of Triggers

Trigger Type	Description
BEFORE	Executes before the triggering event
AFTER	Executes after the triggering event
INSTEAD OF	Executes instead of the triggering event (used for views)
Row-level	Executes once for each affected row
Statement-level	Executes once per triggering statement, regardless of the number of rows affected

Trigger Syntax

CREATE [OR REPLACE] TRIGGER trigger_name
	{BEFORE | AFTER | INSTEAD OF}
	{INSERT [OR] | UPDATE [OR] | DELETE}
	[OF column_name] -- Only for UPDATE triggers
	ON table_name
	[REFERENCING OLD AS o NEW AS n] 
		-- Optional aliases for :OLD and :NEW
	[FOR EACH ROW] -- Row-level trigger
	[WHEN (condition)]  -- Optional
BEGIN
  -- SQL / PL/SQL statements
END;

• :OLD — value before the DML operation

• :NEW — value after the DML operation (Available only in row-level triggers)

Example 1: Age Validation Before Insert/Update

Trigger to prevent inserting or updating an employee whose age is above 60.

CREATE OR REPLACE TRIGGER age_violation
BEFORE INSERT OR UPDATE OF age ON employee
FOR EACH ROW
WHEN (NEW.age > 60)
BEGIN
  inform_supervisor;
END;

Supporting Procedure

CREATE OR REPLACE PROCEDURE inform_supervisor
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('AGE VIOLATION!');
END;

Example 2: Maintain Derived Column Total_sal

Use triggers to maintain the Total_sal column in the DEPARTMENT table (sum of all salaries in the department).

After INSERT or Department Update

CREATE OR REPLACE TRIGGER update_total_sal_insert_or_update
AFTER INSERT OR UPDATE OF Dno ON employee
FOR EACH ROW
BEGIN
  -- Add salary to new department
  UPDATE department
  SET Total_sal = Total_sal + :NEW.salary
  WHERE Dnumber = :NEW.Dno;

  -- Subtract salary from old department if it's an update
  IF UPDATING THEN
    UPDATE department
    SET Total_sal = Total_sal - :OLD.salary
    WHERE Dnumber = :OLD.Dno AND :OLD.Dno IS NOT NULL;
  END IF;
END;

After DELETE

CREATE OR REPLACE TRIGGER update_total_sal_delete
AFTER DELETE ON employee
FOR EACH ROW
WHEN (:OLD.Dno IS NOT NULL)
BEGIN
  UPDATE department
  SET Total_sal = Total_sal - :OLD.salary
  WHERE Dnumber = :OLD.Dno;
END;

Example 3: Preventing Invalid Insert (Salary Check)

CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT ON employee
FOR EACH ROW
WHEN (:NEW.salary < 3000)
BEGIN
  RAISE_APPLICATION_ERROR(-20001, 'Salary below minimum threshold');
END;

Viewing and Managing Triggers

View all triggers in your schema:

SELECT trigger_name FROM user_triggers;

View trigger source code:

SELECT text FROM user_source 
WHERE name = 'TRIGGER_NAME' AND type = 'TRIGGER';

Drop a trigger:

DROP TRIGGER trigger_name;

Triggers vs Constraints

Feature	Triggers	Constraints
Purpose	Handle complex business rules	Enforce simple integrity rules
Execution	DML events (INSERT, UPDATE, DELETE)	Built-in integrity checks
Flexibility	Can contain logic and conditions	Simple, declarative rules
Code visibility	Stored separately from table	Defined within the table schema
Accepts logic?	Yes (PL/SQL allowed)	No (limited to conditions only)
Control	Precise control (row-level, timing)	Always checked automatically

Important Points

• Triggers are automatically invoked — no explicit call.

• Triggers cannot accept parameters.

• Too many triggers can impact performance.

• Triggers should be used when declarative constraints are not sufficient.

• Oracle doesn’t allow COMMIT, ROLLBACK, or SAVEPOINT inside triggers.

Summary

• Triggers automate reactions to data modifications

• Help enforce business logic and maintain data consistency

• Must be used cautiously to avoid side effects and performance issues