What is triggers in SQL?

Published On: 27 March 2023.By .
  • Digital Engineering

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:

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.

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:

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:

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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:

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:

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!

Related content

That’s all for this blog

Go to Top