How To: Unit Test a T-SQL Trigger with VSTS Database Edition
In Visual Studio Team System Database Edition, three types of functionality are unit tested; Stored Procedures, Functions, and Triggers. It’s not so obvious how to test a trigger and at this time there are not many examples. This is probably because there are so many different testing needs. Testing the trigger is very similar to the more obvious calls to stored procedures and functions. This example shows how a trigger can be unit tested using the RAISERROR method to cause a test harness failure. Two other methods exist to perform unit testing failures for those complicated tests.
1. The Assert classes that we can use in the code-behind test classes, or
2. Test conditions in the Database Unit Test Designer. These are Microsoft provided test conditions or custom written test conditions that check the test results and either pass or fail.
In the following example, we simply count the number of records before and after the table action that causes a trigger. We can now get the Red-Yellow-Green lights for TDD. We might want to split this into three different tests and add transaction support. Transaction support is added in either the T-SQL unit test as listed below or we can add it in the Visual Studio C# or VB.NET generated test code.
The T-SQL Unit Test
-- Database unit test for dbo.trg_CopyToHistoricalPrimaryRecords
--
DECLARE @beforeCount INT,
@afterCount INT,
@PrimaryField1 INT,
@PrimaryField2 UNIQUEIDENTIFIER,
@PrimaryField3 BIT,
@PrimaryTableId INT
SELECT @PrimaryField1 = 0,
@PrimaryField2 = NULL,
@PrimaryField3 = 0
-- INSERT Trigger Test
SET @beforeCount = (SELECT COUNT(*) FROM HistoricalPrimaryRecords);
INSERT INTO [dbo].[PrimaryTable] ( [PrimaryField1], [PrimaryField2], [PrimaryField3] )
VALUES( @PrimaryField1, @PrimaryField2, @PrimaryField3 );
SET @PrimaryTableId = SCOPE_IDENTITY();
SET @afterCount = (SELECT COUNT(*) FROM HistoricalPrimaryRecords);
IF NOT @afterCount = @beforeCount + 1
RAISERROR ('TRIGGER INSERT ERROR: trg_CopyToHistoricalPrimaryRecords did not insert a record in the historical table.', 1, 1);
--- UPDATE Trigger Test
SET @beforeCount = (SELECT COUNT(*) FROM HistoricalPrimaryRecords);
UPDATE [dbo].[PrimaryTable]
SET
[PrimaryField1] = @PrimaryTableId
WHERE
[PrimaryTableId] = @PrimaryTableId
SET @afterCount = (SELECT COUNT(*) FROM HistoricalPrimaryRecords);
IF NOT @afterCount = @beforeCount + 1
RAISERROR ('TRIGGER UPDATE ERROR: trg_CopyToHistoricalPrimaryRecords did not insert a record in the historical table.', 1, 1);
--- DELETE Trigger Test
SET @beforeCount = (SELECT COUNT(*) FROM HistoricalPrimaryRecords);
DELETE FROM [dbo].[PrimaryTable]
WHERE [PrimaryTableId] = @PrimaryTableId
SET @afterCount = (SELECT COUNT(*) FROM HistoricalPrimaryRecords);
IF NOT @afterCount = @beforeCount + 1
RAISERROR ('TRIGGER DELETE ERROR: trg_CopyToHistoricalPrimaryRecords did not insert a record in the historical table.', 1, 1);
-- PRINT '@beforeCount: ' + Cast(@beforeCount as varchar(10));
-- PRINT '@afterCount: ' + Cast(@afterCount as varchar(10));
The Trigger
ALTER TRIGGER [dbo].[trg_CopyToHistoricalPrimaryRecords]
ON [dbo].[PrimaryTable]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
-- UPDATE, DELETE: Capture all changes that occurred from an Insert and Delete.
INSERT INTO [PrimaryForeignKeys].[dbo].[HistoricalPrimaryRecords]
( PrimaryTableId
, PrimaryField1
, PrimaryField2
, PrimaryField3)
(SELECT d.PrimaryTableId
, d.PrimaryField1
, d.PrimaryField2
, d.PrimaryField3
FROM deleted d);
-- INSERT: Capture all newly inserted records that were not from Inserts and Deletes.
IF (0 = (SELECT COUNT(*) FROM deleted))
BEGIN
INSERT INTO [PrimaryForeignKeys].[dbo].[HistoricalPrimaryRecords]
( PrimaryTableId
, PrimaryField1
, PrimaryField2
, PrimaryField3)
(SELECT i.PrimaryTableId
, i.PrimaryField1
, i.PrimaryField2
, i.PrimaryField3
FROM inserted i);
END
END