Triggers in SQL server

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:

Syntax:

create trigger trg_test_insert on clients
for insert
as
begin

select * from inserted
end

Explanation:

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.

Effect:

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:

create trigger trg_test_delete on clients
for delete
as
begin
select * from deleted
end

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:

create trigger trg_test_update on clients
for update
as
begin
select * from deleted
select * from inserted
end

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:

create trigger trg_test_update on clients
instead of update
as
begin
raiserror('Cannot execute this action',12,1)
end

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:

create table log_table
(
user_action varchar(10),
user_name varchar(10),
first_name_before varchar(30),
first_name_after varchar(30),
time_stamp datetime
)

Creation of a trigger on insert:

Syntax:

create trigger trg_log_insert on clients
for insert
as
begin
insert into log_table
select
'insert',
current_user,
'',
first_name,
getdate()
from
inserted
end

Explanation:

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

Syntax:


create trigger trg_log_update on clients
after update
as
begin
insert into log_table
select
'update',
current_user,
d.first_name,
i.first_name,
getdate(),*
from
inserted i, deleted d
where i.id = d.id

end

Explanation:

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:

Syntax:

create trigger trg_log_delete on clients
for delete
as
begin
insert into log_table
select
'delete',
current_user,
first_name,
'',
getdate()
from
deleted
end

Explanation:

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:

create trigger trg_log_delete on clients
for delete
as
if
not exists(
select 1 from deleted d inner join salespersons as p
on p.id = d.salesperson_id
)
begin
insert into log_table
select
'delete',
current_user,
d.first_name,
'',
getdate()
from
deleted d
end
else
begin
raiserror('Salesperson exists for client',12,1)
end

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:

create trigger trg_test_insert on clients
for insert
as
begin
declare @first_name varchar(30),
@second_name varchar(30)
select @first_name=first_name,
@second_name=last_name
from inserted
if @first_name = 'Saddham' and
@second_name = 'Hussain'
begin
raiserror ('He is back',12,1)
end
end

Explanation:

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”):

if object_id ('trg_log_update', 'TR') is not null
drop trigger trg_log_update
 
Please help this site survive, share on:



Author:
Last udpated: September 10, 2017
Created: February 24, 2015

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

 
 

2 questions on “Triggers in SQL server

  1. Hi.

    Could you please explain me the below.
    Why does it say drop not create – mistake ?

    Creation of a trigger on update

    Syntax:

    drop trigger trg_log_update
    create trigger trg_log_update on clients…

    Thank you

    • Hi Sebastian,

      Happy to see that you are reading also the blog :)

      The example was showing a drop (deletion of the trigger) followed by a create. I took of the drop statement, thank you for catching that. What is done usually is to put a statement to delete the trigger if it exists, and then write the create statement:

      if object_id (‘trg_log_update’, ‘TR’) is not null
      drop trigger trg_log_update

      This is useful when you want to modify the trigger: it replaces the old version of the trigger by the new version.

      Thanks again
      Vincent