What is a Trigger in SQL: Beginner Friendly Guide with Examples

by

If you are learning SQL, you might come across the term trigger. A trigger in SQL is a special type of stored procedure that automatically executes or fires when certain events occur in a database table, such as INSERT, UPDATE, or DELETE operations.

Triggers are very useful for maintaining data integrity, automatically updating related tables, or auditing changes in your database without manually writing extra code.

How Does an Trigger in SQL Work?

Think of a trigger as a watchdog for your table. For example:

  • When you insert a new row into a table, a trigger can automatically update another table.
  • When you delete a row, a trigger can prevent unwanted deletion or log the action.

Triggers work automatically and help make your database smart and consistent.

Example of a Simple SQL Trigger

Here is a simple example for beginners:

CREATE TRIGGER update_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
   INSERT INTO salary_audit(employee_id, old_salary, new_salary, updated_on)
   VALUES(OLD.id, OLD.salary, NEW.salary, NOW());
END;

Explanation:

  • This trigger runs after an UPDATE on the employees table.
  • It automatically inserts a record in the salary_audit table to keep track of old and new salaries.
  • OLD refers to the old value before update, and NEW refers to the new value after update.

Key Points About SQL Triggers

  1. Automatic Execution: No need to call it manually.
  2. Event-Driven: Works on INSERT, UPDATE, or DELETE operations.
  3. Helps Maintain Data Integrity: Automatically handles changes in related tables.
  4. Can Log Changes: Useful for auditing database changes.

Conclusion

In short, a trigger in SQL is a powerful tool for automating tasks in your database. Beginners should understand triggers as a way to make their database intelligent and reliable without writing repetitive code.