What are triggers in SQL?

A trigger is a stored procedure - which is a set of SQL statements saved under a name, just like a function, so that it can be reused - that is executed automatically when a certain event occurs in a database.

For instance, you may program a trigger to execute when data is inserted in a table.

Benefits

  • Calculating derived columns automatically
  • Ensuring referential integrity
  • Logging events
  • Auditing
  • Replicating tables synchronously instead of the default asynchronous replication
  • Authenticating users
  • Preventing invalid transactions
svg viewer

Types of Triggers

1. Event

  • DML Trigger: It fires when a DML (Database Manipulation Language) event is specified (INSERT/UPDATE/DELETE)
  • DDL Trigger: It fires when a DDL (Database Definition Language) event is specified (CREATE/ALTER)
  • DATABASE Trigger: It fires when a database event is specified (LOGON/LOGOFF/STARTUP/SHUTDOWN)

2. Timing

  • BEFORE Trigger: It fires before the specified event has occurred.
  • AFTER Trigger: It fires after a specified event has occurred.
  • INSTEAD OF Trigger: It lets you skip a statement and execute a different statement present in the trigger body instead.

3. Level

  • STATEMENT level Trigger: It fires one time for a specified event statement.
  • ROW level Trigger: It fires for each record that was affected in a specified event. (only for DML)
svg viewer

SQL Implementation

A trigger is implemented in the given code which will be fired before anything is inserted in the people table. This trigger makes sure that a person does not have a negative age and sets the age to zero.

CREATE TABLE people (
  age INT, 
  name varchar(150)
);
delimiter //
CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END 
IF; //
INSERT INTO people VALUES (-20, 'Sid'), (30, 'Josh');
select * from people;

Free Resources

Copyright ©2026 Educative, Inc. All rights reserved