tSQLt Beyond Stored Procedures: Testing Triggers, Views, and Functions

Shannon Lowder

Shannon Lowder

— 2 min read

Most tSQLt tutorials start with stored procedures, and for good reason — that's where most of the testable business logic lives. But triggers, views, and scalar functions all contain logic worth testing, and tSQLt handles all of them with the same core primitives.

Testing Triggers

Triggers are the hardest SQL objects to test by hand — they fire implicitly, run in the same transaction as the triggering statement, and have side effects that are difficult to observe in isolation. tSQLt makes them tractable.

Say you have an audit trigger on the Orders table:

CREATE TRIGGER dbo.Orders_AfterUpdate
ON dbo.Orders AFTER UPDATE
AS
BEGIN
    INSERT INTO dbo.OrderAuditLog (OrderID, FieldChanged, OldValue, NewValue, ChangedAt)
    SELECT i.OrderID, 'Status', d.Status, i.Status, GETDATE()
    FROM inserted i
    JOIN deleted d ON i.OrderID = d.OrderID
    WHERE i.Status <> d.Status;
END

FakeTable both tables, perform the DML, assert the audit row:

CREATE PROCEDURE TriggerTests.[test Orders_AfterUpdate logs status change]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.Orders';
    EXEC tSQLt.FakeTable 'dbo.OrderAuditLog';

    INSERT INTO dbo.Orders (OrderID, Status) VALUES (1, 'Pending');
    UPDATE dbo.Orders SET Status = 'Shipped' WHERE OrderID = 1;

    CREATE TABLE #Expected (OrderID INT, FieldChanged NVARCHAR(50), OldValue NVARCHAR(50), NewValue NVARCHAR(50));
    INSERT INTO #Expected VALUES (1, 'Status', 'Pending', 'Shipped');

    CREATE TABLE #Actual (OrderID INT, FieldChanged NVARCHAR(50), OldValue NVARCHAR(50), NewValue NVARCHAR(50));
    INSERT INTO #Actual SELECT OrderID, FieldChanged, OldValue, NewValue FROM dbo.OrderAuditLog;

    EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';
END;

The trigger fires as part of the UPDATE, inside the test transaction, and everything rolls back cleanly at the end.

Testing Views

Views can contain significant logic — CASE expressions, joins across multiple tables, computed columns, filtered results. FakeTable the underlying tables, insert controlled data, query the view, assert:

CREATE PROCEDURE ViewTests.[test ActiveCustomers excludes deleted customers]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.Customers';
    INSERT INTO dbo.Customers (CustomerID, Name, IsDeleted) VALUES
        (1, 'Luke Skywalker', 0),
        (2, 'Darth Vader',    1),  -- should be excluded
        (3, 'Leia Organa',    0);

    CREATE TABLE #Expected (CustomerID INT, Name NVARCHAR(100));
    INSERT INTO #Expected VALUES (1, 'Luke Skywalker'), (3, 'Leia Organa');

    CREATE TABLE #Actual (CustomerID INT, Name NVARCHAR(100));
    INSERT INTO #Actual SELECT CustomerID, Name FROM dbo.ActiveCustomers;

    EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';
END;

Testing Scalar Functions

Scalar functions are the simplest to test — inputs in, value out, no side effects:

CREATE PROCEDURE FunctionTests.[test GetAccountStatus returns GoodStanding for zero balance]
AS
BEGIN
    DECLARE @Result NVARCHAR(20) = dbo.GetAccountStatus(0.00);
    EXEC tSQLt.AssertEquals 'GoodStanding', @Result;
END;

When a scalar function calls another function, you can substitute a fake using FakeFunction (covered in a later post) to control what the dependency returns during the test.

The Common Thread

The pattern across all three object types is identical: isolate dependencies with FakeTable or SpyProcedure, exercise the object under test with controlled inputs, assert the output. The SQL object type changes the mechanics slightly — how you invoke it, which tables to fake — but not the principle.

If a SQL object contains logic worth caring about, you have the tools to test it. The only question is whether you will. As always, I'm here to help.

Read more