Performing Unit Tests on Triggers

Testing triggers from DB side or how to do unit testing of triggers without even going on the front-end.

People often ask me that without having real world data, how can we test these. The answer is that for performing any kind of unit testing we do not require to actually commit the data that we are playing with.

So in this article I will talk about how to perform a unit test for INSERT, DELETE and UPDATE triggers without even actually creating extra data/modifying existing data. For achieving the end result I am going to use TRANSACTION CONTROL (BEGINTRAN /ROLLBACK) and NOLOCK dirty read concept. Also I here I am just talking about method so my test cases very straight forward; but how much complicated the triggers are these approaches would work.

To explain how to perform unit test case for each action, I am going to use below two tables and trigger scripts. I have also included the script to populate the data.

-- main table

     CREATE TABLE trigger_test1

(

id int primary key,

value1 int,

value2 int

)

-- audit table

   CREATE TABLE trigger_test2

(

id int,

value1 int,

value2 int

)

-- INSERT trigger

CREATE TRIGGER insert_trigger

ON trigger_test1

FOR INSERT AS

BEGIN

UPDATE a

SET value2= i.value1*2

FROM trigger_test1 a

JOIN INSERTED i

ON a.id = i.id

END

GO

-- UPDATE trigger

CREATE TRIGGER update_trigger

ON trigger_test1

FOR UPDATE AS

BEGIN

UPDATE a

SET value2= i.value1*2

FROM trigger_test1 a

JOIN INSERTED i

ON a.id = i.id

END

GO

-- DELETE trigger

CREATE TRIGGER delete_trigger

ON trigger_test1

FOR DELETE AS

BEGIN

INSERT INTO trigger_test2

SELECT * FROM DELETED

END

GO

--inserting test values

INSERT INTO trigger_test1 (id, value1) SELECT 1,10

INSERT INTO trigger_test1 (id, value1) SELECT 2,10

INSERT INTO trigger_test1 (id, value1) SELECT 3,10

INSERT INTO trigger_test1 (id, value1) SELECT 4,10

INSERT INTO trigger_test1 (id, value1) SELECT 5,10

GO

INSERT trigger test method:

Following script can be used for performing INSERT test case.



BEGIN TRAN

/* to check initial state of the data
(you can have where condition as per your requirement)
HERE having "nolock" is important to make
a dirty read within transaction
*/
SELECT * FROM trigger_test1 with (nolock) WHERE ID = 6

INSERT INTO trigger_test1 (id, value1) SELECT 6,60

SELECT * FROM trigger_test1 with (nolock) WHERE ID = 6

ROLLBACK

DELETE trigger test method:

Following script can be used for performing DELETE test case.

BEGIN TRAN

SELECT 'trigger_test1',* FROM trigger_test1 with (nolock) WHERE ID = 5

SELECT 'trigger_test2',* FROM trigger_test2 with (nolock) WHERE ID = 5

DELETE FROM trigger_test1  WHERE ID = 5

SELECT 'trigger_test1',* FROM trigger_test1 with (nolock) WHERE ID = 5

SELECT 'trigger_test2',* FROM trigger_test2 with (nolock) WHERE ID = 5

ROLLBACK

UPDATE trigger test method:

Following script can be used for performing UPDATE test case.

BEGIN TRAN

SELECT * FROM trigger_test1 with (nolock) WHERE ID = 5

UPDATE trigger_test1 SET value1 = 30 WHERE ID = 5

SELECT * FROM trigger_test1 with (nolock) WHERE ID = 5

ROLLBACK

Finally suppose if we happen to have both INSERT and DELETE triggers implemented then these both can be tested through one SQL as depicted below. This approach is also has two more benefits that you can use existing data sitting within same table for INSERT trigger testing. The trick is to USE temporary table.

BEGIN TRAN

SELECT 'trigger_test1',* FROM trigger_test1 with (nolock) WHERE ID = 5

SELECT 'trigger_test2',* FROM trigger_test2 with (nolock) WHERE ID = 5

SELECT * INTO #tmp FROM trigger_test1  WHERE ID = 5

DELETE FROM trigger_test1  WHERE ID = 5

INSERT INTO trigger_test1  SELECT * FROM #tmp

SELECT 'trigger_test1',* FROM trigger_test1 with (nolock) WHERE ID = 5

SELECT 'trigger_test2',* FROM trigger_test2 with (nolock) WHERE ID = 5

DROP TABLE #tmp

ROLLBACK