檢查記錄是否在表中插入或更新

我創建了一個觸發器，如果[name] <= 2執行更新語句的長度。這個觸發器有 98% 的時間觸發，這讓我相信 2% 的時間它沒有觸發意味著記錄沒有Updated()觸發Inserted()

這是範例 DDL 和我的觸發器，我的觸發器語法有問題嗎？還是我應該尋找一種方法來確定記錄是否已更新？

Create Table TwoNumericNames
(
 name varchar(500)
)

Insert Into TwoNumericNames (name) Values
('AA'), ('RR'), ('CC'), ('MM'), ('EE')


CREATE TRIGGER trgUpdateName
ON [TwoNumericNames]
FOR INSERT
AS
Begin

UPDATE [TwoNumericNames]
SET [name] = CASE
               WHEN [name] LIKE '%AA%' THEN 'Alpha Awe'
               WHEN [name] LIKE '%RR%' THEN 'Raw Red'
               WHEN [name] LIKE '%CC%' THEN 'Capt Cree'
               WHEN [name] LIKE '%MM%' THEN 'My More'
               WHEN [name] LIKE '%EE%' THEN 'Ed Ewe'
           END 
WHERE LEN([name]) <= 2

END

編輯

也許AfterInsert()觸發器會更好。這是應該設置的方式嗎？

Create Table TwoNumericNames
(
 id int,
 name varchar(500)
)

Insert Into TwoNumericNames (name) Values
(1,'AA'), (2,'RR'), (3,'CC'), (4,'MM'), (5,'EE')

CREATE TRIGGER [dbo].[trgUpdateName] ON [dbo].[TwoNumericNames] 
AFTER INSERT
AS 
BEGIN
 SET NOCOUNT ON;

 -- get the last id value of the record inserted or updated
 DECLARE @id INT
 SELECT @id = [Name]
 FROM INSERTED

 -- Insert statements for trigger here
 UPDATE [dbo].[TwoNumericNames] 
 SET [name] = CASE
               WHEN [name] LIKE '%AA%' THEN 'Alpha Awe'
               WHEN [name] LIKE '%RR%' THEN 'Raw Red'
               WHEN [name] LIKE '%CC%' THEN 'Capt Cree'
               WHEN [name] LIKE '%MM%' THEN 'My More'
               WHEN [name] LIKE '%EE%' THEN 'Ed Ewe'
           END 
 WHERE [id] = @id 

END

正如許多人所評論的那樣，您希望隔離觸發器以僅在必要時觸發和修改。這意味著您將不得不僅過濾插入的記錄。您可以使用inserted視圖執行此操作。正如您將從下面的範常式式碼中看到的那樣，我稍微修改了您的程式碼以更容易地查看發生了什麼並將更新語句隔離為僅影響插入的行。

--create the table
create table TwoNumericNames (rowID int IDENTITY, name nvarchar(500))

--insert a row
INSERT TwoNumericNames
VALUES (N'AA')

--note the row
select * from TwoNumericNames;
go

--create trigger
CREATE TRIGGER trgUpdateName
ON TwoNumericNames
FOR INSERT
AS
Begin

   UPDATE T
   SET T.[name] = CASE
                   WHEN I.[name] LIKE '%AA%' THEN 'Alpha Awe'
                   WHEN I.[name] LIKE '%RR%' THEN 'Raw Red'
                   WHEN I.[name] LIKE '%CC%' THEN 'Capt Cree'
                   WHEN I.[name] LIKE '%MM%' THEN 'My More'
                   WHEN I.[name] LIKE '%EE%' THEN 'Ed Ewe'
                   ELSE I.[name]
               END 
   FROM TwoNumericNames T
   JOIN inserted I ON I.rowID = T.rowID

END
go

--insert another row
INSERT TwoNumericNames
VALUES (N'RR')

--note only the one record is updated
select * from TwoNumericNames

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