SQL Triggers
Triggers. Love them or hate them. But they certainly are usefull.
Where Stored Procedures are often used to schedule certain actions at specific times, Triggers are executed direct and on the fly.
So if things need to happen right away to keep your data consistent.
Or if users cannot wait for the next run of your Stored Procedure, Triggers might just be the answer.
What is a Trigger?
A Trigger is just of bunch of SQL Code.
But that Code is executed everytime someone inserts, updates or deletes a record.
So when you do one action (the insert, update or delete) another action (the Trigger) also happens.
That's part of the reason not everybody is happy with Triggers: You can't see them straight away.
So you are thinking you are doing just an update of a column, and then you see that something else has happened.
But what?
Another reason people are not happy with Triggers is performance.
You insert a new record which only takes a fraction of a second.
But then the Trigger is fired and that could slow things down.
And you might get a chain reaction.
Your updates activates a Trigger that does something in another Table that also has a Trigger, and so on.
Before you know it, you end up in a circle.
Or as SQL Server puts it, you are the victim of a deadlock.
But as with Cursors, sometimes a Trigger is required to solve your problem.
So again, feel free to use them but don't overdo it.
If there is an alternative solution, it might just be the better solution.
Creating a Table for Testing
So let's see how you can set up a Trigger. For that, I prepared a demo with a dummy Table you can add in a playground database.
AppointmentID uniqueidentifier not null default newid() primary key nonclustered,
AppointmentHID int identity,
Subject nvarchar(150),
StartDate datetime2,
EndDate datetime2,
Completed tinyint default 0,
CompletedDate datetime2,
Created datetime2,
Modified datetime2
)
GO
So when you have that Table, let's see what we can do with it. What I want to achieve in this example is that the Created and Modified Columns are updated from the Trigger. So when you do an Insert or Update Statement, you do not need to worry about that. You simple cannot forget it, because you don't need to do it!
Add the Trigger to the Table
Here's how you can add the Trigger.
on Appointments
after Insert, Update
as
begin
declare @AppointmentID uniqueidentifier
declare @Created datetime2
declare cAppointments cursor for
select i.AppointmentID, d.Created
from Inserted i
left join deleted d on i.AppointmentID = d.AppointmentID
open cAppointments
fetch next from cAppointments into @AppointmentID, @Created
while @@FETCH_STATUS = 0
begin
if @Created is null
begin
update Appointments
set Created = getdate(), Modified = getdate()
where AppointmentID = @AppointmentID
end
else
begin
update Appointments
set Modified = getdate()
where AppointmentID = @AppointmentID
end
fetch next from cAppointments into @AppointmentID, @Created
end
close cAppointments
deallocate cAppointments
end
GO
Let's break this one down a little.
The Basics of a Trigger
In the Create or Alter Statement you give the Trigger a name.
Usually I use the prefix "tr" followed by the name of the table and the actions on which it is fired.
Just to be able to search for them in Sysobjects.
Next you tell on which Table the Trigger should be added.
And finally you specify on which actions it should be performed.
The use of a Cursor
Most of the times you would need a Cursor inside of your Trigger.
So often that it is a good practice to use one when you don't expect it to be neccesarry.
So when is it needed?
The Trigger is fired on any Insert, Update or Delete statement.
So when you update row by row, you would not need the Cursor.
But if you do a batch update (for example, update all Appointments before a certain date) you will need it.
The Trigger is fired once for the update.
But it should be applied on all the updated rows.
That's were the Inserted and Deleted Tables come in.
Inserted and Deleted
Here you need to understand that SQL Server never does a real update.
When you Update a row, that row is deleted and a new row is inserted.
Hence you only have the Inserted and Deleted Tables, not an Updated Table.
The Inserted and Deleted Tables are created on the fly by SQL Server and they are exactly the same as the Table you are working on.
The Inserted Table contains the rows after the insert or update.
The Deleted Table contains the previous version of that row.
If you Insert a row, the Deleted Table will be empty.
Same if you Delete a row, the Inserted Table will be empty.
When you Update a row, for that moment you have access to the previous and the new version of that row.
In this example I used that to my advantage, because by left joining the Inserted and Deleted Tables I can check if it is an Insert or Update.
Because, when there is an Insert happening, there is no data from the Deleted Table.
Do the work
So with the data I can now check if it is an Update or Insert. If it is an Insert, I need to set both the Created and Modified with the current date/time. If it is an Update, I only need to set the Modified date.
Let's Insert some Data
Now when we insert 2 rows, whatch what happens:
values('Test Appointment', getdate(), dateadd(hour, 1, getdate()))
GO
insert into Appointments(Subject, StartDate, EndDate)
values('Test Appointment', dateadd(hour, 24, getdate()), dateadd(hour, 25, getdate()))
GO
This is what you will see:
When you insert 2 rows, you normally would see 2x (1 row affected).
But instead, we see it 4 times.
The first one you see is from the insert, the second one is from the Trigger.
Same for the 3rd and 4th line.
So when you see more lines affected than you expected, be sure there is a Trigger somewhere.
You will see the same behavior when you do an update, because the Trigger was for both Insert and Update:
set Subject = Subject + ' ' + cast(AppointmentHID as nvarchar(10))
GO
For an Update Statement like this one, that's where the Cursor comes in.
It is only one Update so the Trigger would only work for the first record.
That's not what we want so therefor the use of the Cursor.
Now if your application does not do any batch update, it is good practice to use a Cursor anyway.
You never know if someone later on does a batch update through Management Studio.
Another Example
Here's another example. In this case I want to set the CompletedDate using the current date/time.
But when there is an update that does not change the Completed boolean (for example just the Subject is updated) the action is not necessary.
Also, if Completed already had the value 1, it was already completed and the CompletedDate should not be updated.
So in this case I check if Completed has the value 0 in the Deleted Table, and the value 1 in the Inserted Table.
on Appointments
after Insert, Update
as
begin
declare @AppointmentID uniqueidentifier
declare @Created datetime2
declare @DateForCompletion datetime2
declare cAppointments cursor for
select i.AppointmentID, d.Created,
case
when d.Completed = 0 and i.Completed = 1 then getdate()
else null
end as DateForCompletion
from Inserted i
left join deleted d on i.AppointmentID = d.AppointmentID
open cAppointments
fetch next from cAppointments into @AppointmentID, @Created, @DateForCompletion
while @@FETCH_STATUS = 0
begin
if @Created is null
begin
update Appointments
set Created = getdate(), Modified = getdate(), CompletedDate = @DateForCompletion
where AppointmentID = @AppointmentID
end
else
begin
update Appointments
set Modified = getdate(), CompletedDate = @DateForCompletion
where AppointmentID = @AppointmentID
end
fetch next from cAppointments into @AppointmentID, @Created, @DateForCompletion
end
close cAppointments
deallocate cAppointments
end
GO
In this example I made a comparison between the old row and the new row: "when d.Completed = 0 and i.Completed = 1".
That's because I wanted to set the CompletedDate only once when Completed changes from 0 to 1.
Any Update after that should not change the CompletedDate.
This is good practice because you can prevent unnecessary actions that might affect performance.
To see if it works, just run this one:
set Completed = 1
where AppointmentHID = 1
GO
Disable Triggers
Once your Trigger is there, it will be fired always on the conditions set.
But you can stop them from firing if needed. And you don't need to drop them.
Here's how you can disable 1 Trigger by name, or all Triggers on a Table:
alter table Appointments disable trigger all
And once you are done, do not forget to turn them back on:
alter table Appointments enable trigger all
Happy with Triggers
That's it for today! Be happy with Triggers, but don't be Trigger Happy.
In other words, do not overdo it.
Consider there might be an alternative solution.
For example, if you add a row to one table and a row in another Table should be inserted, you could us a Multiple Insert.
If it is not needed for data consistency or if the user can wait a few moments?
Better use a Stored Procedure and schedule it through the SQL Agent.
If you have multiple different actions that need to be performed, try to keep it all in one Trigger.
And compare the Inserted and Deleted row to check which actions need to be performed.
You can use seperate Triggers for this but then it will become harder to maintain.
So think of the next guy who will have to maintain your database!
A Bonus as an afterthought
Sometimes (for example when running an update of the software that uses the database) it might be necessary to disable all Triggers. Here's a script that will do the trick. By changing the disable to enable you can use the same script to turn the Triggers back on.
declare cObjects cursor for
select [name]
from sysobjects
where [Type] = 'U'
and (instrig <> 0 or updtrig <> 0 or deltrig <> 0)
order by [Name]
open cObjects
fetch next from cObjects into @object
while @@FETCH_STATUS = 0
begin
declare @sql nvarchar(500)
set @sql = N'alter table ' + @object + ' disable trigger all'
print '------------------------------------------------------'
print @sql
EXECUTE sp_executesql @sql
print '------------------------------------------------------'
fetch next from cObjects into @object
end
close cObjects
deallocate cObjects
You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!