插入更新触发器如何确定插入或更新

I need to write an Insert, Update Trigger on table A which will delete all rows from table B whose one column (say Desc) has values like the value inserted/updated in the table A's column (say Col1). How would I go around writing it so that I can handle both Update and Insert cases. How would I determine if the trigger is executed for an update or insert.

我需要在表A上编写一个Insert、Update触发器，该触发器将从表B中删除所有行，表B的一个列(比如Desc)的值与表A的列(比如Col1)中插入/更新的值类似。我怎么写它，这样我就可以处理更新和插入的情况。如何确定触发器是否为更新或插入执行。

20 个解决方案

#1

If it's MS SQL Server...

如果是MS SQL Server…

Triggers have special INSERTED and DELETED tables to track "before" and "after" data. So you can use something like IF EXISTS (SELECT * FROM DELETED) to detect an update. You only have rows in DELETED on update, but there are always rows in INSERTED.

触发器具有特殊的插入和删除表，用于跟踪“前”和“后”数据。因此，您可以使用IF存在(从删除中选择*)来检测更新。更新时，只删除行，但始终插入行。

Look for "inserted" in CREATE TRIGGER

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

Edit, 23 Nov 2011

编辑,2011年11月23日

After comment, this answer is only for INSERTED and UPDATED triggers.
Obviously, DELETE triggers can not have "always rows in INSERTED" as I said above

注释之后，这个答案只适用于插入和更新的触发器。显然，DELETE触发器不能像我上面说的那样“总是插入行”

#2

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

#3

Many of these suggestions do not take into account if you run a delete statement that deletes nothing.
Say you try to delete where an ID equals some value that does not exist in the table.
Your trigger still gets called but there is nothing in the Deleted or Inserted tables.

如果运行一个不删除任何内容的delete语句，那么这些建议中的许多都不考虑。假设您试图删除一个ID等于表中不存在的某个值的地方。您的触发器仍然被调用，但是删除或插入的表中没有任何内容。

Use this to be safe:

为了安全起见:

--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)

Special thanks to @KenDog and @Net_Prog for their answers.
I built this from their scripts.

特别感谢@KenDog和@Net_Prog的回答。我是根据他们的脚本构建的。

#4

I'm using the following, it also correctly detect delete statements that delete nothing:

我用的是下面的方法，它也能正确检测到什么都不删除的删除语句:

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;

#5

After a lot of searching I could not find an exact example of a single SQL Server trigger that handles all (3) three conditions of the trigger actions INSERT, UPDATE, and DELETE. I finally found a line of text that talked about the fact that when a DELETE or UPDATE occurs, the common DELETED table will contain a record for these two actions. Based upon that information, I then created a small Action routine which determines why the trigger has been activated. This type of interface is sometimes needed when there is both a common configuration and a specific action to occur on an INSERT vs. UPDATE trigger. In these cases, to create a separate trigger for the UPDATE and the INSERT would become maintenance problem. (i.e. were both triggers updated properly for the necessary common data algorithm fix?)

在大量搜索之后，我找不到单个SQL Server触发器的确切示例，它处理触发器操作插入、更新和删除的所有(3)个条件。我最终找到了一行文本，其中讨论了这样一个事实:当发生删除或更新时，公共已删除表将包含这两个操作的记录。基于这些信息，我创建了一个小的动作例程，该例程确定触发器为什么被激活。当在INSERT vs. UPDATE触发器上同时出现公共配置和特定操作时，有时需要这种类型的接口。在这些情况下，为更新和插入创建一个单独的触发器将成为维护问题。(即两个触发器是否都得到了适当的更新以进行必要的通用数据算法修正?)

To that end, I would like to give the following multi-trigger event code snippet for handling INSERT, UPDATE, DELETE in one trigger for an Microsoft SQL Server.

为此，我想提供以下多触发器事件代码片段，用于处理Microsoft SQL服务器的一个触发器中的插入、更新和删除。

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   

#6

I believe nested ifs a little confusing and:

我认为嵌套的ifs有点混乱:

Flat is better than nested [The Zen of Python]

平坦优于嵌套[Python的Zen]

;)

,)

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

#7

Try this..

试试这个. .

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

#8

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

#9

A potential problem with the two solutions offered is that, depending on how they are written, an update query may update zero records and an insert query may insert zero records. In these cases, the Inserted and Deleted recordsets will be empty. In many cases, if both the Inserted and Deleted recordsets are empty you might just want to exit the trigger without doing anything.

这两个解决方案的一个潜在问题是，更新查询可能更新零个记录，而插入查询可能插入零个记录，这取决于它们的编写方式。在这些情况下，插入和删除的记录集将为空。在许多情况下，如果插入和删除的记录集都为空，您可能只想不做任何事情就退出触发器。

#10

This might be a faster way:

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

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'

#11

I found a small error in Grahams otherwise cool solution:

我在Grahams中发现了一个小错误，否则很酷的解决办法:

It should be IF COLUMNS_UPDATED() <> 0 -- insert or update
instead of > 0 probably because top bit gets interpreted as SIGNED integer sign bit...(?). So in total:

应该是columns_updates() <>——插入或更新，而不是> 0，可能是因为top位被解释为带符号位…(?)。所以总计:

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

#12

This does the trick for me:

这对我很有帮助:

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

Since not all columns can be updated at a time you can check whether a particular column is being updated by something like this:

因为不是所有的列都可以同时更新，所以您可以检查某个列是否由以下内容更新:

IF UPDATE([column_name])

#13

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

#14

I like solutions that are "computer science elegant." My solution here hits the [inserted] and [deleted] pseudotables once each to get their statuses and puts the result in a bit mapped variable. Then each possible combination of INSERT, UPDATE and DELETE can readily be tested throughout the trigger with efficient binary evaluations (except for the unlikely INSERT or DELETE combination).

我喜欢“计算机科学优雅”的解决方案。这里，我的解决方案在获取它们的状态时，会访问[插入]和[删除]伪表，并将结果放入一个位映射变量中。然后，每一种可能的插入、更新和删除组合都可以通过有效的二进制评估(除了不太可能的插入或删除组合)在整个触发器中进行测试。

It does make the assumption that it does not matter what the DML statement was if no rows were modified (which should satisfy the vast majority of cases). So while it is not as complete as Roman Pekar's solution, it is more efficient.

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

With this approach, we have the possibility of one "FOR INSERT, UPDATE, DELETE" trigger per table, giving us A) complete control over action order and b) one code implementation per multi-action-applicable action. (Obviously, every implementation model has its pros and cons; you will need to evaluate your systems individually for what really works best.)

使用这种方法，我们可以为每个表提供一个“插入、更新、删除”触发器，从而为我们提供A)对操作顺序的完全控制，b)对每个多操作适用的操作提供一个代码实现。(显然，每个实现模型都有其优缺点;你需要对你的系统进行单独的评估，以达到最有效的效果。

Note that the "exists (select * from «inserted/deleted»)" statements are very efficient since there is no disk access (https://social.msdn.microsoft.com/Forums/en-US/01744422-23fe-42f6-9ab0-a255cdf2904a).

注意，“exist (select * from«insert /deleted»)”语句非常有效，因为没有磁盘访问(https://social.msdn.microsoft.com/forums/en - us/01744422-fe-42f6 -9ab0-a255cdf2904a)。

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

#15

while i do also like the answer posted by @Alex, i offer this variation to @Graham's solution above

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

this exclusively uses record existence in the INSERTED and UPDATED tables, as opposed to using COLUMNS_UPDATED for the first test. It also provides the paranoid programmer relief knowing that the final case has been considered...

这只在插入和更新的表中使用记录存在，而不是在第一次测试中使用columns_updates。它还为偏执的程序员提供了一种解脱，因为他们知道已经考虑到了最终的情况……

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

you will get NOOP with a statement like the following :

您将得到NOOP，其语句如下:

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

#16

Quick solution MySQL

快速解决MySQL

By the way: I'm using MySQL PDO.

顺便说一下，我用的是MySQL PDO。

(1) In an auto increment table just get the highest value (my column name = id) from the incremented column once every script run first:

(1)在自动增量表中，每次脚本首先运行时，只需从递增列中获取最高值(我的列名= id):

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

(2) Run the MySQL query as you normaly would, and cast the result to integer, e.g.:

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

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

(3) After the "INSERT INTO ... ON DUPLICATE KEY UPDATE" query get the last inserted id your prefered way, e.g.:

(3)插入…在重复的密钥更新“查询获得最后插入的id您喜欢的方式，例如:

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

(4) Compare and react: If the lastInsertId is higher than the highest in the table, it's probably an INSERT, right? And vice versa.

(4)比较和反应:如果lastInsertId高于表中的最高值，可能是插入，对吗?反之亦然。

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

I know it's quick and maybe dirty. And it's an old post. But, hey, I was searching for a solution a for long time, and maybe somebody finds my way somewhat useful anyway. All the best!

我知道它很快而且可能很脏。这是一个老帖子。但是，嘿，我一直在寻找一个解决方案，也许有人会发现我的方法是有用的。所有最好的!

#17

just simple way

只是简单的方法

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 

#18

In first scenario I supposed that your table have IDENTITY column

在第一种场景中，我假设您的表有标识列

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

In second scenario don't need to use IDENTITTY column

在第二个场景中，不需要使用标识列

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

#19

DECLARE @INSERTEDCOUNT INT,
        @DELETEDCOUNT INT

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

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

IF its updation

如果它的升级

 @INSERTEDCOUNT = 1
 @DELETEDCOUNT = 1

if its insertion

如果它的插入

 @INSERTEDCOUNT = 1
 @DELETEDCOUNT = 0

#20

I've used those exists (select * from inserted/deleted) queries for a long time, but it's still not enough for empty CRUD operations (when there're no records in inserted and deleted tables). So after researching this topic a little bit I've found more precise solution:

我已经使用这些存在(从插入/删除中选择*)查询很长时间了，但是对于空CRUD操作(当插入和删除的表中没有记录时)仍然不够。所以在研究了这个话题之后，我找到了更精确的解决方案:

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'

It's also possible to use columns_updated() & power(2, column_id - 1) > 0 to see if the column is updated, but it's not safe for tables with big number of columns. I've used a bit complex way of calculating (see helpful article below).

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

Also, this approach will still incorrectly classifies some updates as inserts (if every column in the table is affected by update), and probably it will classifies inserts where there only default values are inserted as deletes, but those are king of rare operations (at lease in my system they are). Besides that, I don't know how to improve this solution at the moment.

此外，这种方法仍然会错误地将某些更新归类为插入(如果表中的每一列都受到更新的影响)，并且它可能会将只插入默认值作为删除的插入进行分类，但这些都是罕见操作的王(在我的系统中它们是)。除此之外，我目前还不知道如何改进这个解决方案。

• COLUMNS_UPDATED() for audit triggers by Piotr Rodak
• columns_updates()用作Piotr Rodak的审计触发器
• Dealing with very large bitmasks by Adam Machanic
• 亚当·马赫尼克的作品