Overview

Data integrity in SQL server can be enforced by the use of constraints and triggers. This post will quickly compare both constraints and triggers and explain the main differences between them.

Constraints

Check constraints are used to validate that the data going into the table follows certain rules. Constraints are really simple to use but also have the downside of not being as complex as triggers. They can be placed in the column to ensure that data in the column follow certain rules. For example, if a column Weight is created and has a requirement that it needs to be a positive number, we can enforce that using a check constraint.

	ALTER TABLE dbo.Person ADD CONSTRAINT CHK_Weight_Postive (Weight > 0);

The above command will ensure that the weight column will always have a positive value.

One thing to note with constraints is that they can only check the data of the table they’ve been created on. They cannot query other tables or insert into other tables when an insert is done. That’s a job for triggers.

Triggers

Triggers are more complex actions or rules based on events and can involve complex operations with multiple tables. They can be created to run after an operation such as delete, insert , or update.

If we want to do more complex data validation on the table, we can use triggers to do that.

Schedules example

A scheduling system requires that the schedules can never overlap and a Schedule table is created with a few properties, such as FromUtc and ToUtc.

CREATE TABLE dbo.Schedules
(
ID …
FromUtc DateTime,
ToUtc DateTime,
…
)

If we want to validate on insert that schedules cannot overlap, a trigger needs to be used instead of a check constraint. Check constraints are unable to use data in the table itself as a way to validate the data.

With triggers, we can make use of the inserted and deleted pseudo tables. The inserted table will contain rows that have just been inserted. deleted will contain rows that have been deleted. For the case of an UPDATE statement, both deleted and inserted will be populated. An UPDATE is essentially treated as a DELETE followed by an INSERT.

Let’s say schedule with ID 1 has a FromUtc and ToUtc of (2024-05-06) and (2024-05-10), respectively. If we try to insert a new schedule with a FromUtc and ToUtc of (2024-05-01) and (2024-05-07), we would want it to fail.

-- Trigger using AFTER
CREATE TRIGGER TR_Schedule_Date_Validation
ON 
INSERT, UPDATE
AS 
IF EXISTS(
	SELECT 1 FROM inserted I
	WHERE EXISTS (
		SELECT * FROM dbo.Schedules where FromUtc < I.FromUtc AND I.FromUtc < ToUtc AND I.Id != Id;
		UNION
		SELECT * FROM dbo.Schedules WHERE FromUtc < I.ToUtc AND I.ToUtc < ToUtc AND I.Id != Id;
	)
	BEGIN
		THROW 51000, 'Overlapping schedules error', 1;
	END
)

With the above SQL, after an insert or update is actioned, a check is done to see if there are any overlapping schedules. If there are any overlapping schedules then an exception will be thrown. By throwing the exception in the trigger, the insert/update will then be rolled back.

[!Note] Be sure to validate against the Id as well since what’s in inserted are the values that have just been inserted and selecting rows in the tables can return the newly inserted row.

Using instead of

Whereas AFTER or ON are triggers that occur after an insert/update, INSTEAD OF can be used to replace an insert or update. Unlike tha other triggers, you can only have one instead of trigger, which makes sense. The other triggers will be actioned randomly or if provided in the order specified.

-- Trigger INSTEAD OF
CREATE TRIGGER TR_I_SCHEDULES on dbo.Schedules
INSTEAD OF INSERT
AS 
Insert into dbo.NewSchedules(FromUtc, ToUTc) 
SELECT I.FromUtc, I.ToUtc from inserted I

By using instead of, we can write to other tables and prevent the Schedules table from being inserted.