Skip to main content

Understanding SQL Triggers

Understanding SQL Triggers

SQL Triggers are a type of stored procedure that automatically perform an action when certain events occur in a database. They are primarily used to maintain the integrity of the data in the database. A trigger could be defined to check if the values being inserted into a database are correct, and if they are not, the insertion will be declined.

What is an SQL Trigger?​

An SQL Trigger is a set of SQL commands that are executed or 'fired' automatically whenever a specified database event occurs. These events can be related to a change in data in a table, like an INSERT, UPDATE, or DELETE operation.

Triggers are powerful tools in SQL. They allow the database to automatically react to events. This can enforce certain constraints or rules on the data.

Types of Triggers​

There are two types of Triggers:

  1. Row Level Trigger: This type of trigger is fired whenever a row is affected by a triggering SQL statement.

  2. Statement Level Trigger: These are fired when a triggering SQL statement affects the table.

When are Triggers Used?​

Triggers are used when data needs to be validated before being inserted or changed in a database. They can also be used to log changes or updates made to data in a table. Triggers are also commonly used to enforce business rules and to maintain the referential integrity of the data.

Syntax for Creating a Trigger​

The basic syntax for creating a trigger is:

CREATE TRIGGER trigger_name
BEFORE or AFTER event_name
ON table_name
FOR EACH ROW
BEGIN
   -- Triggered actions
END;

In the above syntax:

  • trigger_name is the name of the trigger.
  • BEFORE or AFTER determines when the trigger will be executed in relation to the triggering event (INSERT, UPDATE, DELETE).
  • table_name is the name of the table the trigger is associated with.
  • FOR EACH ROW means the trigger will be executed for each row affected by the triggering event.
  • The actions inside the BEGIN...END; block will be executed when the trigger is fired.

Example of a Trigger​

Here is an example of a trigger that checks if a price of a product is reasonable before inserting the product into the products table:

CREATE TRIGGER CheckPrice
BEFORE INSERT
ON products
FOR EACH ROW
BEGIN
   IF NEW.price <= 0 THEN
      SIGNAL SQLSTATE '45000' 
      SET MESSAGE_TEXT = 'Price must be greater than 0';
   END IF;
END;

In this trigger, NEW.price refers to the value of the price column of the new row being inserted. If the price is less than or equal to 0, it signals an exception with a message.

Conclusion​

Triggers are a powerful feature of SQL that allow you to maintain consistency and integrity in your data. They can automatically respond to events in the database, which allows you to automate a lot of your data handling. However, they should be used with caution, as they can make the system more complex and harder to debug.