Enabling trigger in unit test

Hi Niels,

Mock data does not execute the Triggers, but the Unit test itself does.

When you have a Update statement typed Unit test for example, you supply input values and a Row filter to update a particular row or multiple rows. This in turn will result in the Trigger being run. With Assertion query you can verify the Trigger did its job and with Expected messages you can select any messages that you expect to receive when this triggers runs.

Same goes for Insert statement typed Unit tests. Supply input values which the Unit test will use to insert a record into the table with that trigger.

Okay, I am trying to make this work, but the trigger doesn't run directly after the update statement, and before the assertion query. So this doesn't work.

Can you share the Unit test code? You can see the code using the Task with the eye:

-- Transaction for unit test execution
begin transaction;
declare @tsf_error_msg nvarchar(max);
declare @tsf_error_catch bit = 0;
begin try
    -- Canary table to check if the transaction is unintentionally committed
    create table unit_test_canary(unit_test_canary char(1));
    -- Disable triggers
    alter table [asset] disable trigger all;
    alter table [asset_maintenance_repair] disable trigger all;
    alter table [asset_maintenance_repair_cost] disable trigger all;
    alter table [asset_maintenance_repair_cost_group] disable trigger all;
    -- Execute tsf_mock_tab
    exec tsf_mock_tab 'asset';
    -- Execute tsf_mock_tab
    exec tsf_mock_tab 'asset_maintenance_repair';
    -- Execute tsf_mock_tab
    exec tsf_mock_tab 'asset_maintenance_repair_cost';
    -- Execute tsf_mock_tab
    exec tsf_mock_tab 'asset_maintenance_repair_cost_group';

    -- Insert the data
    set identity_insert [asset] on;
    insert into [asset] ([company_id], [asset_id], [asset_group], [identification]) values (2, 1, 3, 'R-049-NS');
    set identity_insert [asset] off;
    set identity_insert [asset_maintenance_repair] on;
    insert into [asset_maintenance_repair] ([company_id], [asset_maintenance_repair_id], [po_number], [asset_repair_workshop_id], [asset_id]) values (2, 1, 11, 1, 1);
    set identity_insert [asset_maintenance_repair] off;
    set identity_insert [asset_maintenance_repair_cost] on;
    insert into [asset_maintenance_repair_cost] ([company_id], [asset_maintenance_repair_cost_id], [asset_maintenance_repair_id], [asset_maintenance_repair_cost_code_groep_id], [invoice_no], [asset_repair_workshop_id], [Invoice_total_amount]) values (2, 1, 1, 1, N'123', 1, 1000);
    set identity_insert [asset_maintenance_repair_cost] off;
    set identity_insert [asset_maintenance_repair_cost] on;
    insert into [asset_maintenance_repair_cost] ([company_id], [asset_maintenance_repair_cost_id], [asset_maintenance_repair_id], [asset_maintenance_repair_cost_code_groep_id], [invoice_no], [asset_repair_workshop_id], [Invoice_total_amount]) values (2, 2, 1, 1, N'124', 1, 800);
    set identity_insert [asset_maintenance_repair_cost] off;
    set identity_insert [asset_maintenance_repair_cost_group] on;
    insert into [asset_maintenance_repair_cost_group] ([company_id], [asset_maintenance_repair_cost_group_id], [asset_maintenance_repair_cost_id], [actual_amount]) values (2, 1, 1, 300);
    set identity_insert [asset_maintenance_repair_cost_group] off;
    set identity_insert [asset_maintenance_repair_cost_group] on;
    insert into [asset_maintenance_repair_cost_group] ([company_id], [asset_maintenance_repair_cost_group_id], [asset_maintenance_repair_cost_id], [actual_amount]) values (2, 2, 1, 300);
    set identity_insert [asset_maintenance_repair_cost_group] off;
    set identity_insert [asset_maintenance_repair_cost_group] on;
    insert into [asset_maintenance_repair_cost_group] ([company_id], [asset_maintenance_repair_cost_group_id], [asset_maintenance_repair_cost_id], [actual_amount]) values (2, 3, 1, 400);
    set identity_insert [asset_maintenance_repair_cost_group] off;
    set identity_insert [asset_maintenance_repair_cost_group] on;
    insert into [asset_maintenance_repair_cost_group] ([company_id], [asset_maintenance_repair_cost_group_id], [asset_maintenance_repair_cost_id], [actual_amount]) values (2, 4, 2, 300);
    set identity_insert [asset_maintenance_repair_cost_group] off;
    set identity_insert [asset_maintenance_repair_cost_group] on;
    insert into [asset_maintenance_repair_cost_group] ([company_id], [asset_maintenance_repair_cost_group_id], [asset_maintenance_repair_cost_id], [actual_amount]) values (2, 5, 2, 200);
    set identity_insert [asset_maintenance_repair_cost_group] off;
    set identity_insert [asset_maintenance_repair_cost_group] on;
    insert into [asset_maintenance_repair_cost_group] ([company_id], [asset_maintenance_repair_cost_group_id], [asset_maintenance_repair_cost_id], [actual_amount]) values (2, 6, 2, 450);
    set identity_insert [asset_maintenance_repair_cost_group] off;
    -- Enable triggers
    alter table [asset] enable trigger all;
    alter table [asset_maintenance_repair] enable trigger all;
    alter table [asset_maintenance_repair_cost] enable trigger all;
    alter table [asset_maintenance_repair_cost_group] enable trigger all;
end try
begin catch
    set @tsf_error_msg = error_message();
    raiserror (@tsf_error_msg, 16, 1);
end catch
begin try
    -- Declare parameters

    -- Execute unit test
    update t1
       set t1.invoice_no = '12345'
      from asset_maintenance_repair_cost t1
     where t1.asset_maintenance_repair_cost_id = '1';
    -- Assertion query
    if not exists (select 1 from asset_maintenance_repair_cost amrc where amrc.actual_amount = 1000)
    begin
        exec tsf_send_assertion_msg 0, 'Failed: actual_amount is not calculated correctly';
    end
    else
    begin
        exec tsf_send_assertion_msg 1, 'Successful';
    end

end try
begin catch
    set @tsf_error_msg = error_message();
    set @tsf_error_catch = 1;
end catch
-- If there are no active transactions
if @@trancount = 0
begin
    -- If the canary table still exists, the transaction is committed
    if exists(select 1
                from sys.tables
               where name = 'unit_test_canary')
    begin
        exec tsf_send_assertion_msg 0, 'Failed: The logic managed to commit the unit test transaction. The mock tables are truncated. Please restore a backup and remove the random commit statement.';
        drop table unit_test_canary;
    end
    -- Else the transaction is rolled back
    else
    begin
        raiserror ('TSF_ROLLBACK', 9, 1);
        -- Throw catched error message if occurred
        if @tsf_error_catch = 1
        begin
            raiserror (@tsf_error_msg, 16, 1);
        end
    end
end
else
begin
    -- Throw catched error message if occurred
    if @tsf_error_catch = 1
    begin
        raiserror (@tsf_error_msg, 16, 1);
    end
    -- Rollback unit test execution
    rollback;
end

For what I can see table asset_maintenance_repair_cost should have an Update trigger. You can verify that on the targeted database. The unit test does enable triggers for that table after inserting all mock data; alter table [asset_maintenance_repair] enable trigger all;

So it could be a couple things:

• The Update trigger is not yet rolled out to the targeted database, leaving asset_maintenance_repair without a Update trigger
• The Update trigger code and the Assertion quert do not align.
  • I recommend filtering the if not exists query on t1.asset_maintenance_repair_cost_id = '1';
• Or something I can't think of now

SQL Server does run the trigger right after the action and therefore before the assertion query.

The update trigger is definitely rolled out to the target database, because it works in our production environment. I have filtered the query on the cost_id = 1, and that works. The thing is that I want to test the trigger with this unit test, so it should correctly display the actual_amount column. 

Okay, I've managed to make this work. Since the trigger looks at the actual_amount column in our invoice posts table, I also had to make an update statement on this table to trigger the actual trigger. Now the actual_amount column of our invoice table is being filled, and works correctly. This however forces me to update the value of actual_amount of a row. Not ideal, and feels hacky to me. 

The mock data contains a row in our table where the id = 1 and actual_amount is 300. We have another row with id = 2 and actual_amount of 200. Now these amounts are summed up towards the table that is ‘above’ this table to make for a total invoice amount of 500. Now in our unit test we have to update the row with id = 1 to trigger the trigger, but we don't change the value. The update is actual_amount = 300. So it stays the same, but we still have to run the update statement. I don't know if there is a better way to do this that doesn't feel as hacky as this?

Well we don’t apply any magic on it 😋 When the Unit test performs the query:

update t1
set t1.invoice_no = '12345'
from asset_maintenance_repair_cost t1
where t1.asset_maintenance_repair_cost_id = '1';

The Inserted (and Deleted) table becomes available and has (I suppose) one record in it; the one of asset_maintenance_repair_cost_id = '1’.

In you trigger code, you may combine this inserted and/or deleted row with any of the rows present in asset_maintenance_repair_cost or any other table. If you wish to perform an update on another row or combine several rows, you will need to join the inserted row on the table.

Could you share the (part of) trigger code that should run after this update statement is performed?

Awesome! So everything solved? 😄

Edit: for readability, you could opt to use the WITH statement:

WITH CTE AS
(
    SELECT 
        SUM(AMRC.actual_amount)AA, 
        I.asset_maintenance_repair_cost_id ID
    FROM asset_maintenance_repair_cost AMR
    INNER JOIN Inserted I 
      ON AMR.asset_maintenance_repair_cost_id = I.asset_maintenance_repair_cost_id
    INNER JOIN asset_maintenance_repair_cost_group AMRC 
      ON AMR.asset_maintenance_repair_cost_id = AMRC.asset_maintenance_repair_cost_id
    GROUP BY I.asset_maintenance_repair_cost_id
)
UPDATE AMRC
SET amrc.actual_amount = S.AA
FROM asset_maintenance_repair_cost amrc
INNER JOIN CTE S 
  ON S.ID = AMRC.asset_maintenance_repair_cost_id

Yes, works now. Albeit it feeling weird to do it in this way. Thank you Mark.