Prev Next

Triggers

A TRIGGER is a special type of stored procedure that automatically runs when a specified event occurs in the database. Triggers are often used to enforce business rules, audit changes, or keep data integrity intact.

Types of Triggers

  • AFTER Trigger: Executes after an INSERT, UPDATE, or DELETE operation.
  • INSTEAD OF Trigger: Executes instead of an INSERT, UPDATE, or DELETE operation.

Creating an AFTER Trigger

Use the CREATE TRIGGER statement to create a trigger that fires after a specified event.

Example: AFTER INSERT Trigger

CREATE TRIGGER trg_AfterInsert ON FreedomFighters
AFTER INSERT
AS
BEGIN
    PRINT 'A new freedom fighter record has been added.';
END;

Explanation: This trigger, trg_AfterInsert, fires after a new record is inserted into the FreedomFighters table and prints a message.

Creating an INSTEAD OF Trigger

Use the INSTEAD OF trigger to execute custom logic instead of the default action.

Example: INSTEAD OF DELETE Trigger

CREATE TRIGGER trg_InsteadOfDelete ON FreedomFighters
INSTEAD OF DELETE
AS
BEGIN
    PRINT 'Delete operation is not allowed on this table.';
END;

Explanation: This trigger, trg_InsteadOfDelete, prevents deletion of records from the FreedomFighters table and prints a message instead.

Dropping a Trigger

To remove a trigger, use the DROP TRIGGER statement:

DROP TRIGGER trg_AfterInsert;

Do's and Don'ts

Do's

  • Use triggers to maintain data integrity and enforce business rules.
  • Document triggers to make their purpose and functionality clear.
  • Test triggers thoroughly to ensure they perform as expected.

Don'ts

  • Don't use triggers for tasks that can be performed more efficiently with other methods.
  • Don't create too many triggers on the same table, as it may affect performance.
  • Don't use triggers to implement complex business logic that is hard to maintain.
Prev Next