February 24, 2014 at 10:10 am
Hi,
I need to create triggers to audit some of my tables.
I want that all changes made to this tables that were NOT made by my application (.net app) are sent to audit tables in another database on the same SQL Instance server.
I don't want to use profiller because there is no one on the place where this will be installed and if the profiler stops or has any problem I will not be able to restart profiler again.
Does anyone have a generic script to audit some tables where I can say that I want to audit everything that was not made by my application?
Thank you
February 24, 2014 at 10:23 am
You can search for some scripts here, but you'd need a separate trigger for each table. The fields copied over would vary, so your trigger scripts need to account for that. Note that you'd have to assume that all changes were made with default settings on the connection. No matter what your application is, I can spoof the name in the connection, so this isn't 100% reliable.
Profiler is not how you'd do this. You'd use trace, and that can be run on the server, and you can use a job/process to check if it stops. It's more reliable, and lower overhead, but lots of data, and you'd have to write scripts to search the data for auditing.
There are some articles on auditing here as well that will help. There isn't a good, generic way to do this.
February 24, 2014 at 10:23 am
February 24, 2014 at 10:28 am
There is a person that made a script (generic script) to do this.
I have searched on the internet and did not found...
I saw the script some time ago but I didn´t save it to my pc...
February 24, 2014 at 10:34 am
river1 (2/24/2014)
Hi,I need to create triggers to audit some of my tables.
I want that all changes made to this tables that were NOT made by my application (.net app) are sent to audit tables in another database on the same SQL Instance server.
I don't want to use profiller because there is no one on the place where this will be installed and if the profiler stops or has any problem I will not be able to restart profiler again.
Does anyone have a generic script to audit some tables where I can say that I want to audit everything that was not made by my application?
Thank you
When you say that you want to audit "all changes made to this tables", are you talking about changes to the data or changes to the structure of the tables?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2014 at 10:40 am
river1 (2/24/2014)
There is a person that made a script (generic script) to do this.I have searched on the internet and did not found...
I saw the script some time ago but I didn´t save it to my pc...
To do this "generically" you would have to create an EAV type of auditing table. Meaning you would have a column for TableName, OldValue, NewValue, ChangeDate etc. This makes writing some generic trigger somewhat easy, of course you still have to have some sort of nasty RBAR to get the columns dynamically. Even if you manage to devise some sort of code that isn't slow as all get out you have auditing that is not really very useful. Consider how difficult it would be accurately output the entire row on a given date in the past. Add to that the inaccuracy of App_Name() and you have a cumbersome, slow and inaccurate auditing system.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 24, 2014 at 10:40 am
to the data (insert, update and delete). Only DML
February 24, 2014 at 11:03 am
river1 (2/24/2014)
to the data (insert, update and delete). Only DML
Excellent. Next question. Do you want 1 audit table as the target for all the tables or do you want a separate audit table for each table being audited?
Also, how many columns do these tables handle?
Shifting gears a bit and you have to trust me on this, in no way, shape, or form do you want what people refer to as "generic audit triggers". They make for massive performance problems. I went through converting all such generic triggers to separate non-dynamic triggers in out databases about a year ago. It was taking 4 minutes to update just 4 columns on 10,000 rows of one of the tables. After my changes, it took les than 800 milliseconds.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 24, 2014 at 11:11 am
the other question i'd like to see thrown into the mix is what do you REALLY need to track?
do you really need to know that Lowell modified these x rows, so you save the old values to be compared to the new, or do you really just need to find out that Lowell is updating your data behind your back,(Bobby Bouche)
if the answer is you need to know the specific data, then i'd say just turn on CDC on the tables in question, and then select from the changes for who changed what value.
if you just need whodunnit information,without the specific row information,then a server side trace or the modern replacement of extended events is the way to go. an extended event doesn't need to be re-initialized when the sql service restarts, but creating a procedure that re-creates your trace on startup of the server is easy to do as well, to keep your trace rolling correctly.
Lowell
February 24, 2014 at 8:34 pm
You post reminds me of a question I've been wanting to ask. If you have a (ugh!) wide table of, say, 137 columns and, after the initial insert of course, you only ever update 4 to 6 columns, is CDC still a wise decision... especially if you're not interested in the double-space penalty of auditing the initial insert?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 5, 2014 at 6:59 pm
Just to be sure, this wasn't a "wise guy" question that I posted above. I'd really like to know. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2014 at 7:17 am
the following may help you on your way
/*This provides code to create a trigger on any table to record changes in the data
the changes made are recorded in a single "Audit" table
Only columns that have been updated are recorded IF THE TRIGGER IS SET TO UPDATE ONLY
If the trigger is used for INSERT/DELETE then every column in the table will create a new row in the AUDIT table
YOU HAVE BEEN WARNED !!
Personally I only use this for auditing changes to static/master data tables (eg products/customers/suppliers etc)
and not for any transactional type tables.
Note that there will be an overhead.....but I will leave that for testing in your own environment
The original code was found on the web http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html.
*/
/*start in a safe place !!!!*/
use [tempdb]
GO
IF OBJECT_ID('tempdb..TransData', 'U') IS NOT NULL
DROP TABLE tempdb..TransData;
IF OBJECT_ID('tempdb..Audit', 'U') IS NOT NULL
DROP TABLE tempdb..Audit;
/* create the audit table.
There will only need to be one of these in a database
will be updated from any table with the trigger below*/
CREATE TABLE Audit (
Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
ColumnName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate DATETIME,
UserName VARCHAR(128),
AppName VARCHAR(128)
)
GO
/*create some test data*/
SELECT TOP 100000
TranID = IDENTITY(INT, 1, 1),
CustomerID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
Trans_type = CHAR(Abs(Checksum(Newid())) % 2 + 65)
+ CHAR(Abs(Checksum(Newid())) % 3 + 65)
+ CHAR(Abs(Checksum(Newid())) % 7 + 65),
ProdID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
Sales_Amount= CAST(Rand(Checksum(Newid())) * 99 + 1 AS DECIMAL(5, 2)),
Trans_Date = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2011', '2012'), '2011')
INTO TransData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
/*note that for this audit trigger to work there must be a primary key on the table*/
ALTER TABLE [dbo].[TransData] ADD CONSTRAINT [PK_TransData] PRIMARY KEY CLUSTERED ([TranID] ASC)
GO
/*create the trigger . This has to be created on every table you want to monitor
*/
CREATE TRIGGER [dbo].[transdata_Audit]
ON [dbo].[TransData]
FOR
--INSERT, ---uncomment if required
--DELETE, ---uncomment if required
UPDATE
AS
DECLARE @bit INT
, @field INT
, @maxfield INT
, @char INT
, @ColumnName VARCHAR(128)
, @TableName VARCHAR(128)
, @PKCols VARCHAR(1000)
, @sql VARCHAR(2000)
, @UpdateDate VARCHAR(21)
, @UserName VARCHAR(128)
, @Type CHAR(1)
, @PKSelect VARCHAR(1000)
, @PKField VARCHAR(1000)
, @AppName VARCHAR(128)
/*IMPORTANT You will need to change @TableName to match the table to be audited*/
SELECT @TableName = 'transdata'
/* date - user - application*/
SELECT @UserName = SYSTEM_USER
, @AppName = APP_NAME()
, @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
/* Action*/
IF EXISTS ( SELECT *
FROM inserted )
IF EXISTS ( SELECT *
FROM deleted )
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
/* get list of columns*/
SELECT *
INTO #ins
FROM inserted
SELECT *
INTO #del
FROM deleted
/* Get primary key columns for full outer join*/
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.'
+ c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
/* Get primary key columns ---jls*/
SELECT @PKField = COALESCE(@PKField + '+', '') + ''''
+ '''+convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))+'''''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR ( 'no PK on table %s', 16, - 1, @TableName )
RETURN
END
SELECT @field = 0
, @maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = ( @field - 1 ) % 8 + 1
SELECT @bit = POWER(2, @bit - 1)
SELECT @char = ( ( @field - 1 ) / 8 ) + 1
IF SUBSTRING(COLUMNS_UPDATED(), @char, 1) & @bit > 0
OR @Type IN ( 'I', 'D' )
BEGIN
SELECT @ColumnName = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
SELECT @sql = '
insert Audit (
Type,
TableName,
PK,
ColumnName,
OldValue,
NewValue,
UpdateDate,
UserName,
Appname)
select ''' + @Type + ''',''' + @TableName + ''',' + @PKField + ','''
+ @ColumnName + '''' + ',convert(varchar(1000),d.'
+ @ColumnName + ')' + ',convert(varchar(1000),i.'
+ @ColumnName + ')' + ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''',''' + @Appname + ''''
+ ' from #ins i full outer join #del d' + @PKCols
+ ' where i.' + @ColumnName + ' <> d.' + @ColumnName
+ ' or (i.' + @ColumnName + ' is null and d.'
+ @ColumnName + ' is not null)' + ' or (i.'
+ @ColumnName + ' is not null and d.' + @ColumnName
+ ' is null)'
EXEC ( @sql )
END
END
/*trigger end*/
GO
/*=======================================================*/
/* do some updates*/
/*=======================================================*/
UPDATE TransData
SET Trans_type = 'jls',
Sales_Amount = Sales_Amount * 1.2
WHERE (TranID % 1000 = 0)
SELECT * FROM Audit
order by tableName,cast(PK AS INT),ColumnName;
/*TRUNCATE TABLE Audit;*/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 6, 2014 at 9:53 am
J Livingston SQL (4/6/2014)
the following may help you on your way
/* create the audit table.
There will only need to be one of these in a database
will be updated from any table with the trigger below*/
CREATE TABLE Audit (
Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
ColumnName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate DATETIME,
UserName VARCHAR(128),
AppName VARCHAR(128)
)
GO
Just a couple of recommendations on this type of generic "field-level" trigger.
First, the PK, OldValue, and NewValue columns should have the datatype of SQL_VARIANT. That way, you don't have to do any conversions and there won't be any nasty non-sargable conversions if you ever need to join to the audit table. Using SQL_Variant_Property, you can also figure out what the source datatype was if you ever need to.
I also think that storing the NewValue is a waste of time and an unnecessary duplication of data because the new value will always be in the original table. The old value will record DELETEs if you allow DELETEs to be fully recorded (we don't do DELETEs at work, though).
You also need to remember that (up to 2014, anyway... don't know about that, yet) blob columns of any type cannot be audited by a trigger.
Also, because you have to make a copy of both the INSERTED and DELETED logical trigger tables, this could end up being quite the chore for SQL Server on wide tables. I went through this at work with a similar trigger written as a CLR. It took 4 minutes to update 4 columns for just 10,000 rows on a wide (I didn't design it) 137 column table.
Last but not least, I advise against a full audit of INSERTs (and possibly DELETEs depending on how things are handled) because it IS a field-level audit. Every row inserted could easily cause more than 7 times the storage requirements depeding on the datatypes involved. Imagine building a 1 gig table... it would create a 7 gig audit table and no modifications to the rows have been done yet. Imagine the demise if you created a 20 gig table, which is pretty small by today's standards.
The reason why you don't need to audit inserts is because the whole row exists in whatever table you did the original INSERT into. DELETEs are another story but they're easy for me at work. We're not allowed to delete anything. If you want to enforce that, you make a trigger that rejects the DELETEs.... could be in the audit trigger if you don't mind a rollback on such a rare occasion.
I also try to avoid such dynamic SQL in triggers (although it's much better there than in a CLR trigger). Instead, I wrote a stored procedure for work that takes a table name and builds the correct hard-coded trigger for the table along with the proprietary caveats the we have to enforce/capture at work (which is why I can't post the stored procedure until I clean out the proprietary stuff). If someone needs to modify the table, they also know that they need to rerun the stored procedure on that table to rebuild the trigger.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 6, 2014 at 10:56 am
Hi Jeff.....really appreciate your comments.
my thoughts and response to your post below:
Just a couple of recommendations on this type of generic "field-level" trigger.
First, the PK, OldValue, and NewValue columns should have the datatype of SQL_VARIANT. That way, you don't have to do any conversions and there won't be any nasty non-sargable conversions if you ever need to join to the audit table. Using SQL_Variant_Property, you can also figure out what the source datatype was if you ever need to.
>>JLS reply...seems goood advice...will read up some more
I also think that storing the NewValue is a waste of time and an unnecessary duplication of data because the new value will always be in the original table. The old value will record DELETEs if you allow DELETEs to be fully recorded (we don't do DELETEs at work, though).
>>>JLS reply...hmmm...me thinks that seeing old/new when reviewing an audit table provides a better humam understanding of what has happened.
particulary where a user has altered some data...made a transaction process...and then reverted to original data
think...changing a suppliers bank details prior to posting a payment and then revert back....
obviously this all depends on the security of the application ......???
You also need to remember that (up to 2014, anyway... don't know about that, yet) blob columns of any type cannot be audited by a trigger.
>>JLS reply....fair do's....blob columns have not been considered in this code.
Also, because you have to make a copy of both the INSERTED and DELETED logical trigger tables, this could end up being quite the chore for SQL Server on wide tables. I went through this at work with a similar trigger written as a CLR. It took 4 minutes to update 4 columns for just 10,000 rows on a wide (I didn't design it) 137 column table.
>>JLS reply.....havent tested this on such a wide table....maybe someone will <grin>
Last but not least, I advise against a full audit of INSERTs (and possibly DELETEs depending on how things are handled) because it IS a field-level audit. Every row inserted could easily cause more than 7 times the storage requirements depeding on the datatypes involved. Imagine building a 1 gig table... it would create a 7 gig audit table and no modifications to the rows have been done yet. Imagine the demise if you created a 20 gig table, which is pretty small by today's standards.
The reason why you don't need to audit inserts is because the whole row exists in whatever table you did the original INSERT into. DELETEs are another story but they're easy for me at work. We're not allowed to delete anything. If you want to enforce that, you make a trigger that rejects the DELETEs.... could be in the audit trigger if you don't mind a rollback on such a rare occasion.
>>JLS reply... as outlined in the code preamble...I only use this for master data/static tables...If doing mass insert/delete/update I would disable trigger first...but we have a very small shop and therefore could control.....hence the WARNING in the preamble.
I also try to avoid such dynamic SQL in triggers (although it's much better there than in a CLR trigger). Instead, I wrote a stored procedure for work that takes a table name and builds the correct hard-coded trigger for the table along with the proprietary caveats the we have to enforce/capture at work (which is why I can't post the stored procedure until I clean out the proprietary stuff). If someone needs to modify the table, they also know that they need to rerun the stored procedure on that table to rebuild the trigger.
>>Irrespective of pros/cons on this post, I would once again like to thank the forum posters for contributing to threads such as these, becasue there is no defintive answer and the ubiquitous "It depends" continues to rule.
So...to all who may come across this thread...please read all the replys/links before you decide upon a solution.
regards JLS
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 6, 2014 at 12:41 pm
/*possible proof of concept when applied to wide tables*/
/*response to Jeff's comments
"Also, because you have to make a copy of both the INSERTED and DELETED logical trigger tables,
this could end up being quite the chore for SQL Server on wide tables.
I went through this at work with a similar trigger written as a CLR.
It took 4 minutes to update 4 columns for just 10,000 rows on a wide (I didn't design it) 137 column table.
>>>JLS reply
same code with 120 wide column table...albeit majority are INTS
100 000 row table
6 column updates
please run code and review stats
*/
/*start in a safe place !!!!*/
use [tempdb]
GO
IF OBJECT_ID('tempdb..TransData', 'U') IS NOT NULL
DROP TABLE tempdb..TransData;
IF OBJECT_ID('tempdb..Audit', 'U') IS NOT NULL
DROP TABLE tempdb..Audit;
/* create the audit table.
There will only need to be one of these in a database
will be updated from any table with the trigger below*/
CREATE TABLE Audit (
Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
ColumnName VARCHAR(128),
OldValue VARCHAR(1000),
NewValue VARCHAR(1000),
UpdateDate DATETIME,
UserName VARCHAR(128),
AppName VARCHAR(128)
)
GO
/*create some test data*/
SELECT TOP 100000
TranID = IDENTITY(INT, 1, 1),
CustomerID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
Trans_type = CHAR(Abs(Checksum(Newid())) % 2 + 65)
+ CHAR(Abs(Checksum(Newid())) % 3 + 65)
+ CHAR(Abs(Checksum(Newid())) % 7 + 65),
ProdID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol1 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol2 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol3 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol4 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol5 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol6 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol7 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol8 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol9 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol10 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol11 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol12 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol13 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol14 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol15 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol16 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol17 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol18 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol19 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol20 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol21 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol22 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol23 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol24 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol25 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol26 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol27 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol28 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol29 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol30 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol31 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol32 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol33 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol34 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol35 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol36 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol37 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol38 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol39 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol40 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol41 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol42 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol43 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol44 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol45 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol46 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol47 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol48 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol49 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol50 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol51 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol52 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol53 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol54 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol55 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol56 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol57 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol58 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol59 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol60 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol61 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol62 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol63 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol64 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol65 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol67 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol68 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol69 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol70 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol71 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol72 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol73 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol74 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol75 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol76 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol77 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol78 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol79 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol80 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol81 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol82 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol83 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol84 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol85 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol86 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol87 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol88 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol89 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol90 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol91 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol92 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol93 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol94 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol95 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol96 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol97 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol98 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol99 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol100 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol101 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol102 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol103 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol104 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol105 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol106 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol107 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol108 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol109 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol110 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol111 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol112 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol113 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol114 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol115 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol116 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol117 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol118 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol119 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
WideCol120 = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),
Sales_Amount= CAST(Rand(Checksum(Newid())) * 99 + 1 AS DECIMAL(5, 2)),
Trans_Date = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2011', '2012'), '2011')
INTO TransData
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
/*note that for this audit trigger to work there must be a primary key on the table*/
ALTER TABLE [dbo].[TransData] ADD CONSTRAINT [PK_TransData] PRIMARY KEY CLUSTERED ([TranID] ASC)
GO
/*create the trigger . This has to be created on every table you want to monitor
*/
CREATE TRIGGER [dbo].[transdata_Audit]
ON [dbo].[TransData]
FOR
--INSERT, ---uncomment if required
--DELETE, ---uncomment if required
UPDATE
AS
DECLARE @bit INT
, @field INT
, @maxfield INT
, @char INT
, @ColumnName VARCHAR(128)
, @TableName VARCHAR(128)
, @PKCols VARCHAR(1000)
, @sql VARCHAR(2000)
, @UpdateDate VARCHAR(21)
, @UserName VARCHAR(128)
, @Type CHAR(1)
, @PKSelect VARCHAR(1000)
, @PKField VARCHAR(1000)
, @AppName VARCHAR(128)
/*IMPORTANT You will need to change @TableName to match the table to be audited*/
SELECT @TableName = 'transdata'
/* date - user - application*/
SELECT @UserName = SYSTEM_USER
, @AppName = APP_NAME()
, @UpdateDate = CONVERT(VARCHAR(8), GETDATE(), 112) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114)
/* Action*/
IF EXISTS ( SELECT *
FROM inserted )
IF EXISTS ( SELECT *
FROM deleted )
SELECT @Type = 'U'
ELSE
SELECT @Type = 'I'
ELSE
SELECT @Type = 'D'
/* get list of columns*/
SELECT *
INTO #ins
FROM inserted
SELECT *
INTO #del
FROM deleted
/* Get primary key columns for full outer join*/
SELECT @PKCols = COALESCE(@PKCols + ' and', ' on') + ' i.'
+ c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
/* Get primary key columns ---jls*/
SELECT @PKField = COALESCE(@PKField + '+', '') + ''''
+ '''+convert(varchar(100), coalesce(i.' + COLUMN_NAME + ',d.' + COLUMN_NAME + '))+'''''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @TableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
IF @PKCols IS NULL
BEGIN
RAISERROR ( 'no PK on table %s', 16, - 1, @TableName )
RETURN
END
SELECT @field = 0
, @maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
WHILE @field < @maxfield
BEGIN
SELECT @field = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION > @field
SELECT @bit = ( @field - 1 ) % 8 + 1
SELECT @bit = POWER(2, @bit - 1)
SELECT @char = ( ( @field - 1 ) / 8 ) + 1
IF SUBSTRING(COLUMNS_UPDATED(), @char, 1) & @bit > 0
OR @Type IN ( 'I', 'D' )
BEGIN
SELECT @ColumnName = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
SELECT @sql = '
insert Audit (
Type,
TableName,
PK,
ColumnName,
OldValue,
NewValue,
UpdateDate,
UserName,
Appname)
select ''' + @Type + ''',''' + @TableName + ''',' + @PKField + ','''
+ @ColumnName + '''' + ',convert(varchar(1000),d.'
+ @ColumnName + ')' + ',convert(varchar(1000),i.'
+ @ColumnName + ')' + ',''' + @UpdateDate + ''''
+ ',''' + @UserName + ''',''' + @Appname + ''''
+ ' from #ins i full outer join #del d' + @PKCols
+ ' where i.' + @ColumnName + ' <> d.' + @ColumnName
+ ' or (i.' + @ColumnName + ' is null and d.'
+ @ColumnName + ' is not null)' + ' or (i.'
+ @ColumnName + ' is not null and d.' + @ColumnName
+ ' is null)'
EXEC ( @sql )
END
END
/*trigger end*/
GO
/*=======================================================*/
/* do some updates*/
/*=======================================================*/
UPDATE TransData
SET Trans_type = 'jls',
WideCol118 = 100,
WideCol45 = 32,
WideCol13 = 1800,
WideCol18 = 258888,
WideCol49 = 27
WHERE (TranID % 1000 = 0)
SELECT * FROM Audit
order by tableName,cast(PK AS INT),ColumnName;
/*TRUNCATE TABLE Audit;*/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply