How to write sql trigger

SQL trigger is a special object in the database management system that can automatically execute defined actions when specific events occur in the database. Triggers can be used to handle various scenarios, such as inserting, updating, or deleting data. In this article, we will introduce how to write SQL triggers and give specific code examples.

The basic syntax of a SQL trigger is as follows:

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
trigger_body

Among them, trigger_name is the name of the trigger, BEFORE or AFTERKeywords specify that the trigger is executed before or after the event, INSERT, UPDATE, DELETEKeywords specify the event type associated with the trigger, table_name is the table name associated with the trigger. FOR EACH ROW specifies that the trigger is executed for each row of data, trigger_body is the action that the trigger needs to perform.

Below we show how to write SQL triggers through several specific scenarios.

Scenario 1: Automatically set the creation time before inserting data.

Suppose we have a table called users which contains id, name and create_time Three columns, we want to automatically set create_time to the current time before inserting a new user.

Code example:

CREATE TRIGGER set_create_time
BEFORE INSERT
ON users
FOR EACH ROW
BEGIN
    SET NEW.create_time = NOW();
END;

Scenario 2: Automatically update the modification time after updating the data.

Now assume that we need to automatically update the update_time column to the latest modification time after updating user information.

Code example:

CREATE TRIGGER set_update_time
AFTER UPDATE
ON users
FOR EACH ROW
BEGIN
    SET NEW.update_time = NOW();
END;

Scenario 3: Automatically back up deleted data before deleting it.

In some cases, we may need to automatically back up the data to be deleted to another table before deleting the data.

Suppose we have a table named user_backup, which has the same structure as the users table. We want to back up the data to be deleted to user_backup before deleting the user. table.

Code sample:

CREATE TRIGGER backup_user
BEFORE DELETE
ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_backup (id, name, create_time)
    VALUES (OLD.id, OLD.name, OLD.create_time);
END;

The above are examples of several common SQL triggers. In actual applications, more complex triggers can be written according to needs. However, it should be noted that too many or complex triggers may have a certain impact on database performance, so careful evaluation and consideration are required when designing triggers.

The above is the detailed content of How to write sql trigger. For more information, please follow other related articles on the PHP Chinese website!