Skip to main content

Creating Triggers

SQL Triggers are a powerful feature that allows the database to automatically perform certain actions in response to specific events. This feature can be incredibly useful in maintaining the integrity of the data in the database, automating certain tasks, and more.

What is a Trigger in SQL?​

A Trigger is a set of SQL statements that are executed, or "triggered," automatically when a specified event occurs. These events could be changes in data such as INSERT, UPDATE or DELETE operations on a particular table.

Types of Triggers​

There are two main types of Triggers:

  1. Row-level Trigger: This type of trigger is executed once for each row that is affected by the INSERT, UPDATE, or DELETE operation.

  2. Statement-level Trigger: This type of trigger is executed once for each transaction, regardless of how many rows are affected.

Creating a Trigger​

Creating a trigger involves defining the trigger, specifying the event that will cause the trigger to be executed, and writing the SQL code that should be run when the trigger is executed.

Here is the basic syntax for creating a trigger:

CREATE TRIGGER trigger_name 
trigger_time trigger_event 
ON table_name 
FOR EACH ROW 
trigger_body;
  • trigger_name: This is the name of the trigger.
  • trigger_time: This is when the trigger will be executed. The options are BEFORE or AFTER.
  • trigger_event: This is the event that will cause the trigger to be executed. The options are INSERT, UPDATE, or DELETE.
  • table_name: This is the name of the table that the trigger is associated with.
  • trigger_body: This is the SQL code that will be executed when the trigger is triggered.

Let's look at an example:

CREATE TRIGGER before_employee_update 
BEFORE UPDATE 
ON employees 
FOR EACH ROW 
BEGIN 
   -- Triggered SQL statements go here
END;

In this example, the trigger before_employee_update will be executed before any UPDATE operation on the employees table. The SQL statements inside the BEGIN and END block will be executed for each row that is affected by the update.

Trigger Example​

Let's say we have a table orders and we want to create a trigger that automatically updates the total_price column whenever a new row is inserted.

Here's how we could do it:

CREATE TRIGGER before_insert_orders 
BEFORE INSERT 
ON orders 
FOR EACH ROW 
SET NEW.total_price = NEW.quantity * NEW.price_per_item;

In this example, the trigger before_insert_orders will be executed before any INSERT operation on the orders table. The total_price column of the new row will be automatically set to the product of the quantity and price_per_item columns.

Conclusion​

Triggers can be a powerful tool when working with SQL, allowing you to automate tasks and ensure data integrity. However, they should be used sparingly as they can make debugging more difficult and can slow down data operations. But, with careful use, they can simplify your SQL workflows and make your database more efficient.