Dynamically Build UPDATE statement within INSERT/UPDATE/DELETE trigger

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;

good audit trail scripts http://stackoverflow.com/questions/15941881/sql-server-auto-audit-updated-column — Thomas, Sep 08 '14 at 14:54

score 1 · Answer 1 · answered Sep 05 '14 at 14:25

1

Like many people, you misunderstand how a trigger works. When you insert, update or delete multiple records, the trigger is called once and the tables deleted/inserted can contain multiple records, not 1 for each record effected. You need to rewrite this assuming you have multiple records in those tables.

INSERT INTO [ContentChangeLog]([PkValue],[TableName],[Action],ActionDate)
SELECT ContentNumber, @TableName, 'I', GETDATE()
FROM INSERTED i
WHERE not exists(SELECT TOP 1 1 FROM DELETED WHERE ContentNumber = i.ContentNumber)

Here is an example of the Inserted records only, you will want to do something similar for your updates and deletes.

answered Sep 05 '14 at 14:25

Steve

5,585
2
18
32

no u misunderstand my requirement. as per my scenario only one record will be updated or inserted. i like to dynamically build insert & update statement from trigger. say i have 100 columns in table but only 2 columns updated then my trigger will fire. so from trigger i like to generate update statement like update mytable_name SET col1='value1', col2='value2' where id=10 something like this. if still not clear my requirement then please let me know. thanks – Thomas Sep 05 '14 at 14:55
Build your SQL in a varchar variable and then execute it with something like this: sp_executesql http://msdn.microsoft.com/en-us/library/ms188001.aspx – Steve Sep 05 '14 at 18:58

Dynamically Build UPDATE statement within INSERT/UPDATE/DELETE trigger

1 Answers1