How To Tell if Trigger is failing

Question

I have a trigger, that I want to try to update a field based off the value of another table. I have manually verified that the data exists in the other table so I know the join will work. However, data is being inserted into the table, but no update made. Which makes me think I did something off with the trigger

Create Trigger [dbo].[trig_OnInsert] ON [dbo].[SHIPMENT]
For Insert
    AS
        BEGIN
            UPDATE si
            SET si.au = au.au + ' - ' + si.au
            FROM SHIPMENT si
            JOIN alphauniversal au
            ON si.cm_controllerID = au.controllerid
            WHERE si.cm_controllerID IN (Select si.cm_controllerID FROM Inserted)
        End

Also, if the trigger presents an error, or doesn't execute successfully, will the record still be inserted in the table?

Answer 1

I fear the where clause with the in is causing problems. Try joining to the inserted table instead. Here are two examples - one with the in clause and one with a join to the inserted table. The results are different using my sample data.

drop table if exists shipment;
drop table if exists alphauniversal;
go
create table SHIPMENT (cm_controllerID int,au varchar(100))
create table alphauniversal (controllerid int, au varchar(100))
insert into alphauniversal values(1,'abc')
insert into alphauniversal values(2,'xyz')
go
Create Trigger [dbo].[trig_OnInsert] ON [dbo].[SHIPMENT]
For Insert
    AS
        BEGIN
            UPDATE si
            SET si.au = au.au + ' - ' + si.au
            FROM SHIPMENT si
            JOIN alphauniversal au
            ON si.cm_controllerID = au.controllerid
            WHERE si.cm_controllerID IN (Select si.cm_controllerID FROM Inserted)
            --JOIN inserted i on i.cm_controllerID=si.cm_controllerID
        End
go
insert into SHIPMENT values(1,'test1')
insert into SHIPMENT values(2,'test2')
select * from shipment

---

cm_controllerID     au
1                  abc - abc - test1
2                  xyz - test2

---

drop table if exists shipment;
drop table if exists alphauniversal;
go
create table SHIPMENT (cm_controllerID int,au varchar(100))
create table alphauniversal (controllerid int, au varchar(100))
insert into alphauniversal values(1,'abc')
insert into alphauniversal values(2,'xyz')
go
Create Trigger [dbo].[trig_OnInsert] ON [dbo].[SHIPMENT]
For Insert
    AS
        BEGIN
            UPDATE si
            SET si.au = au.au + ' - ' + si.au
            FROM SHIPMENT si
            JOIN alphauniversal au
            ON si.cm_controllerID = au.controllerid
            --WHERE si.cm_controllerID IN (Select si.cm_controllerID FROM Inserted)
            JOIN inserted i on i.cm_controllerID=si.cm_controllerID
        End
go
insert into SHIPMENT values(1,'test1')
insert into SHIPMENT values(2,'test2')
select * from shipment

---

cm_controllerID au
1               abc - test1
2               xyz - test2

---

The trigger code runs as part of the transaction that actually inserted the rows in the base table. If the trigger encounters a fatal error, the transaction should roll back everything that was done during that transaction. It's an all-or-nothing process.

You cannot swallow an exception inside a trigger even with try/catch logic. Any exception inside a trigger WILL abort the transaction.