What Is a Trigger in SQL?

1. Introduction

SQL triggers are a powerful feature that we can use to perform operations when data changes in our database automatically. In this tutorial, we’ll explore how to define and modify triggers in PostgreSQL, MySQL, and SQL Server. The examples will be based on Baeldung’s University schema.

2. What Are SQL Triggers?

We can define an SQL trigger as a stored procedure in our database that will trigger upon a Data Manipulation Language (DML) event. A DML event is an event where a modification of data occurs. These include events regarding INSERT, UPDATE, and DELETE statements.

The database can fire a trigger at different timings, before, after, or instead of a DML event. For example, we can fire a trigger when we update data on a specific row or for a whole statement, like a bulk deletion.

We should note that it’s highly recommended to use triggers only when their use is justified. SQL triggers are difficult to track and maintain, as they add complexity to our flow, and we can only track their result and not their actual execution.

A good example of a justified use of triggers is to audit changes in our database without using the application layer and boilerplate code, and this is the example we’re going to use in our tutorial.

To begin, let’s create our audit table. It’ll hold audit records for any changes performed in the Student table:

CREATE TABLE IF NOT EXISTS Student_Audit_Log (
  id SERIAL PRIMARY KEY,
  student_id INT NOT NULL,
  action VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

And now, we’ll define the same table for MySQL:

CREATE TABLE IF NOT EXISTS Student_Audit_Log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  action VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

And finally, for SQL Server:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student_Audit_Log]') AND type in (N'U'))
BEGIN
  CREATE TABLE Student_Audit_Log (
    id INT IDENTITY(1,1) PRIMARY KEY,
    student_id INT NOT NULL,
    action VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT GETDATE()
);
END

Since we want to log the deletions as well, we’ve omitted the reference to the student_id column in the Student table to avoid integrity issues.

3. Defining SQL Triggers

Now that our schema is complete, we’re ready to define our triggers.

3.1. Defining SQL Triggers in PostgreSQL

In PostgreSQL, we first need to create a function:

CREATE OR REPLACE FUNCTION log_student_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO Student_Audit_Log(student_id, action)
        VALUES (NEW.id, 'INSERT');
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO Student_Audit_Log(student_id, action)
        VALUES (NEW.id, 'UPDATE');
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO Student_Audit_Log(student_id, action)
        VALUES (OLD.id, 'DELETE');
    RETURN OLD;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Our function has a return type of trigger. This allows us to use it later, when we actually define our trigger. Upon a DML event, PostgreSQL provides two special variables, OLD and NEW. These values represent the changes. The OLD variable holds the record as it was before the DML event, and the NEW one holds the record as it is after the DML event.

This way, we can perform operations in our function, using the necessary data. Moreover, we can use another special variable, TG_OP, that can tell us which operation was performed. Finally, the function needs to return a record matching exactly the table definition it was called upon, or null. We use the NEW and OLD values to do this.

Now, we’re ready to define our trigger as well:

CREATE TRIGGER student_audit_trigger
    AFTER INSERT OR UPDATE OR DELETE ON Student
    FOR EACH ROW EXECUTE FUNCTION log_student_changes();

We define that we want our trigger to be called after each insert, update, or delete statement on our Student table, and for each affected row, it calls our trigger function.

Now, every time we change something in our table, it’ll be logged.

3.2. Defining SQL Triggers in MySQL

In MySQL, we don’t have the option to use a single statement to define our trigger for all the events. Instead, we have to define a single trigger for each one:

DELIMITER //
CREATE TRIGGER after_student_insert
    AFTER INSERT ON Student
    FOR EACH ROW
BEGIN
    INSERT INTO Student_Audit_Log(student_id, action)
    VALUES (NEW.id, 'INSERT');
END//

CREATE TRIGGER after_student_update
    AFTER UPDATE ON Student
    FOR EACH ROW
BEGIN
    INSERT INTO Student_Audit_Log(student_id, action)
    VALUES (NEW.id, 'UPDATE');
END//

CREATE TRIGGER after_student_delete
    AFTER DELETE ON Student
    FOR EACH ROW
BEGIN
    INSERT INTO Student_Audit_Log(student_id, action)
    VALUES (OLD.id, 'DELETE');
END//
DELIMITER ;

We also don’t have to define a trigger function; instead, we define the operation inside each trigger. MySQL provides us with the special variables OLD and NEW as well so we can access the data before and after the DML event. We don’t need to return any value in this case.

3.3. Defining SQL Triggers in SQL Server

SQL Server has the option to define a trigger for all events. Again, we don’t have to define a trigger function, but we define the operations performed inside the trigger definition:

CREATE TRIGGER trg_student_audit
    ON Student
    AFTER INSERT, UPDATE, DELETE
    AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO Student_Audit_Log (student_id, action)
        SELECT id, 'INSERT'
        FROM inserted;
    END

    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        INSERT INTO Student_Audit_Log (student_id, action)
        SELECT id, 'UPDATE'
        FROM inserted;
    END

    IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    BEGIN
        INSERT INTO Student_Audit_Log (student_id, action)
        SELECT id, 'DELETE'
        FROM deleted;
    END
END
GO

As we can see, when we call a trigger, the database provides us with two special tables that we can use to check our old and new data, the inserted and deleted tables. If both hold a value, that means that we updated our row; that is, we deleted the old value and inserted the new one.

4. Statement-Level Triggers

Up until now, we’ve seen triggers that get executed after a DML event that affects a row. We can also define a trigger that can be executed for a batch event, like a batch delete. This allows us to call the trigger functionality only once per statement, instead of all rows.

We must mention, a couple of things, though.

First, MySQL doesn’t support statement-level triggers.

And second, SQL Server also treats all triggers as statement-level ones. Using the inserted and deleted tables, we can choose to treat them as row-level ones or ignore them to have statement-level functionality.

So, we only have different commands in PostgreSQL. As such, we’ll keep our focus there.

For simplicity, we’ll use the same logging table for our example, and we’ll log a bulk deletion event, without keeping a record of the deleted IDs. As before, we need to define a trigger function and then the trigger:

CREATE OR REPLACE FUNCTION log_bulk_deletion_student()
RETURNS TRIGGER AS $$ 
  BEGIN 
    INSERT INTO Student_Audit_Log (action)
    VALUES ('BULK_DELETION'); 
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Our function inserts a log statement in our audit table that we performed a bulk deletion. As before, the function returns a type of trigger, and since it needs to have a return statement, we return NULL, as we don’t have a specific row from the Student table to return.

Now we can define our trigger as well:

CREATE TRIGGER log_bulk_deletion_student_trigger
AFTER DELETE ON Student
FOR EACH STATEMENT EXECUTE FUNCTION
log_bulk_deletion_student();

The definition of the trigger is simple enough. After each delete statement that we perform on the Student table, our trigger function will be executed as well.

5. INSTEAD OF Triggers

We can use INSTEAD OF triggers to change the behavior of a DML event. The trigger will be called, as its name implies, instead of the actual statement. We can use it to protect a table and never allow deletions, even if a user calls one by mistake. We should mention again that MySQL doesn’t support INSTEAD OF triggers. For our example, we’ll assume that we cannot delete records from the Department table.

5.1. INSTEAD OF Triggers in PostgreSQL

The definition of an INSTEAD OF trigger in PostgreSQL is similar to the previous examples. PostgreSQL, however, allows us to define an INSTEAD OF trigger only for views. Let’s define a view that we treat as an actual table for department data and queries:

CREATE OR REPLACE VIEW Department_View AS
SELECT * FROM Department;

Now, we can define our trigger function and the trigger to prevent deletions:

CREATE OR REPLACE FUNCTION prevent_department_delete()
RETURNS TRIGGER AS $$
BEGIN
  RETURN NULL; 
END;
$$ LANGUAGE plpgsql; 

CREATE TRIGGER prevent_department_deletion_trigger
INSTEAD OF DELETE ON Department_View
FOR EACH ROW
EXECUTE FUNCTION prevent_department_delete();

5.2. INSTEAD OF Triggers in SQL Server

SQL Server supports INSTEAD OF triggers on both views and databases, so we can define one on the Department table directly:

CREATE TRIGGER prevent_department_deletion_trigger
ON Department
INSTEAD OF DELETE
AS	 
BEGIN	 
RETURN;	 
END;

6. Conclusion

In this article, we learned what SQL triggers are and how to define and use them for PostgreSQL, MySQL, and SQL Server. We also demonstrated the differences in definitions for each one, statement-level triggers, and INSTEAD OF triggers.