Insert Update trigger how to determine if insert or update

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.

---

If it's 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 .

Look for "inserted" in CREATE TRIGGER

Edit, 23 Nov 2011

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

---

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

---

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.

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.

链接地址: http://www.djcxy.com/p/93914.html

上一篇: 如果Oracle分层表中的子数据为空，则获取父数据

下一篇: 插入更新触发器如何确定是否插入或更新