插入更新触发器如何确定是插入还是更新

触发器有特殊的 INSERTED和 DELETED表来跟踪“之前”和“之后”数据。因此，您可以使用类似于 IF EXISTS (SELECT * FROM DELETED)的东西来检测更新。在更新时，DELETED中只有行，但是 INSERTED中总是有行。

在 创建触发器中查找“插入”。

2011年11月23日

经过大量的搜索，我找不到一个单个 SQLServer 触发器的确切示例，该触发器可以处理触发器操作 INSERT、 UPDATE 和 DELETE 的所有(3)三个条件。最后，我找到了一行文本，其中提到当发生 DELETE 或 UPDATE 时，公共 DELETED 表将包含这两个操作的记录。基于这些信息，我创建了一个小的 Action 例程来确定触发器被激活的原因。当 INSERT 与 UPDATE 触发器上同时存在公共配置和特定操作时，有时需要使用这种类型的接口。在这些情况下，为 UPDATE 和 INSERT 创建单独的触发器将成为维护问题。(也就是说，这两个触发器是否都为必要的公共数据算法修复正确更新了?)

为此，我想给出以下多触发器事件代码片段，用于在一个触发器中处理 Microsoft SQL Server 的 INSERT、 UPDATE 和 DELETE。

CREATE TRIGGER [dbo].[INSUPDDEL_MyDataTable]
ON [dbo].[MyDataTable] FOR INSERT, UPDATE, DELETE
AS


-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with caller queries SELECT statements.
-- If an update/insert/delete occurs on the main table, the number of records affected
-- should only be based on that table and not what records the triggers may/may not
-- select.
SET NOCOUNT ON;


--
-- Variables Needed for this Trigger
--
DECLARE @PACKLIST_ID varchar(15)
DECLARE @LINE_NO smallint
DECLARE @SHIPPED_QTY decimal(14,4)
DECLARE @CUST_ORDER_ID varchar(15)
--
-- Determine if this is an INSERT,UPDATE, or DELETE Action
--
DECLARE @Action as char(1)
DECLARE @Count as int
SET @Action = 'I' -- Set Action to 'I'nsert by default.
SELECT @Count = COUNT(*) FROM DELETED
if @Count > 0
BEGIN
SET @Action = 'D' -- Set Action to 'D'eleted.
SELECT @Count = COUNT(*) FROM INSERTED
IF @Count > 0
SET @Action = 'U' -- Set Action to 'U'pdated.
END


if @Action = 'D'
-- This is a DELETE Record Action
--
BEGIN
SELECT @PACKLIST_ID =[PACKLIST_ID]
,@LINE_NO = [LINE_NO]
FROM DELETED


DELETE [dbo].[MyDataTable]
WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
END
Else
BEGIN
--
-- Table INSERTED is common to both the INSERT, UPDATE trigger
--
SELECT @PACKLIST_ID =[PACKLIST_ID]
,@LINE_NO = [LINE_NO]
,@SHIPPED_QTY =[SHIPPED_QTY]
,@CUST_ORDER_ID = [CUST_ORDER_ID]
FROM INSERTED


if @Action = 'I'
-- This is an Insert Record Action
--
BEGIN
INSERT INTO [MyChildTable]
(([PACKLIST_ID]
,[LINE_NO]
,[STATUS]
VALUES
(@PACKLIST_ID
,@LINE_NO
,'New Record'
)
END
else
-- This is an Update Record Action
--
BEGIN
UPDATE [MyChildTable]
SET [PACKLIST_ID] = @PACKLIST_ID
,[LINE_NO] = @LINE_NO
,[STATUS]='Update Record'
WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
END
END

CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;


--
-- Check if this is an INSERT, UPDATE or DELETE Action.
--
DECLARE @action as char(1);


SET @action = 'I'; -- Set Action to Insert by default.
IF EXISTS(SELECT * FROM DELETED)
BEGIN
SET @action =
CASE
WHEN EXISTS(SELECT * FROM INSERTED) THEN 'U' -- Set Action to Updated.
ELSE 'D' -- Set Action to Deleted.
END
END
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED) RETURN; -- Nothing updated or inserted.


...


END

这可能是一种更快捷的方式:

DECLARE @action char(1)


IF COLUMNS_UPDATED() > 0 -- insert or update
BEGIN
IF EXISTS (SELECT * FROM DELETED) -- update
SET @action = 'U'
ELSE
SET @action = 'I'
END
ELSE -- delete
SET @action = 'D'

所提供的两种解决方案的一个潜在问题是，更新查询可能更新零条记录，而插入查询可能插入零条记录，具体取决于它们的编写方式。在这些情况下，“插入”和“删除”记录集将为空。在许多情况下，如果“插入”和“删除”记录集都是空的，那么您可能只想退出触发器而不执行任何操作。

我发现格雷厄姆的一个小错误，否则很酷的解决方案:

DECLARE @action CHAR(8)
IF COLUMNS_UPDATED() <> 0 -- delete or update?
BEGIN
IF EXISTS (SELECT * FROM deleted) -- updated cols + old rows means action=update
SET @action = 'UPDATE'
ELSE
SET @action = 'INSERT' -- updated columns and nothing deleted means action=insert
END
ELSE -- delete
BEGIN
SET @action = 'DELETE'
END

为了安全起见:

--Determine if this is an INSERT,UPDATE, or DELETE Action or a "failed delete".
DECLARE @Action as char(1);
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)
THEN 'U'  -- Set Action to Updated.
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'I'  -- Set Action to Insert.
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'D'  -- Set Action to Deleted.
ELSE NULL -- Skip. It may have been a "failed delete".
END)

在第一个场景中，我假设您的表具有 IDENTITY 列

CREATE TRIGGER [dbo].[insupddel_yourTable] ON [yourTable]
FOR INSERT, UPDATE, DELETE
AS
IF @@ROWCOUNT = 0 return
SET NOCOUNT ON;
DECLARE @action nvarchar(10)
SELECT @action = CASE WHEN COUNT(i.Id) > COUNT(d.Id) THEN 'inserted'
WHEN COUNT(i.Id) < COUNT(d.Id) THEN 'deleted' ELSE 'updated' END
FROM inserted i FULL JOIN deleted d ON i.Id = d.Id

在第二种情况下，不需要使用 IDENTITTY 列

CREATE TRIGGER [dbo].[insupddel_yourTable] ON [yourTable]
FOR INSERT, UPDATE, DELETE
AS
IF @@ROWCOUNT = 0 return
SET NOCOUNT ON;
DECLARE @action nvarchar(10),
@insCount int = (SELECT COUNT(*) FROM inserted),
@delCount int = (SELECT COUNT(*) FROM deleted)
SELECT @action = CASE WHEN @insCount > @delCount THEN 'inserted'
WHEN @insCount < @delCount THEN 'deleted' ELSE 'updated' END

快速解决方案 MySQL

顺便说一下: 我正在使用 MySQL PDO。

(1)在自动递增表中，每个脚本先运行一次，就从递增的列中获得最高值(我的列名 = id) :

$select = "
SELECT  MAX(id) AS maxid
FROM    [tablename]
LIMIT   1
";

(2)像往常一样运行 MySQL 查询，并将结果转换为整数，例如:

$iMaxId = (int) $result[0]->maxid;

(3)在“ INSERT INTO... ON DUPLICATE KEY UPDATE”查询之后，按照你喜欢的方式获取最后一个插入的 id，例如:

$iLastInsertId = (int) $db->lastInsertId();

(4)比较和反应: 如果 lastInsertId 高于表中的最高值，那么它可能是一个 INSERT，对吗？反之亦然。

if ($iLastInsertId > $iMaxObjektId) {
// IT'S AN INSERT
}
else {
// IT'S AN UPDATE
}

我知道这很快，也许很肮脏。这是一个老职位。但是，嘿，我一直在寻找一个解决方案，也许有人发现我的方法有点用。一切顺利！

这对我来说很有用:

declare @action_type int;
select @action_type = case
when i.id is not null and d.id is     null then 1 -- insert
when i.id is not null and d.id is not null then 2 -- update
when i.id is     null and d.id is not null then 3 -- delete
end
from      inserted i
full join deleted  d on d.id = i.id

因为并不是所有的列都可以一次性更新，所以你可以通过以下方式检查一个特定的列是否被更新:

IF UPDATE([column_name])

很简单

CREATE TRIGGER [dbo].[WO_EXECUTION_TRIU_RECORD] ON [dbo].[WO_EXECUTION]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE
AS
BEGIN


select @vars = [column] from inserted
IF UPDATE([column]) BEGIN
-- do update action base on @vars
END ELSE BEGIN
-- do insert action base on @vars
END


END

DECLARE @INSERTEDCOUNT INT,
@DELETEDCOUNT INT


SELECT @INSERTEDCOUNT = COUNT([YourColumnName]) FROM inserted


SELECT @DELETEDCOUNT = COUNT([YourColumnName]) FROM deleted

如果更新的话

 @INSERTEDCOUNT = 1
@DELETEDCOUNT = 1

如果它的插入

 @INSERTEDCOUNT = 1
@DELETEDCOUNT = 0

declare @insCount int
declare @delCount int
declare @action char(1)


select @insCount = count(*) from INSERTED
select @delCount = count(*) from DELETED


if(@insCount > 0 or @delCount > 0)--if something was actually affected, otherwise do nothing
Begin
if(@insCount = @delCount)
set @action = 'U'--is update
else if(@insCount > 0)
set @action = 'I' --is insert
else
set @action = 'D' --is delete


--do stuff here
End

我正在使用下面的命令，它也正确地检测删除语句，删除任何东西:

CREATE TRIGGER dbo.TR_TableName_TriggerName
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;


IF NOT EXISTS(SELECT * FROM INSERTED)
-- DELETE
PRINT 'DELETE';
ELSE
BEGIN
IF NOT EXISTS(SELECT * FROM DELETED)
-- INSERT
PRINT 'INSERT';
ELSE
-- UPDATE
PRINT 'UPDATE';
END
END;

我已经使用这些 exists (select * from inserted/deleted)查询很长时间了，但对于空 CRUD 操作(当 inserted和 deleted表中没有记录时)仍然不够。因此，在对这个话题进行了一些研究之后，我找到了更精确的解决办法:

declare
@columns_count int = ?? -- number of columns in the table,
@columns_updated_count int = 0


-- this is kind of long way to get number of actually updated columns
-- from columns_updated() mask, it's better to create helper table
-- or at least function in the real system
with cte_columns as (
select @columns_count as n
union all
select n - 1 from cte_columns where n > 1
), cte_bitmasks as (
select
n,
(n - 1) / 8 + 1 as byte_number,
power(2, (n - 1) % 8) as bit_mask
from cte_columns
)
select
@columns_updated_count = count(*)
from cte_bitmasks as c
where
convert(varbinary(1), substring(@columns_updated_mask, c.byte_number, 1)) & c.bit_mask > 0


-- actual check
if exists (select * from inserted)
if exists (select * from deleted)
select @operation = 'U'
else
select @operation = 'I'
else if exists (select * from deleted)
select @operation = 'D'
else if @columns_updated_count = @columns_count
select @operation = 'I'
else if @columns_updated_count > 0
select @operation = 'U'
else
select @operation = 'D'

也可以使用 columns_updated() & power(2, column_id - 1) > 0查看列是否被更新，但是对于列数量大的表来说不安全。我使用了一种有点复杂的计算方法(参见下面有用的文章)。

• 对于审计触发器，由 Piotr Rodak 提供
• 处理非常大的位掩码

我喜欢“计算机科学优雅”的解决方案在这里，我的解决方案分别命中[插入]和[删除]两个伪表一次，以获取它们的状态，并将结果放入位映射变量中。然后，通过有效的二进制计算(除了不太可能的 INSERT 或 DELETE 组合) ，可以轻松地在整个触发器中测试 INSERT、 UPDATE 和 DELETE 的每个可能组合。

它确实假设，如果没有修改行，那么 DML 语句是什么并不重要(这应该满足绝大多数情况)。因此，虽然它不像罗曼 · 皮卡尔的解决方案那样完整，但它更有效率。

使用这种方法，我们可以为每个表设置一个“ FOR INSERT，UPDATE，DELETE”触发器，这样我们就可以 A)完全控制操作顺序，b)为每个适用于多个操作的操作设置一个代码实现。(显然，每个实现模型都有其优缺点; 您需要单独评估系统，以确定什么样的系统最有效。)

请注意，由于不存在磁盘访问(https://social.msdn.microsoft.com/Forums/en-US/01744422-23fe-42f6-9ab0-a255cdf2904a) ，因此“ vis (select * from“ insert/delete”)”语句非常有效。

use tempdb
;
create table dbo.TrigAction (asdf int)
;
GO
create trigger dbo.TrigActionTrig
on dbo.TrigAction
for INSERT, UPDATE, DELETE
as
declare @Action tinyint
;
-- Create bit map in @Action using bitwise OR "|"
set @Action = (-- 1: INSERT, 2: DELETE, 3: UPDATE, 0: No Rows Modified
(select case when exists (select * from inserted) then 1 else 0 end)
| (select case when exists (select * from deleted ) then 2 else 0 end))
;
-- 21 <- Binary bit values
-- 00 -> No Rows Modified
-- 01 -> INSERT -- INSERT and UPDATE have the 1 bit set
-- 11 -> UPDATE <
-- 10 -> DELETE -- DELETE and UPDATE have the 2 bit set


raiserror(N'@Action = %d', 10, 1, @Action) with nowait
;
if (@Action = 0) raiserror(N'No Data Modified.', 10, 1) with nowait
;
-- do things for INSERT only
if (@Action = 1) raiserror(N'Only for INSERT.', 10, 1) with nowait
;
-- do things for UPDATE only
if (@Action = 3) raiserror(N'Only for UPDATE.', 10, 1) with nowait
;
-- do things for DELETE only
if (@Action = 2) raiserror(N'Only for DELETE.', 10, 1) with nowait
;
-- do things for INSERT or UPDATE
if (@Action & 1 = 1) raiserror(N'For INSERT or UPDATE.', 10, 1) with nowait
;
-- do things for UPDATE or DELETE
if (@Action & 2 = 2) raiserror(N'For UPDATE or DELETE.', 10, 1) with nowait
;
-- do things for INSERT or DELETE (unlikely)
if (@Action in (1,2)) raiserror(N'For INSERT or DELETE.', 10, 1) with nowait
-- if already "return" on @Action = 0, then use @Action < 3 for INSERT or DELETE
;
GO


set nocount on;


raiserror(N'
INSERT 0...', 10, 1) with nowait;
insert dbo.TrigAction (asdf) select top 0 object_id from sys.objects;


raiserror(N'
INSERT 3...', 10, 1) with nowait;
insert dbo.TrigAction (asdf) select top 3 object_id from sys.objects;


raiserror(N'
UPDATE 0...', 10, 1) with nowait;
update t set asdf = asdf /1 from dbo.TrigAction t where asdf <> asdf;


raiserror(N'
UPDATE 3...', 10, 1) with nowait;
update t set asdf = asdf /1 from dbo.TrigAction t;


raiserror(N'
DELETE 0...', 10, 1) with nowait;
delete t from dbo.TrigAction t where asdf < 0;


raiserror(N'
DELETE 3...', 10, 1) with nowait;
delete t from dbo.TrigAction t;
GO


drop table dbo.TrigAction
;
GO

我认为嵌套有点令人困惑:

平面比嵌套好[ The Zen of Python ]

;)

DROP TRIGGER IF EXISTS AFTER_MYTABLE


GO


CREATE TRIGGER dbo.AFTER_MYTABLE ON dbo.MYTABLE AFTER INSERT, UPDATE, DELETE


AS BEGIN


--- FILL THE BEGIN/END SECTION FOR YOUR NEEDS.


SET NOCOUNT ON;


IF EXISTS(SELECT * FROM INSERTED)  AND EXISTS(SELECT * FROM DELETED)
BEGIN PRINT 'UPDATE' END
ELSE IF EXISTS(SELECT * FROM INSERTED)  AND NOT EXISTS(SELECT * FROM DELETED)
BEGIN PRINT 'INSERT' END
ELSE IF    EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
BEGIN PRINT 'DELETED' END
ELSE BEGIN PRINT 'NOTHING CHANGED'; RETURN; END  -- NOTHING


END

试试这个。

ALTER TRIGGER ImportacionesGS ON dbo.Compra
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
-- idCompra is PK
DECLARE @vIdCompra_Ins INT,@vIdCompra_Del INT
SELECT @vIdCompra_Ins=Inserted.idCompra FROM Inserted
SELECT @vIdCompra_Del=Deleted.idCompra FROM Deleted
IF (@vIdCompra_Ins IS NOT NULL AND @vIdCompra_Del IS NULL)
Begin
-- Todo Insert
End
IF (@vIdCompra_Ins IS NOT NULL AND @vIdCompra_Del IS NOT NULL)
Begin
-- Todo Update
End
IF (@vIdCompra_Ins IS NULL AND @vIdCompra_Del IS NOT NULL)
Begin
-- Todo Delete
End
END

Declare @Type varchar(50)='';
IF EXISTS (SELECT * FROM inserted) and  EXISTS (SELECT * FROM deleted)
BEGIN
SELECT @Type = 'UPDATE'
END
ELSE IF EXISTS(SELECT * FROM inserted)
BEGIN
SELECT @Type = 'INSERT'
END
ElSE IF EXISTS(SELECT * FROM deleted)
BEGIN
SELECT @Type = 'DELETE'
END

虽然我也很喜欢@Alex 发布的答案，但是我提供了上述@Graham 解决方案的变体

declare @action varchar(4)
IF EXISTS (SELECT * FROM INSERTED)
BEGIN
IF EXISTS (SELECT * FROM DELETED)
SET @action = 'U'  -- update
ELSE
SET @action = 'I'  --insert
END
ELSE IF EXISTS (SELECT * FROM DELETED)
SET @action = 'D'  -- delete
else
set @action = 'noop' --no records affected
--print @action

你会得到如下语句的 NOOP:

update tbl1 set col1='cat' where 1=2

declare @result as smallint
declare @delete as smallint = 2
declare @insert as smallint = 4
declare @update as smallint = 6
SELECT @result = POWER(2*(SELECT count(*) from deleted),1) + POWER(2*(SELECT
count(*) from inserted),2)


if (@result & @update = @update)
BEGIN
print 'update'
SET @result=0
END
if (@result & @delete = @delete)
print 'delete'
if (@result & @insert = @insert)
print 'insert'

我这样做:

select isnull((select top 1 1 from inserted t1),0) + isnull((select top 1 2 from deleted t1),0)

1-> 插入

2-> 删除

3-> 更新

set @i = isnull((select top 1 1 from inserted t1),0) + isnull((select top 1 2 from deleted t1),0)
--select @i


declare @action varchar(1) = case @i when 1 then 'I' when 2 then 'D' when 3 then 'U' end
--select @action




select @action c1,* from inserted t1 where @i in (1,3) union all
select @action c1,* from deleted t1 where @i in (2)

DECLARE @ActionType CHAR(6);
SELECT  @ActionType = COALESCE(CASE WHEN EXISTS(SELECT * FROM INSERTED)
AND EXISTS(SELECT * FROM DELETED)  THEN 'UPDATE' END,
CASE WHEN EXISTS(SELECT * FROM DELETED)  THEN 'DELETE' END,
CASE WHEN EXISTS(SELECT * FROM INSERTED) THEN 'INSERT' END);
PRINT   @ActionType;