Dynamic SQL in a Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic SQL in a Trigger

Author

Topic

lofwyre
Starting Member

1 Post

Posted - 2010-03-19 : 01:30:52

Hi

I am trying to work out how I can dynamically specify what columns I write out in a trigger for auditing


CREATE TRIGGER ExampleTriggerTable ON ExampleTable
AFTER UPDATE
AS
BEGIN
	DECLARE @statement NVARCHAR(MAX)
	SET @statement = N'
		INSERT INTO ExampleTriggerTable (data)
		SELECT (FieldA)
		FROM inserted'
Exec(@statement)

END
GO

The above is a simple example and it gives the follwing error

"Invalid Object Name inserted" when ExampleTable is updated.

If I can get the dynamic SQL to work my plan is to use UPDATE_COLUMNS to get the specific columns for this table that have changed and then create an sql that concats the results for those columns.

My goal is to have an audit table like this:

ExampleTriggerTable
ID
UserID
ChangeDate
OriginalValues nvarchar(MAX)
NewValues nvarchar(MAX)

OrignalValues would end up containing:
Field1=blah,Field2=blah,Fiel4=Blah

and NewValues would contain
Field1=newblah,Field2=newblah,Fiel4=newBlah

and as an example Field3 and Field5 (of this pretend table) would not be recorded in the audit log because they didnt change.

Any help would be great.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-19 : 09:52:40

The reason why your dynamic sql doesn't work is because the inserted table is out of scope. And I have to say that this audit table would be very hard to make use of. It would be far better to just copy the entire old row to an audit table that has the same structure as the real table. I takes a little more storage but it's A LOT easier to work with. here's the code I use for my audit tables...absolutely free

CREATE TABLE [dbo].[MyTable] (
	[RegId] [bigint] NOT NULL,
	[RegTime] [datetime] NOT NULL,
	[ReferenceNo] [char](50) NOT NULL,
	[RecordType] [smallint] NOT NULL,
	[DataVersion] [smallint] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
	[ModifiedBy] [varchar](200) NOT NULL,
)
GO

CREATE TABLE [dbo].[MyTable_audit](
	[RegId] [bigint] NOT NULL,
	[RegTime] [datetime] NOT NULL,
	[ReferenceNo] [char](50) NOT NULL,
	[RecordType] [smallint] NOT NULL,
	[DataVersion] [smallint] NOT NULL,
	[ModifiedDate] [datetime] NULL,
	[ModifiedBy] [varchar](200) NULL,
	[IsDeleted] [tinyint] NULL,
)
GO

CREATE TRIGGER [dbo].[AuditDel] ON [dbo].[MyTable]
FOR DELETE
AS 

SET NOCOUNT ON

INSERT INTO MyTable_audit
  ([RegId],[RegTime],[ReferenceNo],[RecordType],[DataVersion],[ModifiedDate],[ModifiedBy],[IsDeleted])  
SELECT 
  del.[RegId],
  del.[RegTime],
  del.[ReferenceNo],
  del.[RecordType],
  del.[DataVersion],
  del.[ModifiedDate],
  del.[ModifiedBy],
  [IsDeleted] = 1
FROM deleted del
GO

CREATE TRIGGER [dbo].[AuditUpd] ON [dbo].[MyTable]
FOR UPDATE 
AS 

SET NOCOUNT ON

UPDATE reg SET 
  reg.DataVersion = ins.DataVersion + 1,
  reg.ModifiedDate = GETDATE(),
  reg.ModifiedBy = SYSTEM_USER
FROM MyTable reg
  INNER JOIN inserted ins
    ON reg.RegId = ins.RegId

INSERT INTO MyTable_audit
  ([RegId],[RegTime],[ReferenceNo],[RecordType],[DataVersion],[ModifiedDate],[ModifiedBy],[IsDeleted])  
SELECT 
  del.[RegId],
  del.[RegTime],
  del.[ReferenceNo],
  del.[RecordType],
  del.[DataVersion],
  del.[ModifiedDate],
  del.[ModifiedBy],
  [IsDeleted] = 0
FROM deleted del

GO

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources