In SQL, a trigger is a special type of stored procedure that is automatically executed in response to certain events, such as an INSERT, UPDATE, or DELETE operation on a table. Triggers are often used to enforce business rules or perform other automatic processing when data is changed in a database.
Create a trigger in sql
To create a trigger in SQL, you can use the CREATE TRIGGER statement, which has the following basic syntax:
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
WHEN (condition)
BEGIN
-- Trigger code goes here
END;
Here’s a breakdown of the various parts of this syntax:
- trigger_name: The name of the trigger you’re creating.
- {BEFORE | AFTER}: Indicates when the trigger should be executed (before or after the triggering event).
- {INSERT | UPDATE | DELETE}: Specifies the type of event that should trigger the execution of the trigger.
- table_name: The name of the table on which the trigger is being created.
- REFERENCING OLD AS old NEW AS new: Allows you to reference the old and new values of the rows being affected by the trigger.
- FOR EACH ROW: Specifies that the trigger should be executed once for each row affected by the triggering event.
- WHEN (condition): An optional condition that must be true for the trigger to execute.
- BEGIN…END: The body of the trigger, which contains the code that should be executed when the trigger is activated.
For example, let’s say you want to create a trigger that automatically inserts a new row into a log table whenever a row is inserted into a users table. Here’s how you could do it:
CREATE TRIGGER insert_user_log
AFTER INSERT
ON users
FOR EACH ROW
BEGIN
INSERT INTO log (user_id, action) VALUES (NEW.id, 'User created');
END;
In this example, the insert_user_log trigger is set to execute AFTER INSERT on the users table. When the trigger is activated, it inserts a new row into the log table with the ID of the newly inserted row from the users table and a message indicating that a user was created.
