mysql
  1. mysql-create-trigger
PreviousNext

Create Trigger - (MySQL Triggers)

In MySQL, a trigger is a set of SQL statements that are automatically invoked in response to a specified event. These events can be INSERT, UPDATE, or DELETE operations on a certain table. In this tutorial, we'll discuss how to create triggers in MySQL.

Syntax

The syntax for creating a trigger in MySQL is as follows:

CREATE TRIGGER trigger_name
{BEFORE | AFTER}
{INSERT | UPDATE | DELETE} ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements to be executed
END;
  • trigger_name: The name of the trigger to be created.
  • BEFORE | AFTER: Indicates when the trigger should be executed - before or after the specified operation.
  • INSERT | UPDATE | DELETE: Specifies the operation that should trigger the execution of the code in the trigger.
  • table_name: The name of the table for which the trigger is being created.
  • FOR EACH ROW: Indicates that the trigger should execute for each row affected by the specified operation.
  • BEGIN and END: The body of the trigger, where the SQL statements to be executed are specified.

Example

Let's say we have a table called "employees" with the following columns: id, name, age, and salary. We want to create a trigger that automatically updates the salary of an employee whenever their age changes. Here's how we can implement it:

CREATE TRIGGER upd_salary AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.age <> OLD.age THEN
        UPDATE employees
        SET salary = salary * 1.1
        WHERE id = NEW.id;
    END IF;
END;

In the example above, we created a trigger called "upd_salary" that executes after an update operation is performed on the "employees" table. The trigger checks if the age of the employee has been updated and if so, updates their salary by multiplying it by 1.1. The WHERE clause is used to update only the salary of the employee whose age has been updated.

Output

When an update operation is performed on the "employees" table and the age of an employee is updated, the trigger will automatically execute, updating the salary of the employee. There is no physical output for this trigger, but you can view the updated data in the "employees" table.

Explanation

In the example above, we created a trigger that makes use of the AFTER UPDATE event on the "employees" table. We specified that the trigger should execute FOR EACH ROW affected by the update operation. In the body of the trigger, we checked if the age of the employee had been updated and if so, we updated their salary by multiplying it by 1.1.

Use

Triggers can be used to automate certain tasks in MySQL, such as updating data, enforcing constraints, and logging activity. They can be extremely useful in situations where you need to automatically execute SQL statements in response to certain events.

Important Points

  • Triggers are defined using the CREATE TRIGGER statement in MySQL.
  • Triggers can be executed BEFORE or AFTER a specified operation.
  • Triggers can be defined for INSERT, UPDATE, or DELETE operations.
  • The body of a trigger can contain any valid SQL statements.
  • Triggers can be used to enforce constraints or automate tasks in MySQL.

Summary

In this tutorial, we discussed how to create triggers in MySQL. We covered the syntax, example, output, explanation, use, and important points of MySQL triggers. With this knowledge, you can now create triggers in MySQL to automate tasks and perform automatic updates in response to certain events.

TriggerShow Trigger
Published on: