June 28, 2010 at 5:59 pm
Hi All,
write a trigger which keeps a track of history of a table.
My Boss needs a single trigger that works for all 52 tables.
and the trigger should get the table names and the column names from sysobject and or other system tables.Please it is urgent
Thanks
June 28, 2010 at 11:37 pm
I would strongly recommend that you do not go that route, it'll lead to horrendously complex triggers, usually needing dynamic SQL, often having difficulty with the inserted and deleted tables (they aren't available inside dynamic SQL or inside procs called from triggers)
What I do suggest is that you write a stored procedure that, when run, generates triggers on all the tables that need one. That stored proc can query the system tables (sysobjects, syscolumns) to figure out what trigger to create. It's still not simple, but it's way simpler than a generic trigger.
You say 'keep a track'. How? Where should the history be written and in what format? Do you have an audit table per table being tracked? Are you storing audit history in XML? If not, how do you store history of tables with different structures in one table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2010 at 2:43 am
I have had to do similar in the past in order to figure out what a third party application was doing in various business processes....
I cannot share the code with you but here is what I did:
1. Create Stored Procedures To Perform all these tasks:
a) Create An INSERT trigger that writes to a central log table - the name of the table that was inserted into, the user, the date, the time, the application, the host etc etc. This table had a unique key that I then used as a reference on another table that held a copy of the inserted data - Yes I was duplicating the data for this purpose as I needed to....you would not want to do this on a Production database. The stored proc created a unique trigger tailored to each table by analysing column and index information - using dynamic sql to generate the trigger code and apply it.
b) Create A DELETE trigger the does the same job as a) but for DELETES
c) Create an Update trigger that performed similar operations to a) and b) for each update, storing the before and after rows from deleted and inserted - YES more duplication of data - NOT FOR PRODUCTION USE
d) Create a reporting query that displayed Insert/Update/Delete information in date/time order (important information here to avoid deadlocks in bespoke code) for each process. As part of this query, I only displayed those columns that had changed on an UPDATE, but all data on the row for INSERTS and DELETES.
All these processes accepted parameters to apply/remove triggers to single tables/ranges of tables/all tables and the procedures are now used by many of my colleagues when they want to do a quick audit on any process using SQL server.
However, they took a long time to get working safely (i.e. no errors caused by triggers) and quickly (They are not for production but have been used by various support desk people to troubleshoot live systems without causing problems on medium to low usage systems) and in the long run it might be a good time to convince your Boss of the benefits of an upgrade to SQL2008... 😀
If you need to ask any questions while writing your own - if you cannot avoid it - feel free to do that here and I can at least pull out snippets from the code to help...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 29, 2010 at 5:48 am
Thsnks guys for your quick reply.
I agree we should create stored proc.and here is the code for that.
would you guys make it work for sql server 2000. it errored out on sql 2000. and am not famliar with system tables in 2000
DECLARE @TABLENAME varchar(100)
DECLARE @SCHEMA varchar(100)
SET @TABLENAME = N'yourTableName'
SET @SCHEMA = N'dbo'
DECLARE @Done bit
SET @Done=0
DECLARE @CRLF char(2)
SET @CRLF = char(10)
DECLARE @sql varchar(1000)
SET @sql = '
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''['+@SCHEMA+'].
['+@TABLENAME+'_Audit]'') AND type in (N''U''))
DROP TABLE [dbo].['+@TABLENAME+'_Audit]
CREATE TABLE ['+@SCHEMA+'].['+@TABLENAME+'_Audit] ('+@CRLF
DECLARE @COLUMNID int
SET @COLUMNID = 0
DECLARE @COLUMNNAME varchar(1000)
DECLARE @COLUMNTYPE varchar(100)
DECLARE @COLUMNSIZE int
WHILE @Done=0
BEGIN
SELECT top 1
@COLUMNID=clmns.column_id,
@COLUMNNAME=clmns.name ,
@COLUMNTYPE=usrt.name ,
@COLUMNSIZE=CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND
clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int)
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and
baset.user_type_id = baset.system_type_id
WHERE
(tbl.name=@TABLENAME and SCHEMA_NAME(tbl.schema_id)=@SCHEMA)
and clmns.column_id > @COLUMNID
ORDER BY
clmns.column_id asc
IF @@rowcount=0
begin
SET @Done=1
end
else
begin
SET @sql=@SQL+'['+@COLUMNNAME+'] ['+@COLUMNTYPE+'] '
IF (@COLUMNTYPE='varchar') SET @sql=@SQL+'('+ltrim(str(@COLUMNSIZE))
+') '
SET @sql=@SQL+'NULL, '+@CRLF
end
END
SET @sql=@SQL+'[timestamp] datetime )'
print @sql
exec (@SQL)
SET @sql='IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].
['+@TABLENAME+'_Trigger]''))
DROP TRIGGER [dbo].['+@TABLENAME+'_Trigger]'
print @sql
exec(@SQL)
SET @sql='CREATE TRIGGER ['+@TABLENAME+'_Trigger]
ON ['+@SCHEMA+'].['+@TABLENAME+']
AFTER UPDATE, INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO '+@TABLENAME+'_Audit
SELECT *,getdate() FROM inserted
END'
print @sql
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
exec (@SQL)
June 29, 2010 at 6:02 am
sys.objects -> sysobjects
sys.columns -> syscolumns
sys.types -> systypes
sys.tables -> sysobjects where xtype = 'u'
sys.triggers -> sysobjects where xtype = 'tr'
The column names have changed as well, but you should be able to figure them out, most are intuitive, for the rest check Books Online (compare the 2000 and 2005 ones)
Do note that with the way this is written, you must remember, if you make changes to the source tables, to make the same change to the audit table, or the triggers will fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2010 at 10:05 pm
Hi all,
Have changed the code to work for sql 2000
But it only creates the audit table for the first column, and it doesnt loop to other columns.
Have spent more time on it , but couldnt figure out.......
Any Ideas?
DECLARE @TABLENAME varchar(100)
DECLARE @SCHEMA varchar(100)
SET @TABLENAME = N'Customer'
SET @SCHEMA = N'dbo'
DECLARE @Done bit
SET @Done=0
DECLARE @CRLF char(2)
SET @CRLF = char(10)
DECLARE @sql varchar(1000)
SET @sql='IF EXISTS (select name from sysobjects where xtype =''U''
and name like ''%'+@TABLENAME+'_Audit'')
DROP Table [dbo].['+@TABLENAME+'_Audit]
CREATE TABLE ['+@SCHEMA+'].['+@TABLENAME+'_Audit] ('+@CRLF
DECLARE @COLUMNID int
SET @COLUMNID = 0
DECLARE @COLUMNNAME varchar(1000)
DECLARE @COLUMNTYPE varchar(100)
DECLARE @COLUMNSIZE int
WHILE @Done=0
BEGIN
SELECT top 1
@COLUMNID=clmns.id,
@COLUMNNAME=clmns.name ,
@COLUMNTYPE=usrt.name ,
@COLUMNSIZE=CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND
clmns.length <> -1 THEN clmns.length/2 ELSE clmns.length END AS int)
FROM
(select * from sysobjects where xtype='U') AS tbl
INNER JOIN syscolumns AS clmns ON clmns.id=tbl.id
LEFT OUTER JOIN systypes AS usrt ON usrt.xusertype = clmns.xusertype
LEFT OUTER JOIN systypes AS baset ON baset.xusertype = clmns.xtype and
baset.xusertype = baset.xtype
WHERE
(tbl.name=@TABLENAME )
and clmns.colid > @COLUMNID
ORDER BY
clmns.colid asc
IF @@rowcount=0
begin
SET @Done=1
end
else
begin
SET @sql=@SQL+'['+@COLUMNNAME+'] ['+@COLUMNTYPE+'] '
IF (@COLUMNTYPE='varchar') SET @sql=@SQL+'('+ltrim(str(@COLUMNSIZE))
+') '
SET @sql=@SQL+'NULL, '+@CRLF
end
END
SET @sql=@SQL+'[timestamp] datetime )'
print @sql
exec (@SQL)
-------the following is just to show that I got the data for that table---------------------
/*SELECT clmns.colid,clmns.id,
clmns.name ,
usrt.name ,
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND
clmns.length <> -1 THEN clmns.length/2 ELSE clmns.length END AS int)
FROM
(select * from sysobjects where xtype='U') AS tbl
INNER JOIN syscolumns AS clmns ON clmns.id=tbl.id
LEFT OUTER JOIN systypes AS usrt ON usrt.xusertype = clmns.xusertype
LEFT OUTER JOIN systypes AS baset ON baset.xusertype = clmns.xtype and
baset.xusertype = baset.xtype
WHERE
tbl.name='Customer'
and clmns.colid > 0
ORDER BY
clmns.colid asc --select * from systypes*/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply