Solved

SQL Server 2005 Triggers

Posted on 2009-05-13
13
212 Views
Last Modified: 2013-11-30
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

0
Comment
Question by:Paul_Foley
  • 6
  • 5
  • 2
13 Comments
 
LVL 3

Expert Comment

by:sateeshcv
ID: 24382371
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
 

Author Comment

by:Paul_Foley
ID: 24382521
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
 

Author Comment

by:Paul_Foley
ID: 24382530
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
 
LVL 3

Expert Comment

by:sateeshcv
ID: 24383107
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24384698
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
 

Author Comment

by:Paul_Foley
ID: 24390993
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:Paul_Foley
ID: 24391006
Increasing the points
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24391060
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
 

Accepted Solution

by:
Paul_Foley earned 0 total points
ID: 24391161
that would be fine if I knew which columns were text, but there are hundreds of tables so I need something dynamic
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24391632
Then I am afraid I have no idea.  Hopefully someone will step up to the plate.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24440614
>>no one seemed to be able to answer this question for me.<<
Perhaps because it cannot be done.
0
 

Author Comment

by:Paul_Foley
ID: 24446891
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24446923
>>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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now