SQL Server 2005 Triggers

Hi, I'm creating a trigger to be deployed on a couple of hundred tables to audit insert, Update and delete. You cannot have after if some fields are text, so how can I convert to varchar on the select? See the statement below. I just need the convert part for the statement.

Thanks
Create TRIGGER [dbo].[tri_pfAudit_@tblName_INSERT] ON [dbo].[@tblName]
AFTER INSERT
AS
   
    Declare @x as XML
    --In select I need to check if text, and convert to varchar
    Select @x = (Select * from Inserted FOR XML AUTO, BINARY BASE64 , ELEMENTS, Root(''ROOT''))
     
    Insert Table_Audit(TableName,txtXML,Action) Select ''@tblName'',@x,''I''

Open in new window

Paul_FoleyAsked:
Who is Participating?
 
Paul_FoleyAuthor Commented:
that would be fine if I knew which columns were text, but there are hundreds of tables so I need something dynamic
0
 
sateeshcvCommented:
Hi,
Remove the double quotes for @tblName.  Just put single quote. Same thing applies to Action Column also.
Insert Table_Audit(TableName,txtXML,Action) Select 'tblName',@x,'I'
0
 
Paul_FoleyAuthor Commented:
Hi, That is not the problem. its because this statement is dynamically created inside another statement. I should have made that clear, sorry. The actual error I am getting is

Procedure USPU_Generate_Audit_Trigger, Line 65
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

which is why in the select I need to check for text and convert it to varchar, but there are hundreds of tables so not possible to know each text column manually, or each procedure. So i am looking for a way around this. Because I have text fields in a few hundred tables as well.

Thanks. the full code is.
CREATE PROCEDURE [dbo].[USPU_Generate_Audit_Trigger]
    @tblName varchar(100) =''                                    --Table name to add triggers to
AS
BEGIN
 
Set NOCOUNT ON
 
Declare @s0 varchar(4000)                --to drop triggers if exists 
Declare @s1 varchar(4000)                --Create Insert Trigger
Declare @s2 varchar(4000)                --Update Trigger
Declare @s3 varchar(4000)                --Delete Trigger
Declare @s varchar(4000)
 
 
Select @s0 ='
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[tri_pfAudit_@tblName_INSERT]''))
DROP TRIGGER [dbo].[tri_pfAudit_@tblName_INSERT]
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[tri_pfAudit_@tblName_UPDATE]''))
DROP TRIGGER [dbo].[tri_pfAudit_@tblName_UPDATE]
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[tri_pfAudit_@tblName_DELETE]''))
DROP TRIGGER [dbo].[tri_pfAudit_@tblName_DELETE]
'
 
Select @s1 = '
Create TRIGGER [dbo].[tri_pfAudit_@tblName_INSERT] ON [dbo].[@tblName]
For INSERT
AS
   
    Declare @x as XML
    Select @x = (Select * from Inserted FOR XML AUTO, BINARY BASE64 , ELEMENTS, Root(''ROOT''))
     
    Insert Table_Audit(TableName,txtXML,Action) Select ''@tblName'',@x,''I''
'
 
Select @s2 = '
Create TRIGGER [dbo].[tri_pfAudit_@tblName_UPDATE] ON [dbo].[@tblName]
For Update
AS
   
    Declare @x as XML
    Select @x = (Select * from Inserted FOR XML AUTO, BINARY BASE64 , ELEMENTS, Root(''ROOT''))
    Insert Table_Audit(TableName,txtXML,Action) Select ''@tblName'',@x,''U''
 
'
 
Select @s3 = '
Create TRIGGER [dbo].[tri_pfAudit_@tblName_DELETE] ON [dbo].[@tblName]
For DELETE
AS
    Declare @x as XML
    Select @x = (Select * from Deleted FOR XML AUTO, BINARY BASE64 , ELEMENTS, Root(''ROOT''))
    Insert Table_Audit(TableName,txtXML,Action) Select ''@tblName'',@x,''D''
 
'
 
    Select @s= Replace(@s0,'@tblName',@tblName)
    Execute (@s)
    Select @s= Replace(@s1,'@tblName',@tblName)
    Execute (@s)
    Select @s= Replace(@s2,'@tblName',@tblName)
    Execute (@s)
    Select @s = Replace(@s3,'@tblName',@tblName)
    Execute (@s)
    Return 0
 
 
END

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Paul_FoleyAuthor Commented:
An also, I get the above error when applying the trigger with the following code.
BEGIN TRAN
 
DECLARE @name varchar(100)
DECLARE db_cursor CURSOR FOR  
 
SELECT [name] 
FROM sys.tables
 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   
 
WHILE @@FETCH_STATUS = 0   
BEGIN   
    EXEC dbo.USPU_Generate_Audit_Trigger @name
    PRINT 'Added Trigger For Table: ' + @name
	  FETCH NEXT FROM db_cursor INTO @name    
END   
 
CLOSE db_cursor   
DEALLOCATE db_cursor 
 
IF @@ERROR = 0 
	COMMIT TRAN
ELSE 
	ROLLBACK TRAN

Open in new window

0
 
sateeshcvCommented:
Hi,
Try to execute the below procedure and let me know:

CREATE PROCEDURE [dbo].[USPU_Generate_Audit_Trigger]
    @tblName varchar(100) =''                                    --Table name to add triggers to
AS
BEGIN
 
Set NOCOUNT ON
 

Declare @s2 varchar(4000)                --Update Trigger
Declare @s3 varchar(4000)                --Delete Trigger
Declare @s varchar(4000)

Declare @s0 varchar(4000)                --to drop triggers if exists
Declare @s1 varchar(4000)                --Create Insert Trigger
--Declare @tblName Varchar(100)
--set @tblName='abc'
 
Select @s0 ='
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[tri_pfAudit_'+@tblName+'_INSERT]''))
DROP TRIGGER [dbo].[tri_pfAudit_'+@tblName+'_INSERT]
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[tri_pfAudit_'+@tblName+'_UPDATE]''))
DROP TRIGGER [dbo].[tri_pfAudit_'+@tblName+'_UPDATE]
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[tri_pfAudit_'+@tblName+'_DELETE]''))
DROP TRIGGER [dbo].[tri_pfAudit_'+@tblName+'_DELETE]
'
exec (@s0)
Select @s1 = '
Create TRIGGER [dbo].[tri_pfAudit_'+@tblName+'_INSERT] ON [dbo].['+@tblName+']
For INSERT
AS
   
    Declare @x as XML
    Select @x = (Select * from Inserted FOR XML AUTO, BINARY BASE64 , ELEMENTS, Root(''ROOT''))
     
    Insert Table_Audit(TableName,txtXML,Action) Select '''+@tblName+''',@x,''I''
'
exec (@s1)

Select @s2 = '
Create TRIGGER [dbo].[tri_pfAudit_'+@tblName+'_UPDATE] ON [dbo].['+@tblName+']
For Update
AS
   
    Declare @x as XML
    Select @x = (Select * from Inserted FOR XML AUTO, BINARY BASE64 , ELEMENTS, Root(''ROOT''))
    Insert Table_Audit(TableName,txtXML,Action) Select '''+@tblName+''',@x,''U''
 
'
exec (@s2)
 
Select @s3 = '
Create TRIGGER [dbo].[tri_pfAudit_'+@tblName+'_DELETE] ON [dbo].['+@tblName+']
For DELETE
AS
    Declare @x as XML
    Select @x = (Select * from Deleted FOR XML AUTO, BINARY BASE64 , ELEMENTS, Root(''ROOT''))
    Insert Table_Audit(TableName,txtXML,Action) Select '''+@tblName+''',@x,''D''
 
'
exec (@s3)
end


0
 
Anthony PerkinsCommented:
Since you appear to be using SQL Server 2005, why not convert the columns with the deprecated text data type to vachar(MAX).  That would be the simplest alternatinve in my view.

Also, make sure the Compatibility is set to 90
0
 
Paul_FoleyAuthor Commented:
I cannot do that as the tables are used by a third party application, I'd have to convert during a select on the trigger or by some other manner. I am looking for a solution that dosen't involve any changes to the structure of the tables.
0
 
Paul_FoleyAuthor Commented:
Increasing the points
0
 
Anthony PerkinsCommented:
Since you are using text data types and need that data, then the best you can hope to achieve is using the Inserted/Deleted table to query against the table to retrieve the data that way.

So for example, instead of this:
Select * from Inserted FOR XML AUTO, BINARY BASE64 , ELEMENTS, Root(''ROOT'')

You would use something like this:
Select t.Col1, t.Col2, t.Col3, ..., CAST(t.TextCol as varchar(MAX) TextCol, ... Coln from Inserted i Inner Join TableName t On i.<PrimaryKey> = t.<PrimaryKey> FOR XML AUTO, BINARY BASE64 , ELEMENTS, Root(''ROOT''))
0
 
Anthony PerkinsCommented:
Then I am afraid I have no idea.  Hopefully someone will step up to the plate.
0
 
Anthony PerkinsCommented:
>>no one seemed to be able to answer this question for me.<<
Perhaps because it cannot be done.
0
 
Paul_FoleyAuthor Commented:
Perhaps it cannot, although that is a serious limitation of SQL server in my opinion. I often use audit trailing on larger applications to inspect code changes, and exclude knock-on effects.
0
 
Anthony PerkinsCommented:
>>although that is a serious limitation of SQL server in my opinion.<<
You have every right to your opinion.  However, most shops either do not allow any type of Dynamic SQL or frown upon it, in view of the serious risks involved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.