Insert Update trigger how to determine if insert or update

Question

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

User · Answer

i do this  select isnull  select top 1 1 from inserted t1  0    isnull  select top 1 2 from deleted t1  0   1 - gt  insert 2 - gt  delete 3 - gt  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 · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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

User · Answer

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   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  gt  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    amp  c bit mask  gt  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  gt  0     select  operation    U  else     select  operation    D    It s also possible to use columns updated    amp  power 2  column id - 1   gt  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    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 Dealing with very large bitmasks by Adam Machanic

User · Answer

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

User · Answer

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 · Answer

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

User · Answer

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  gt  0     BEGIN         SET  Action    D  -- Set Action to  D eleted          SELECT  Count   COUNT    FROM INSERTED         IF  Count  gt  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

User · Answer

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   gt  COUNT d Id  THEN  inserted                        WHEN COUNT i Id   lt  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  gt   delCount THEN  inserted                        WHEN  insCount  lt   delCount THEN  deleted  ELSE  updated  END

User · Answer

declare  insCount int declare  delCount int declare  action char 1   select  insCount   count    from INSERTED select  delCount   count    from DELETED      if  insCount  gt  0 or  delCount  gt  0 --if something was actually affected  otherwise do nothing     Begin         if  insCount    delCount              set  action    U --is update         else if  insCount  gt  0              set  action    I  --is insert         else             set  action    D  --is delete          --do stuff here     End

User · Answer

while i do also like the answer posted by  Alex  i offer this variation to  Graham s solution above  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      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       update tbl1 set col1  cat  where 1 2

User · Answer

Triggers have special INSERTED and DELETED tables to track  quot before quot  and  quot after quot  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  quot inserted quot  in CREATE TRIGGER  Edit  23 Nov 2011 After comment  this answer is only for INSERTED and UPDATED triggers  Obviously  DELETE triggers can not have  quot always rows in INSERTED quot  as I said above

User · Answer

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

User · Answer

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   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    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    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  lt - Binary bit values -- 00 - gt  No Rows Modified -- 01 - gt  INSERT -- INSERT and UPDATE have the 1 bit set -- 11 - gt  UPDATE  lt  -- 10 - gt  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  amp  1   1  raiserror N For INSERT or UPDATE    10  1  with nowait   -- do things for UPDATE or DELETE if   Action  amp  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  lt  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  lt  gt  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  lt  0   raiserror N  DELETE 3      10  1  with nowait  delete t from dbo TrigAction t  GO  drop table dbo TrigAction   GO

User · Answer

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 - gt maxid     3  After the  INSERT INTO     ON DUPLICATE KEY UPDATE  query get the last inserted id your prefered way  e g     iLastInsertId    int   db- gt 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  gt   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

User · Answer

I found a small error in Grahams otherwise cool solution   It should be  IF COLUMNS UPDATED    lt   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    lt  gt  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

User · Answer

I believe nested ifs a little confusing and      Flat is better than nested  The Zen of 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

User · Answer

This might be a faster way   DECLARE  action char 1   IF COLUMNS UPDATED    gt  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 · Answer

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  amp   update    update   BEGIN   print  update    SET  result 0 END if   result  amp   delete    delete    print  delete  if   result  amp   insert    insert    print  insert

[sql-server] Insert Update trigger how to determine if insert or update

Examples related to sql-server

Examples related to tsql

Examples related to triggers