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.

create table Appointments (
  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.

create or alter trigger trAppointmentsInsertUpdate
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:

insert into Appointments(Subject, StartDate, EndDate)
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:

update Appointments
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.

create or alter trigger trAppointmentsInsertUpdate
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:

update Appointments
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 trAppointmentsInsertUpdate

alter table Appointments disable trigger all

And once you are done, do not forget to turn them back on:

alter table Appointments enable trigger trAppointmentsInsertUpdate

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 @object nvarchar(150)

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!