Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
May 5, 2000

Generate Audit Table Trigger Code

This code will generate code to do a column-by-column comparison against a table, assuming that you are joining the inserted table (aliased as "i") with the deleted table (aliased as "d") in a trigger:
 SET NOCOUNT ON
DECLARE @x sysname

SELECT    @x = 'authors'

CREATE TABLE #x
(SQL varchar(255)
,    colid int )

INSERT    #x
select    'OR  IsNull( i.' + name + ', "12/31/9999" ) <> IsNull( d.' + name + ',
"12/31/9999" )'
     , colid
from syscolumns
where     id = object_id(@x)
and   status <> 0
AND  name <> 'timestamp'
AND  usertype = 12
union
select    'OR  IsNull( i.' + name + ', -9999) <> IsNull( d.' + name + ', -9999)'
     , colid
from syscolumns
where     id = object_id(@x)
and   status <> 0
AND  name <> 'timestamp'
and  usertype = 8
union
select    'OR  IsNull( i.' + name + ', "" ) <> IsNull( d.' + name + ', "" )'
     , colid
from syscolumns
where     id = object_id(@x)
and   status <> 0
AND  name <> 'timestamp'
and  scale IS NULL
AND  usertype NOT IN ( 12, 8 )
union
select    'OR  IsNull( i.' + name + ', 0) <> IsNull( d.' + name + ', 0)'
     , colid
from syscolumns
where     id = object_id(@x)
and   status <> 0
AND  name <> 'timestamp'
and  prec = 3
AND  scale = 0
AND  usertype NOT IN ( 12, 8 )
union
select    'OR  IsNull( i.' + name + ', -9999) <> IsNull( d.' + name + ', -9999)'
     , colid
from syscolumns
where     id = object_id(@x)
and   status <> 0
AND  name <> 'timestamp'
and  scale IS NOT NULL
and  prec <> 3
AND  usertype NOT IN ( 12, 8 )
union
select    'OR  i.' + name + ' <> d.' + name
     , colid
from syscolumns
where     id = object_id(@x)
and   status = 0
AND  name <> 'timestamp'
ORDER BY 2

SELECT    "    " + SQL
FROM #x

DROP TABLE #X
SET NOCOUNT OFF
GO
David Satz
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap