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 AFTER
Keywords specify that the trigger is executed before or after the event, INSERT
, UPDATE
, DELETE
Keywords 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!