14 – Creating A Trigger

In this post, we will be explaining what triggers are and how to create them.

Introduction

Warning! Triggers are complicated! Well kind of. The concept of triggers themselves is not so bad but the way they work may leave your head spinning. Triggers are similar to events in MS Access in that they are bits of code that fire (hence the name triggers) when something happens to a table in SQL Server. So, what can happen to a table? Well, a table can have records inserted, updated or deleted and these are what causes the triggers to fire.

Why would we want to use Triggers?

Think of the IsCurrent field in tblGuests. We have a stored procedure that can update one or all the records in tblGuests and this stored procedure is fired every time the database is opened (once per day normally). But what happens if you get a walk-in client who needs a room for a couple of days. This client would check-in immediately but because we only call the stored procedure when the database opens, he or she will not show up as “active” until the following morning. We could run the procedure every hour and update every single guest as we do it but that is overkill. So, instead, we want the procedure to fire whenever we insert, update or delete a record into/from tblBookingDetails (as this is the table that really affects the IsCurrent field in tblGuests).

Triggers can be set to respond to table level events before or after the desired changes are committed to the system. We are interested in running the update_current stored procedure after a record in tblBookingDetails has been inserted, update or deleted.

How does SQL Server handler a Trigger Event

SQL Server has 2 system tables that are necessary to handle the Trigger events. One is called INSERTED and the other DELETED. When a record is inserted in a table, a copy of the record is added to the INSERTED table. We use this table in the trigger code to access the values of the record.

When a record is deleted, a copy of the record is added to the DELETED table. It may seem a little bit strange that SQL Server is duplicating inserts in the INSERT table but with the DELETED table, the advantages are obvious. Imagine we have just deleted a record from tblBookingDetails and we want to run the Update_Current stored procedure against the relevant GuestID_FK. Well, where do we get the GuestID_FK from? It isn’t in tblBookingDetails as that record has just been deleted! So, for this reason we use the DELETED table (‘SELECT GuestID_FK FROM DELETED’ would produce the necessary GuestID_FK).

Anything missing? Oh yeah, updates. There is no update table! When we perform an update, SQL Server cleverly adds a record in both the DELETED table AND the INSERTED table. Why? Well, when updating a record we kind of have two versions of that record; the original and the updated.

This may be a little confusing but fear not! We have a video and the explanation of the Trigger code to help you out.

Creating A Trigger

The Code

CREATE TRIGGER tr_after_insert_booking_detail
ON HotelBookingsDB.dbo.tblBookingDetails
AFTER INSERT, UPDATE, DELETE
AS
	DECLARE @varBookingID int
	DECLARE @varGuestID int
IF EXISTS (SELECT * FROM INSERTED)
	BEGIN
		SELECT @varBookingID = i.BookingID_FK from inserted i;
		SELECT @varGuestID = (SELECT GuestID_FK FROM tblBookings tb WHERE tb.BookingID = @varBookingID);
		EXEC dbo.UpdateCurrent @GuestID = @varGuestID
	END
ELSE
	BEGIN
		SELECT @varBookingID = d.BookingID_FK from deleted d;
		SELECT @varGuestID = (SELECT GuestID_FK FROM tblBookings tb WHERE tb.BookingID = @varBookingID);
		EXEC dbo.UpdateCurrent @GuestID = @varGuestID
	END

Explanation of The Code

CREATE TRIGGER tr_after_insert_booking_detail
This line creates the Trigger and gives it a name (tr_after_insert_booking_detail)

ON HotelBookingsDB.dbo.tblBookingDetails
You must select which table it is for.

AFTER INSERT, UPDATE, DELETE
Here we tell the Trigger that it should fire after an INSERT, UPDATE or DELETE.

AS
DECLARE @varBookingID int
DECLARE @varGuestID int
Here we are declaring two variables; varBookingID and varGuestID (we need to use the @ sign in SQL Server for variables).

IF EXISTS (SELECT * FROM INSERTED)
This is a very important line of code. As we are handling an insert, update and delete, we need to slightly vary the trigger depending on which one it is. So, if we are inserting/updating, we do one thing, if deleting, we do another. The table we are referencing here is called INSERTED. A record is added here if a record is inserted or updated in tblBookingDetails and so if a record exists in INSERTED we know that an insert or update has been fired.

BEGIN
SELECT @varBookingID = i.BookingID_FK from inserted i;
Here we are setting the value of varBookingID to INSERTED.BookingID_FK.

SELECT @varGuestID = (SELECT GuestID_FK FROM tblBookings tb WHERE tb.BookingID = @varBookingID);
Here we are setting the value of varGuestID to a select statement that returns the GuestID_FK from tblBookings.

EXEC dbo.UpdateCurrent @GuestID = @varGuestID
END
This is where we fire the stored procedure. We use varGuestID as the argument to pass to the stored procedure.

ELSE
Remember IF EXISTS (SELECT * FROM INSERTED)? Well, this is the else part, which means we are dealing with a deletion.

BEGIN
SELECT @varBookingID = d.BookingID_FK from deleted d;
Here we are setting the value of varBookingID to DELETED.BookingID_FK.

SELECT @varGuestID = (SELECT GuestID_FK FROM tblBookings tb WHERE tb.BookingID = @varBookingID);
Here we are setting the value of varGuestID to a select statement that returns the GuestID_FK from tblBookings.

EXEC dbo.UpdateCurrent @GuestID = @varGuestID
END
This is where we fire the stored procedure. We use varGuestID as the argument to pass to the stored procedure.

The code can be a little hard to grasp at first but will make sense if you review it a couple of times.

Modifying the Guest Summary Report

Triggers are a really useful tool in your SQL Server armory and something that will set your SQL Server applications apart from simple MS Access applications.