What Are Database Triggers? Types, Examples, Syntax To Use

Database triggers deftly revolutionize the way modern businesses interact with their data. So, what is it that they do that makes them so important to databases? Being a fundamental concept of database management systems (DBMS), these triggers maintain first-rate data integrity and automate complex workflows, all while minimizing manual intervention.

With the global DBMS market projected to reach USD 57,180 million by 2028, the importance of mastering database triggers is more pronounced than ever. However, to make the most out of them, you should master their syntax and get a solid understanding of their implementation techniques.

If you are looking to get your hand into database triggers, this article is just what you need. Gain valuable insights into the world of database triggers as industry experts guide you through the types of database triggers and more advanced concepts — such as the syntax for various DBMS.

What Are Database Triggers?

Image Source

Database triggers are types of stored procedures that run in response to an event occurring in a database. They are typically associated with changes made to a table's data, like insertions, updates, or deletions.

Triggers are useful for maintaining the data integrity in the database. They can adjust the entire database systematically whenever there is a change. Here are some common use cases for triggers:

• Triggers are used to maintain an audit file to record changes made in a transaction history table. This makes it easy to track changes and add this information to user reports.
  
• They are a helpful tool for calculating values within columns. For instance, triggers can maintain a TotalSales column on a customer record which would need to be updated every time a sale is made.
  
• Triggers help implement business rules. They inspect all data before running a data manipulation statement, apply any business rules, and ultimately complete the transaction. This helps maintain customer status based on purchase history.

While they are useful, triggers can be tricky to work with. Their automatic execution can complicate troubleshooting when an error occurs. They also increase the database overhead as they get executed every time any field is updated.

3 Types Of Database Triggers: Everything You Need To Know

Database triggers are a powerful tool that automates responses to certain events within a database. Let’s now discuss the different types of database triggers and see how they function, the events that can activate them, and the unique characteristics of each type.

MySQL Triggers

Image Source

In MySQL, you can deploy database triggers that run when you INSERT, UPDATE, or DELETE data from a table. These triggers are examples of row triggers – activated for each row affected.

This means that if a SQL statement updates 500 rows in a table, the trigger will have to run 500 times. MySQL does not support statement-level triggers which are executed once for each transaction regardless of how many rows are affected.

MySQL Trigger Syntax

In MySQL, a trigger is defined using the CREATE TRIGGER statement. The general syntax is as follows:

plaintext
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
    -- Trigger body goes here...
END;

Let’s breakdown the trigger:

• The CREATE TRIGGER is used to create a new trigger. 
  
• The trigger_name is the name of the trigger. 
  
• The trigger_time can be BEFORE or AFTER which specifies when the trigger should be fired.
  
• The trigger_event can be an INSERT, UPDATE, or DELETE operation, which specifies the type of operation that will fire the trigger.
  
• The ON table_name clause specifies the table on which the trigger is to be created. 
  
• The FOR EACH ROW trigger specifies that it should be fired for each row affected by the triggering event.
  
• The trigger_body is the statement to be executed when the trigger is fired. This can be a simple statement or a compound statement enclosed by BEGIN and END if the trigger should execute multiple statements.

SQL Server Triggers

Image Source

In SQL Server, triggers are designed to respond to changes in the data or the structure of a database. You can even write triggers that activate when someone logs into the database. In total, SQL Server supports 3 types of triggers:

Data Manipulation Language (DML) Triggers

DML triggers are similar to MySQL triggers and execute in response to data changes in a table. DML triggers can be further divided into 2 sub-types:

• INSTEAD OF Triggers: These triggers run before SQL Server starts the action that triggers them. So even if the check fails, this type still runs.
  
• AFTER Triggers: These triggers run after the SQL Server finishes the action that triggers them. For instance, an AFTER INSERT trigger on a table doesn't activate until the row clears all checks, like the primary key check.

Data Definition Language (DDL) Triggers

DDL triggers fire when changes in the structure of a database occur, like creating a new table or changing an existing one.

Logon Triggers

These triggers fire in response to LOGON events. When a user connects to a SQL Server, the logon trigger activates before the user's session is started.

Microsoft SQL Server Trigger Syntax

Let's now discuss the syntax for each type of SQL Server trigger.

DML Triggers Syntax

The syntax for creating a DML trigger in SQL Server is as follows:

plaintext
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

The breakdown of this trigger is:

• The CREATE trigger statement creates the trigger and the ALTER trigger statement modifies an existing trigger. 
  
• The schema_name and trigger_name specify the name of the trigger and the ON clause specifies the table or view on which the trigger is created. 
  
• The FOR | AFTER | INSTEAD OF clause specifies when the trigger should be activated.
  
• The INSERT, UPDATE, and DELETE statements specify the type of operation that will activate the trigger.
  
• The AS keyword is followed by the SQL statements that define the actions of the trigger.

DDL Triggers Syntax

The syntax for creating DDL triggers in SQL Server is similar to that of a DML trigger but the ON clause specifies whether the trigger is created at the server level (ALL SERVER) or the database level (DATABASE).

plaintext
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

Logon Triggers Syntax

The syntax for creating a Logon trigger in SQL Server is also similar to that of a DML or DDL trigger but has fewer trigger options.

plaintext
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

In all these cases, sql_statement represents the series of SQL commands that will be executed when the trigger fires.

PostgreSQL Triggers

PostgreSQL provides a more flexible syntax for creating triggers. These triggers are connected to tables and will automatically perform predefined actions when a specific operation (SQL INSERT, UPDATE, DELETE, or TRUNCATE statement) is performed on the table. 

There are 2 main types of triggers in PostgreSQL: 

• Statement-level Triggers: These triggers run once per SQL statement, no matter how many rows it affects. They can be set to run before or after the event.
  
• Row-level Triggers: These are the triggers that activate once for each row impacted by the event that triggers them. They can be set to activate before, after, or instead of the event.

PostgreSQL Trigger Syntax

Here's the general syntax for creating a trigger in PostgreSQL:

plaintext
CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name 
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

Here’s a breakdown of the syntax:

• The CREATE TRIGGER statement creates a new trigger. The REPLACE clause modifies an existing trigger. The CONSTRAINT keyword is optional and is used to create a constraint trigger.
  
• The BEFORE | AFTER | INSTEAD OF clause specifies when the trigger should be fired. BEFORE and AFTER indicate that the trigger should be fired before or after the triggering event. INSTEAD OF shows that the trigger should be fired instead of the triggering event.
  
• The event can be INSERT, UPDATE, DELETE, or TRUNCATE. Multiple triggers can be specified using the OR keyword.
  
• The ON table_name clause specifies the table on which the trigger is to be created. The FROM referenced_table_name clause is optional and is used when creating a foreign-key constraint trigger.
  
• The NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] clause specifies the timing of the constraint check. This is only applicable to constraint triggers.
  
• The REFERENCING clause is used to define transition tables that capture the state of the data before and after the triggering event.
  
• The FOR [ EACH ] { ROW | STATEMENT } clause specifies whether the trigger should be fired for each row affected by the triggering event or once per SQL statement.
  
• The WHEN ( condition ) clause is optional and specifies the condition that must be met for the trigger to fire.
  
• The EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments ) clause specifies the function that will be executed when the trigger fires. The function must be defined to take no arguments and return type triggers.

Knowing the theory is helpful but seeing these triggers in action gives a practical perspective and helps in understanding how they interact with the data and operations on the database. Let's consider some examples and watch those triggers work their magic.

Making Triggers Work: 3 Practical Database Examples To Learn From

Here are some examples of triggers in 3 popular SQL database systems: MySQL, SQL Server, and PostgreSQL. 

Example 1: MySQL Trigger

In our first example, we will demonstrate a common use case of triggers in MySQL: to keep a running total of a particular field. We will create an account table and a trigger that adds the amount of each new account to a system variable, @sum.

Let's say you have an account table like this:

We want to update a system variable @sum with the new cumulative sum of the column amount every time a new record is added to the table. This can be done using the following trigger:

plaintext
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;

Assume the @sum variable is currently 300. If we insert a new record into the account table with acct_num as 3 and amount as 150.00:

plaintext
INSERT INTO account (acct_num, amount) VALUES (3, 150.00);

The trigger ins_sum will activate before the insert operation and add 150.00 to @sum. So, after the insert operation, the @sum variable will be 450.00.

Example 2: SQL Server Trigger

In SQL Server, we will create an example where a trigger is used to maintain an audit table. The trigger will copy every new record inserted in the Employee table to the Employee_Audit table.

Here's the SQL code to create the Employee_Audit table:

plaintext
CREATE TABLE Employee_Audit (EmployeeId INT, Name NVARCHAR(100), Salary DECIMAL(10,2));

The trigger trgAfterInsert is created to copy each new record from the Employee table to the Employee_Audit table:

plaintext
CREATE TRIGGER trgAfterInsert
ON Employee
AFTER INSERT
AS
BEGIN
  INSERT INTO Employee_Audit
  SELECT * FROM inserted
END

If we insert a new record into the Employee table with Employee ID as 3, Name as Doe, and Salary as 5500:

plaintext
INSERT INTO Employee (EmployeeId, Name, Salary) VALUES (3, 'Doe', 5500);

The trigger trgAfterInsert will activate after the insert operation and insert the new record into the Employee_Audit table. So, after the insert operation, the Employee_Audit table will have the following data:

Example 3: PostgreSQL Trigger

For the third example, let’s consider the following sample table:

We want to write a database trigger that automatically updates the last_updated column if the price of a product is changed.

For this, we first create a function named update_last_modified that sets the last_modified field to the current timestamp:

plaintext
CREATE OR REPLACE FUNCTION update_last_modified()
    RETURNS TRIGGER AS
$$
BEGIN
    NEW.last_modified = NOW();
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

Then, we create a trigger named update_products_timestamp that calls the update_last_modified function before each update operation on the products table:

plaintext
CREATE TRIGGER update_products_timestamp
    BEFORE UPDATE
    ON products
    FOR EACH ROW
    EXECUTE PROCEDURE update_last_modified();

Now, if we update the price of the product with product_id as 2:

plaintext
UPDATE products
SET price = 26.99
WHERE product_id = 2;

The update_products_timestamp trigger will activate before the update operation. This trigger will then call the update_last_modified function to update the last_modified field to the current timestamp.

After the update operation, the products table would look like this:

The exact value of the last_modified field for the record with product_id as 2 would depend on the exact time the update operation was performed.

Also read: PostgreSQL Trigger Fundamentals: Examples & Syntax

Database Triggers For Change Data Capture

Trigger-based change data capture (CDC) is a method of tracking changes in a database that involves defining database triggers and creating a log of changes in shadow tables. These triggers fire in response to INSERT, UPDATE, or DELETE commands to indicate a change in the data. Some databases have native support for triggers.

In this method, 3 triggers are created for each table participating in data replication: an INSERT trigger for new records, an UPDATE trigger for changed records, and a DELETE trigger for deleted records. 

The information about the changed record goes to a shadow table, often referred to as the "Events History" table. Changes are then propagated to the target database based on the events in this History table.

While the approach is reliable and detailed, providing an immutable, verbose log of all transactions, it does have some major drawbacks:

• Added overhead: The requirement of triggers for each table can burden database performance.
  
• Frequent writes: With each data insertion, update, or deletion, the database has to write multiple times which slows operations.
  
• Vendor lock-in: The use of trigger functions requires vendor-specific code which complicates future migration to other database systems.

An alternative CDC approach is log-based CDC. Let’s see what it is and how you can implement it easily.

Log-Based CDC Using Estuary Flow

Log-based CDC approach reads transaction logs or redo logs to track changes in a database. These logs record all database events like inserts, updates, and deletions. It's a low-impact, efficient method that doesn't require modifications to the source application or database schema.

Log-based CDC offers high reliability and real-time change detection with minimal impact on the production database. However, using log-based CDC can be tricky and requires an advanced solution capable of handling its complexities.

This is where Estuary Flow comes into the picture. Flow is our real-time Data Operations platform that can set up pipelines with both historical and real-time data in minutes. It uses low-impact log-based CDC to minimize load on your database.

Here are some key features of Estuary Flow:

• Built-in testing: It offers unit tests to uphold data accuracy as your pipelines continue to evolve.
  
• Exactly-once semantics: Flow delivers exactly-once semantics for precise, accurate world views.
  
• Schema inference: It can convert your unstructured data into a structured format automatically.
  
• Resilience to failure: Flow is designed with cross-region and data center compatibility. So, even if a cloud provider is down, your operations won’t be impacted.
  
• Scaling: As a distributed system, Flow expands alongside your data. It has managed data volumes up to 7 GB/s and can quickly backfill terabytes of data from your source systems within minutes.

Conclusion

Database triggers are a powerful tool in the database world. They let you automate tasks, keep your data accurate, and quickly respond to specific events. The best part is that you can use triggers in various popular databases like MySQL, SQL Server, and PostgreSQL.

While triggers have their uses, they can be a bit tricky when dealing with change data capture. That's where log-based CDC comes in as a handy alternative, especially when you implement them with an advanced solution like Estuary Flow. Flow minimizes the operational burden and offers real-time change detection with minimal impact on the production database.

If you're looking for a platform that can handle data integration and migration using log-based CDC efficiently, Estuary Flow is an obvious choice. It offers a range of features designed to simplify your data operations with exceptional reliability. You can start exploring Estuary Flow for free by signing up here or reaching out to our team to discuss your needs.