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.

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

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.

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

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'

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.

I found a small error in Grahams otherwise cool solution:

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:

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

Many of these suggestions do not take into account if you run a delete statement that deletes nothing.
<br />Say you try to delete where an ID equals some value that does not exist in the table.
<br />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.
<br />I built this from their scripts.

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

**Quick solution MySQL**

By the way: I'm using 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:

$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.:


$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.:

$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.

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!

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

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