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;
0
试试这个..
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
2
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
0
Quick solution 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为您的首选方式,例如:
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
0
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
5
我相信嵌套ifs有点令人困惑,并且:
Flat比嵌套更好[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
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
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
--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)
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
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
1
这可能是一种更快的方式:
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'
110
只是简单的方法
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
5
在第一个场景中,我认为你的表有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
我已经使用了那些 exists (select * from inserted/deleted) 查询很长一段时间了,但它's still not enough for empty CRUD operations (when there'在 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 查看列是否已更新,但是's not safe for tables with big number of columns. I'使用了一种复杂的计算方法(请参阅下面的有用文章) .
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
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
20 回答
我正在使用以下内容,它还正确检测删除任何内容的删除语句:
试试这个..
如果它的更新
如果插入
Quick solution MySQL
顺便说一下:我正在使用MySQL PDO .
(1)在自动增量表中,只要每个脚本首先运行,就从增量列中获取最高值(我的列名= id):
(2)按照你的规范运行MySQL查询,并将结果转换为整数,例如:
(3)在“INSERT INTO ... ON DUPLICATE KEY UPDATE”查询后,获取最后插入的id为您的首选方式,例如:
(4)比较和反应:如果lastInsertId高于表中的最高值,它可能是一个INSERT,对吗?反之亦然 .
我知道这很快,也许很脏 . 这是一个老帖子 . 但是,嘿,我正在寻找一个解决方案很长一段时间,也许有人发现我的方式有点有用 . 祝一切顺利!
我相信嵌套ifs有点令人困惑,并且:
;)
虽然我也喜欢@Alex发布的答案,但我在上面的@ Graham解决方案中提供了这种变化
这仅在INSERTED和UPDATED表中使用记录存在,而不是在第一次测试中使用COLUMNS_UPDATED . 它还为偏执的程序员提供了解决,因为他们知道最后的案例已被考虑......
你会得到NOOP,声明如下:
如果您运行删除任何内容的删除语句,其中许多建议都不会考虑 .
假设您尝试删除ID等于表中不存在的某个值的位置 .
您的触发器仍会被调用,但“已删除”或“已插入”表中没有任何内容 .
使用它是安全的:
特别感谢@KenDog和@Net_Prog的回答 .
我从他们的脚本中构建了这个 .
经过大量搜索后,我找不到单个SQL Server触发器的确切示例,该触发器处理触发器操作INSERT,UPDATE和DELETE的所有(3)三个条件 . 我终于找到了一行文字,其中谈到了当发生DELETE或UPDATE时,公共DELETED表将包含这两个动作的记录 . 基于该信息,我创建了一个小的Action例程,用于确定触发器被激活的原因 . 当在INSERT与UPDATE触发器上同时存在公共配置和特定操作时,有时需要此类型的接口 . 在这些情况下,为UPDATE和INSERT创建单独的触发器将成为维护问题 . (即两个触发器是否都已正确更新以进行必要的通用数据算法修复?)
为此,我想提供以下多触发事件代码片段,用于在Microsoft SQL Server的一个触发器中处理INSERT,UPDATE,DELETE .
我在格雷厄姆发现了一个小错误,否则很酷的解决方案:
它应该是IF COLUMNS_UPDATED() < > 0 - 插入或更新
而不是> 0可能因为顶部位被解释为SIGNED整数符号位...(?) . 所以总的来说:
这可能是一种更快的方式:
只是简单的方法
在第一个场景中,我认为你的表有IDENTITY列
在第二种情况下,不需要使用IDENTITTY列
提供的两个解决方案的潜在问题在于,根据它们的编写方式,更新查询可以更新零记录,并且插入查询可以插入零记录 . 在这些情况下,Inserted和Deleted记录集将为空 . 在许多情况下,如果Inserted和Deleted记录集都为空,您可能只想退出触发器而不做任何事情 .
我已经使用了那些
exists (select * from inserted/deleted)
查询很长一段时间了,但它's still not enough for empty CRUD operations (when there'在inserted
和deleted
表中没有记录 . 所以在研究了这个主题后,我发现了更精确的解决方案:也可以使用
columns_updated() & power(2, column_id - 1) > 0
查看列是否已更新,但是's not safe for tables with big number of columns. I'使用了一种复杂的计算方法(请参阅下面的有用文章) .此外,这种方法仍然会错误地将某些更新分类为插入(如果表中的每一列都受更新影响),并且可能会将只插入默认值的插入分类为删除,但这些是稀有操作的王者(在租用我的系统他们是) . 除此之外,我目前还不知道如何改进这个解决方案 .
COLUMNS_UPDATED() for audit triggers by Piotr Rodak
Dealing with very large bitmasks by Adam Machanic
我喜欢“计算机科学优雅”的解决方案 . 我的解决方案在这里每次点击[inserted]和[deleted] pseudotables来获取它们的状态并将结果放在一个位映射变量中 . 然后,INSERT,UPDATE和DELETE的每个可能组合都可以通过有效的二进制评估在整个触发器中进行测试(除了不太可能的INSERT或DELETE组合) .
如果没有修改行(这应该满足绝大多数情况),它确实假设DML语句是什么并不重要 . 因此,虽然它不像Roman Pekar的解决方案那么完整,但效率更高 .
通过这种方法,我们可以在每个表中使用一个“FOR INSERT,UPDATE,DELETE”触发器,从而为我们提供A)对操作顺序的完全控制,以及b)每个多动作适用操作的一个代码实现 . (显然,每种实现模型都有其优点和缺点;您需要单独评估您的系统以获得最佳效果 . )
请注意,"exists (select * from «inserted/deleted»)"语句非常有效,因为没有磁盘访问(https://social.msdn.microsoft.com/Forums/en-US/01744422-23fe-42f6-9ab0-a255cdf2904a) .
如果是MS SQL Server ......
触发器具有特殊的
INSERTED
和DELETED
表来跟踪"before"和"after"数据 . 因此,您可以使用IF EXISTS (SELECT * FROM DELETED)
之类的内容来检测更新 . 更新中只有DELETED
中的行,但INSERTED
中始终存在行 .在CREATE TRIGGER中查找"inserted"
编辑,2011年11月23日
评论后,此答案仅适用于
INSERTED
和UPDATED
触发器 .显然,正如我上面所说,DELETE触发器不能具有“始终在
INSERTED
中的行”这对我有用:
由于并非所有列都可以一次更新,因此可以通过以下方式检查特定列是否正在更新: