Insert Update trigger how to determine if insert or update

user_17296 0
Viewed 587268

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.

triggers tsql sql-server
23 Answers

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

user_275350 
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;
user_58474430

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'
user_11804380

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.

user_13112630 
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'
user_119123520

i do this:

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

1 -> insert

2 -> delete

3 -> update

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