如何判斷觸發器是否失敗

我有一個觸發器，我想嘗試根據另一個表的值更新一個欄位。我已手動驗證數據是否存在於另一個表中，因此我知道聯接將起作用。但是，數據被插入到表中，但沒有進行更新。這讓我覺得我用觸發器做了一些事情

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

另外，如果觸發器出現錯誤，或者沒有成功執行，記錄還會插入到表中嗎？

我擔心where帶有 的條款in會引起問題。試著joining去inserted桌子。這裡有兩個範例 - 一個帶有in子句，一個帶有 a jointo insertedtable。使用我的樣本數據得出的結果有所不同。

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

---

觸發器程式碼作為在基表中實際插入行的事務的一部分執行。如果觸發器遇到致命錯誤，則事務應回滾在該事務期間完成的所有內容。這是一個全有或全無的過程。

即使使用 try/catch 邏輯，您也無法在觸發器中吞下異常。觸發器內的任何異常都將中止事務。

引用自：https://dba.stackexchange.com/questions/274905