What is triggers in SQL?

Published On: 27 March 2023.By Nimit Patel.

Digital Engineering

What is triggers in SQL?

Let’s understand what is “trigger” and where we can use it?

In MySQL, triggers can be used as a replacement for any task that we need to perform after a certain task performed. This will be executed automatically in response to specific events, such as INSERT, DELETE and UPDATE operations on a table. This can be used to perform different actions like below:

Logging changes to the table
Validating data
Updating related tables etc.

Triggers are defined using SQL statements and are associated with a specific table. When the specified event occurs, the trigger code is executed automatically, allowing you to perform additional actions on the affected rows.

How To Use MySQL Triggers {With Examples} | phoenixNAP KB

Image Source: https://phoenixnap.com

Syntax of trigger:

CREATE TRIGGER {trigger_name}
    {trigger_clause}
    ON {table_name} 
FOR EACH ROW
{trigger_body};

CREATE TRIGGER {trigger_name}

{trigger_clause}

ON {table_name}

FOR EACH ROW

{trigger_body};

First, specify the name of the trigger that you want to create in the CREATE TRIGGER clause – Replace {trigger_name} with your desired trigger name.

Second, use desired clause to specify the time to invoke the trigger – Replace {trigger_clause} with any of the desired time from below:

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

Third, specify the name of the table that the trigger is associated with after the ON keyword – Replace {table_name} with your table name.

Finally, specify the trigger body which contains one or more SQL statements that execute when the trigger is invoked – Replace {trigger_body} with your desired SQL queries/actions.

If you have multiple statements in the {trigger_body}, you have to use the BEGIN END block and change the default delimiter.

DELIMITER $$

CREATE TRIGGER {trigger_name}
    {trigger_clause}
    ON {table_name} 
FOR EACH ROW
BEGIN
    -- statement 1
    -- statement 2
    -- statement 3
    ...

END$$    

DELIMITER ;

DELIMITER $$

CREATE TRIGGER {trigger_name}

{trigger_clause}

ON {table_name}

FOR EACH ROW

BEGIN

-- statement 1

-- statement 2

-- statement 3

...

END$$

DELIMITER ;

For example, you can create a trigger that updates a log table whenever a row is inserted, updated, or deleted from a specific table. The trigger code can also include conditions and control flow statements to customize its behavior.

Note:

In a BEFORE INSERT trigger, you can access and change the NEW values. However, you cannot access the OLD values because OLD values obviously do not exist.
In an AFTER INSERT trigger, you can access the NEW values but you cannot change them. Also, you cannot access the OLD values because there is no OLD on INSERT triggers.
In a BEFORE UPDATE trigger, you can update the NEW values but cannot update the OLD values.
In a AFTER UPDATE trigger, you can access OLD and NEW rows but cannot update them.
In a BEFORE DELETE trigger, you can access the OLD row but cannot update it. Also, there is no NEW row in the BEFORE DELETE trigger.
In an AFTER DELETE trigger, you can access the OLD row but cannot change it and there is no NEW row in the AFTER DELETE trigger.

Example 1:

CREATE TRIGGER `update_product_rating_after_update_in_review_table` 
AFTER UPDATE ON `reviews` 
FOR EACH ROW BEGIN
     SET @currentRating = (SELECT AVG(rating) FROM reviews r WHERE r.product_id = OLD.product_id);
     UPDATE products p SET product_rating = @currentRating WHERE p.product_id = OLD.product_id;
END

CREATE TRIGGER `update_product_rating_after_update_in_review_table`

AFTER UPDATE ON `reviews`

FOR EACH ROW BEGIN

SET @currentRating = (SELECT AVG(rating) FROM reviews r WHERE r.product_id = OLD.product_id);

UPDATE products p SET product_rating = @currentRating WHERE p.product_id = OLD.product_id;

END

In above example, we’re creating a trigger “update_product_rating_after_update_in_review_table” which will update the product_rating column available in “product” table whenever there is any update on “review” table.

Example 2:

CREATE TRIGGER log_changes
AFTER INSERT, UPDATE, DELETE
ON my_table
FOR EACH ROW
BEGIN
  IF (NEW.column1 <> OLD.column1 OR NEW.column2 <> OLD.column2) THEN
    INSERT INTO my_log_table (table_name, action_type, row_id) 
    VALUES ('my_table', 'update', NEW.id);
  END IF;
END;

CREATE TRIGGER log_changes

AFTER INSERT, UPDATE, DELETE

ON my_table

FOR EACH ROW

BEGIN

IF (NEW.column1 <> OLD.column1 OR NEW.column2 <> OLD.column2) THEN

INSERT INTO my_log_table (table_name, action_type, row_id)

VALUES ('my_table', 'update', NEW.id);

END IF;

END;

In triggers, we have 2 keywords NEW & OLD. NEW keyword denotes the new value after update/delete/insert and OLD denotes values before update.

Benefits of triggers in MySQL:

Automatic Execution: Triggers execute automatically when a specific event occurs, such as an insert, update, or delete operation on a table. This means that you don’t have to manually perform certain actions in response to these events.
Data Validation: Triggers can be used to validate data before it is inserted or updated in a table. For example, you can create a trigger that checks if the entered value is within a certain range, or if it meets certain conditions.
Logging and Auditing: Triggers can be used to log and audit changes to a table. This allows you to keep track of all changes made to a table and track who made them.
Data Consistency: Triggers can be used to enforce data consistency rules. For example, you can create a trigger that automatically updates a related table when a row is inserted or updated in another table.
Business Logic Implementation: Triggers can be used to implement complex business logic that cannot be easily handled by a single SQL statement. For example, you can create a trigger that checks the status of an order and sends an email to the customer if the status changes.

SHOW TRIGGER:

SHOW TRIGGERS 
FROM database_name
WHERE search_condition;

SHOW TRIGGERS

FROM database_name

WHERE search_condition;

Here, we can search trigger with conditions also like trigger where table_name = ‘xyz’ etc. We can include all this clauses in WHERE condition.

DROP TRIGGER:

DROP TRIGGER [IF EXISTS] [schema_name].trigger_name;

1	DROP TRIGGER [IF EXISTS] [schema_name].trigger_name;

In Drop trigger, if we want to delete trigger which is not in current schema then we have to give schema_name explicitly. It is recommended to use schema_name always.

Click here to download a sample project.

In above sample project we have table “order_audit”, where we’re maintaining audits on “orders” table. Whenever we have any update/insert/delete on “orders” table, a trigger will automatically be executed which will audit/log into “order_audit” table.

HAPPY EXPLORING!