locked
Dynamically Build UPDATE statement within INSERT/UPDATE/DELETE trigger RRS feed

  • Question

  • basically i am looking for logic to generate insert or update statement in string variable from trigger. suppose when people just update 'N' of fields like update statement....then my trigger will fire and from that trigger i want build what update statement was issued by user and store in string variable

    the same way i want to build insert statement from trigger too but i am not getting logic. so if anyone has any idea or sample code then please share with me to achieve my goal.

    ALTER TRIGGER WSContent AFTER INSERT, UPDATE, DELETE
    AS
    
    SET NOCOUNT ON;
    
    DECLARE @Action VARCHAR(10)
    DECLARE @PKValue INT
    DECLARE @TableName VARCHAR(50)
    
    SET @TableName='website_content'
    
    IF EXISTS(SELECT * FROM INSERTED)
    BEGIN
      IF EXISTS(SELECT * FROM DELETED)
      BEGIN
         SET @Action ='U';
         SELECT @PKValue=ContentNumber from DELETED
      END
      ELSE
      BEGIN
         SET @Action ='I';
         SELECT @PKValue=ContentNumber from INSERTED
      END
    END
    ELSE
    BEGIN
      SET @Action = 'D';
      SELECT @PKValue=ContentNumber from DELETED
    END;
    
    INSERT INTO [ContentChangeLog]([PkValue],[TableName],[Action],ActionDate)
    VALUES(@PKValue,@TableName,@Action,GetDate())
    
    SET NOCOUNT OFF;
    Friday, September 5, 2014 9:36 AM

Answers

  • INSERT ContentChangeLog(PkValue, TableName, Action, ActionDate)
       SELECT coalesce(i.ContentNumber, d.ContentNumber), 'website_content',
              CASE WHEN i.ContentNumber IS NOT NULL AND
                        d.ContentNumber IS NOT NULL THEN 'U'
                  WHEN i.ContentNumber IS NOT NULL AND
                       d.ContentNumber IS NULL THEN 'I'
                  WHEN i.ContentNumber IS NULL AND
                       d.ContentNumber IS NOT NULL THEN 'D'
              END, sysdatetime()
       FROM   inserted i
       FULL JOIN deleted ON i.ContentNumber = d.ContentNumber

    Note: you should never do things like:

         SELECT @PKValue=ContentNumber from DELETED

    because a trigger fires once per statement and the statement may affect zero, one or many rows.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, September 7, 2014 7:19 PM