A trigger waits that a user does an action on a database. When it happens, the trigger launches a group of actions.
Example: a trigger waits that a user updates a row in the table clients. When this happens, the trigger automatically inserts a row in another table.
How does an SQL trigger work?
It works in two steps:
- you create the trigger in the database, specifying:
1) which user action the trigger is waiting for.
2) on which table this action should happen.
3) which actions the trigger executes when it happens.
- every time a user executes the action defined in 1), the trigger executes the actions defined in 2).
The picture below illustrates these two steps: creation of the trigger, then launch of the trigger on a certain condition.
Simple examples of an SQL trigger:
Let us start with simple examples. They do not correspond to a common use of a trigger: the purpose of these examples is to understand how triggers are working.
Example with insert:
trg_test_insert is the name of the trigger.
on clients: means that the trigger waits for an action on the clients table.
for insert: means that the action the trigger is waiting for is an insert.
begin … end: The actions that the trigger will execute are written between the keywords begin and end. In this example, the action is “select * from inserted”.
select * from inserted : when a user executes an insert on the table clients, the trigger creates a temporary table:
- this temporary table contains all rows the user inserted.
- this temporary table’s name is “inserted”.
- you can get information from this temporary table the same way you would from any other table: using a select statement.
- you can also use select using a join, a group by, or any techniques you learnt about that you can combine with to select statement.
When executing an insert on the table clients, the DBMS automatically displays all the information contained in the inserted row.
The picture below illustrates that the trigger is inserting a row in the temporary table called inserted at the same time as the user is inserting a row in the clients table:
Example with delete:
In case of a trigger on delete, the trigger puts the information about the row to delete in a temporary table. This temporary table is called deleted.
Example with update:
select * from deleted shows the information in the rows to update before the udpate.
select * from inserted shows the information in the rows updated after the udpate.
This is because the update is treated as the combination of a deletion (deletion of the old value) and an insertion (insertion of the new value).
Are the temporary table names (“ inserted” and “deleted”) always the same?
Yes, these names are always the same on SQL server:
- inserted contains the information of the inserted row, or of the updated row after update.
- deleted contains the information of the deleted row, or of the updated row before the update.
The picture below illustrates that the trigger is inserting a row in the inserted and deleted temporary tables at the same time as the user is updating a row in the clients table:
Can a trigger prevent an action from happening?
Yes. A trigger can wait for a user to attempt to execute this action, and prevent this action from happening. To implement that, you replace the keyword “for” by “instead of”. When using “instead of”, the action that the user asked for is not executed. Instead, only the action launched by the trigger (indicated between BEGIN and END) is executed:
In this simplistic example, when a user attempts to update the clients table:
- the update is not executed.
- an error message is raised.
Practical use of a trigger: log table.
You will now see how to insert a row in a log table each time a user executes one of the following actions:
- insert into the table clients
- update of the table clients
- deletion in the table clients
The information recorded in the log table will be:
- the action executed by the user (insert, update or delete).
- the user who executed the action.
- the first_name of table clients before the action (empty in case of insert).
- the first_name of table clients after the action (empty in case of delete).
- the date and time at which the user executed the action.
Creation of the log table:
The following statement will create the table in which the trigger will insert information:
Creation of a trigger on insert:
trg_log_insert is the name of the trigger.
clients is the name of the table into which the user is inserting a row, which will trigger the insertion of a row in log_table.
log_table is the name of the table in which the database will insert a row automatically.
'insert' is basically the string “insert” (note that you can put any meaningful text in there). This will indicate that the action recorded in log_table corresponds to an insert.
current_user: this gives the name of the user executing the insert in the table clients.
first_name: value of the column first_name in the table in which the user inserts a row (clients).
inserted: temporary table created by the trigger. The trigger copies in this temporary table the row to insert in the clients table. This is why first_name from inserted gives the first name of the inserted client.
Creation of a trigger on update
trg_log_update is the name of the trigger.
'update' is basically the string “update” (note that you can put any meaningful text in there). This will indicate that the action corresponds to an update.
d.first_name: value of the column first_name in the temporary table deleted. This temporary table contains data from the rows to update before the update.
i.first_name: value of the column first_name in the temporary table inserted. This temporary table contains data from the rows to update after the update.
Note that the update is considered as the sum of a deletion (deletion of the row with the old value) and an insertion (insertion of the row with a new value).
...inserted i, deleted d where i.id = d.id: a join is done between the temporary table inserted and the temporary table deleted in order to show the information before and after the update on the same line.
Creation of a trigger on delete:
trg_log_delete is the name of the trigger.
‘delete’ is basically the string “delete” (note that you can put any meaningful text in there). This will indicate that the action corresponds to a delete.
first_name: value of the column first_name in the temporary table deleted. This temporary table contains data from the rows to delete.
'':The value of the column first_name after the delete is set to empty, as after the deletion of the row, this field will have no value.
Trigger with condition:
The following trigger is inserting a row in the log table under condition:
if not exists( select 1 from deleted d inner join salesperson as p on p.id = d.salesperson_id) : this means that the action is triggered only if the row to delete in clients has no matching entry in the table salesperson. The match between the two table is done by comparing salesperson_id in clients with id in salespersons.
raiserror: as explained in the article about stored procedure, it is raising an error message.
Use of variable, use of if statement:
Like in a stored procedure, you can use variables and “if” conditions in a trigger. Here is an example:
In order to put a value from the inserted row in a variable, you need to use a select statement, as “inserted” behaves like a table.
select @first_name=first_name, @second_name=last_name from inserted: this assigns the first name and second name of the inserted client to the variables @first_name and @last_name.
if @first_name = 'Saddham' and @second_name = 'Hussein' : this condition is using the values of these two parameters. If this condition is true, the database will execute what is between the keywords “begin” and “end”.
Dropping a trigger if it already exist:
As for stored procedure you need a command to delete the trigger if it already exists. The syntax is the following (example with a trigger called “trg_log_update”):