0

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?

0
2

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.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.