May 8, 2016 at 7:36 am
J Livingston SQL (5/8/2016)
Rod did you run the test script I posted?1 Million row table
-- update 6 columns on 100 000 rows
--run 10 iterations of update with trigger on
--then disable trigger and repeat
No sorry, I didn't. I'll try doing that when I get back to work on Wednesday. (I'm taking Monday and Tuesday off.)
Kindest Regards, Rod Connect with me on LinkedIn.
May 8, 2016 at 8:19 am
J Livingston SQL (5/6/2016)
R.P.Rozema (5/6/2016)
mock up some example table and generate a significant volume of test data into itsome test data......and yeah the generic audit is slow.
It's only slow because you're doing it wrong.
You should not collect the list of columns in the table, it's PK definition, etc. every time you fire the trigger. They will be the same as last time.
You should not use dymanic SQL inside of CREATE TRIGGER statement.
Instead - you should use dynamic SQL to generate CREATE TRIGGER statement.
And you need to query system views only when the table schema is changed.
_____________
Code for TallyGenerator
May 8, 2016 at 8:26 am
Sergiy (5/8/2016)
J Livingston SQL (5/6/2016)
R.P.Rozema (5/6/2016)
mock up some example table and generate a significant volume of test data into itsome test data......and yeah the generic audit is slow.
It's only slow because you're doing it wrong.
You should not collect the list of columns in the table, it's PK definition, etc. every time you fire the trigger. They will be the same as last time.
You should not use dymanic SQL inside of CREATE TRIGGER statement.
Instead - you should use dynamic SQL to generate CREATE TRIGGER statement.
And you need to query system views only when the table schema is changed.
ok.
The original code was found on the web http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html
can you please provide sample to code to explain what you mean?
Many thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 8, 2016 at 2:49 pm
J Livingston SQL (5/8/2016)
Sergiy (5/8/2016)
J Livingston SQL (5/6/2016)
R.P.Rozema (5/6/2016)
mock up some example table and generate a significant volume of test data into itsome test data......and yeah the generic audit is slow.
It's only slow because you're doing it wrong.
You should not collect the list of columns in the table, it's PK definition, etc. every time you fire the trigger. They will be the same as last time.
You should not use dymanic SQL inside of CREATE TRIGGER statement.
Instead - you should use dynamic SQL to generate CREATE TRIGGER statement.
And you need to query system views only when the table schema is changed.
ok.
The original code was found on the web http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html
can you please provide sample to code to explain what you mean?
Many thanks
He's talking about the same thing I am... use code to generate a hard coded trigger for the table. It's not like the table changes every second.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 8, 2016 at 3:01 pm
Jeff Moden (5/8/2016)
J Livingston SQL (5/8/2016)
Sergiy (5/8/2016)
J Livingston SQL (5/6/2016)
R.P.Rozema (5/6/2016)
mock up some example table and generate a significant volume of test data into itsome test data......and yeah the generic audit is slow.
It's only slow because you're doing it wrong.
You should not collect the list of columns in the table, it's PK definition, etc. every time you fire the trigger. They will be the same as last time.
You should not use dymanic SQL inside of CREATE TRIGGER statement.
Instead - you should use dynamic SQL to generate CREATE TRIGGER statement.
And you need to query system views only when the table schema is changed.
ok.
The original code was found on the web http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html
can you please provide sample to code to explain what you mean?
Many thanks
He's talking about the same thing I am... use code to generate a hard coded trigger for the table. It's not like the table changes every second.
I am not defending a generic table audit...the test code I supplied proves its inefficany.
...I would just like to see some sample code that points me in the right direction please.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 8, 2016 at 3:03 pm
J Livingston SQL (5/6/2016)
some test data......and yeah the generic audit is slow.Personally I only use this for Updates and only on relatively small tables with few changes....tables for customers/products etc.
Just thought I'd say "thanks" for the test data and the trigger example. Compared to the SQLCLR trigger that folks were using at work, that's comparatively fast. For what it does, I agree... it's still comparatively slow.
Like Sergiy says and I previously said, it's better to create code that will create a hard-coded trigger. That way, you're not always having to do a copy of the trigger logical tables nor having to recalculate that which has not changed.
Shifting gears, the trigger generation code I wrote for work is way too esoteric for demonstration purposes and cleaning it up for general usage is a bit of a pain. I'll just write some new code with what I mean and post that.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2016 at 12:37 am
J Livingston SQL (5/8/2016)
ok.The original code was found on the web http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html
can you please provide sample to code to explain what you mean?
Many thanks
Sorry, did not have much time to make it up, but this is how the trigger generated by dynamic query should look like:
IF OBJECT_ID('[dbo].[transdata_Audit]') IS NOT NULL
DROP TRIGGER [dbo].[transdata_Audit]
GO
CREATE TRIGGER [dbo].[transdata_Audit]
ON [dbo].[TransData]
FOR
--INSERT, ---uncomment if required
--DELETE, ---uncomment if required
UPDATE
AS
DECLARE @TableName sysname
SET @TableName = N'dbo.Transdata'
INSERT INTO dbo.Audit_User ( UserName )
SELECT SYSTEM_USER
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_User WHERE UserName = SYSTEM_USER)
INSERT INTO dbo.Audit_App ( AppName )
SELECT APP_NAME()
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_App WHERE AppName = APP_NAME())
INSERT dbo.Audit ( ActionDate, ActionType, TableColumnID,
Key_Col1,Key_COl2, Key_COl3, Key_COl4,
OldValue, NewValue,
UserID, AppID )
SELECT GETDATE(), 0, T.ID, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol1], i.[WideCol1],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol1' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol1])
UNION ALL
SELECT GETDATE(), 0, t.ID, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol2], i.[WideCol2],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol2' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol2])
UNION ALL
SELECT GETDATE(), 0, t.ID, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol3], i.[WideCol3],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol3' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol3])
I changed table definitions a bit:
CREATE TABLE Audit_User (
ID INT IDENTITY(1,1) NOT NULL ,
UserName NVARCHAR(128),
PRIMARY KEY (ID),
UNIQUE (UserName)
)
GO
CREATE TABLE Audit_App (
ID SMALLINT IDENTITY(-32678,1) NOT NULL ,
AppName NVARCHAR(128),
PRIMARY KEY (ID),
UNIQUE (AppName)
)
GO
CREATE TABLE Audit_TableColumn (
ID INT IDENTITY(-32678,1) NOT NULL ,
TableName sysname,
ColumnName sysname,
BeingMonitored BIT NOT NULL DEFAULT (1),
PRIMARY KEY NONCLUSTERED (ID),
UNIQUE CLUSTERED (TableName, ColumnName)
)
GO
CREATE TABLE Audit (
ActionDate DATETIME,
ActionType tinyint, -- 1 for INSERT, 0 for UPDATE, 255 for DELETE
TableColumnID int NOT NULL,
Key_Col1 SQL_VARIANT NOT NULL ,
Key_COl2 SQL_VARIANT NULL,
Key_COl3 SQL_VARIANT NULL,
Key_COl4 SQL_VARIANT null,
OldValue sql_variant,
NewValue sql_variant,
UserID int,
AppID INT
)
CREATE CLUSTERED INDEX CX_Audit ON Audit (ActionDate, ActionType, TableColumnID, Key_Col1, Key_Col2, Key_Col3, Key_Col4)
GO
_____________
Code for TallyGenerator
May 9, 2016 at 5:18 am
Sergiy (5/9/2016)
J Livingston SQL (5/8/2016)
ok.The original code was found on the web http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html
can you please provide sample to code to explain what you mean?
Many thanks
Sorry, did not have much time to make it up, but this is how the trigger generated by dynamic query should look like:
IF OBJECT_ID('[dbo].[transdata_Audit]') IS NOT NULLDROP TRIGGER [dbo].[transdata_Audit]
GO
CREATE TRIGGER [dbo].[transdata_Audit]
ON [dbo].[TransData]
FOR
--INSERT, ---uncomment if required
--DELETE, ---uncomment if required
UPDATE
AS
DECLARE @TableName sysname
SET @TableName = N'dbo.Transdata'
INSERT INTO dbo.Audit_User ( UserName )
SELECT SYSTEM_USER
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_User WHERE UserName = SYSTEM_USER)
INSERT INTO dbo.Audit_App ( AppName )
SELECT APP_NAME()
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_App WHERE AppName = APP_NAME())
INSERT dbo.Audit ( ActionDate, ActionType, TableColumnID,
Key_Col1,Key_COl2, Key_COl3, Key_COl4,
OldValue, NewValue,
UserID, AppID )
SELECT GETDATE(), 0, T.ID, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol1], i.[WideCol1],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol1' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol1])
UNION ALL
SELECT GETDATE(), 0, t.ID, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol2], i.[WideCol2],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol2' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol2])
UNION ALL
SELECT GETDATE(), 0, t.ID, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol3], i.[WideCol3],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol3' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol3])
I changed table definitions a bit:
CREATE TABLE Audit_User (ID INT IDENTITY(1,1) NOT NULL ,
UserName NVARCHAR(128),
PRIMARY KEY (ID),
UNIQUE (UserName)
)
GO
CREATE TABLE Audit_App (
ID SMALLINT IDENTITY(-32678,1) NOT NULL ,
AppName NVARCHAR(128),
PRIMARY KEY (ID),
UNIQUE (AppName)
)
GO
CREATE TABLE Audit_TableColumn (
ID INT IDENTITY(-32678,1) NOT NULL ,
TableName sysname,
ColumnName sysname,
BeingMonitored BIT NOT NULL DEFAULT (1),
PRIMARY KEY NONCLUSTERED (ID),
UNIQUE CLUSTERED (TableName, ColumnName)
)
GO
CREATE TABLE Audit (
ActionDate DATETIME,
ActionType tinyint, -- 1 for INSERT, 0 for UPDATE, 255 for DELETE
TableColumnID int NOT NULL,
Key_Col1 SQL_VARIANT NOT NULL ,
Key_COl2 SQL_VARIANT NULL,
Key_COl3 SQL_VARIANT NULL,
Key_COl4 SQL_VARIANT null,
OldValue sql_variant,
NewValue sql_variant,
UserID int,
AppID INT
)
CREATE CLUSTERED INDEX CX_Audit ON Audit (ActionDate, ActionType, TableColumnID, Key_Col1, Key_Col2, Key_Col3, Key_Col4)
GO
Hi...thanks for this....but I think that you need some code somewhere to populate dbo.Audit_TableColumn ??
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 9, 2016 at 5:47 am
It can be fun when management design systems, especially when they do not know what technology options are available or how they perform. It gives a lot of work to the technology people, and on a rare occasion actually meets the business needs.
I think your manager should be delegating the specification of this system to the specialists who actually know what are good and bad approaches. I would start by looking at the actual business requirements and verifying with whoever wrote them that they actually want what they are asking for.
Kimble has done a great job with BI design in formalising various types of slowly-changing dimensions. These concepts directly apply to OLTP systems also. If the business says it wants an audit kept of all data changes, they need to understand this will cost a lot more (time and money) than doing a piece of work to categorise the data into Type 1, 2, 3 etc SCDs and therefore applying the correct level of auditing to each data item.
If the data is categorised into various types of SCD, then most data is likely to be a Type 1 SCD and therefore changes will require no audit apart from maybe a date-last-changed being updated on the relevant table.
If there is a requirement to materialise all data changes, then look at the time period that this is required for. If you can keep log backups for the period that materialisation is required, then an alternative to an audit table may be to use a log reader product to rebuild the I/U/D statements and analyse those. One advantage of a log reader is all of the work is done offline to the database, and only done when a materialisation is required.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 9, 2016 at 8:18 am
Hi Sergiy
I have looked at your trigger and I am not seeing any benefits at all.....perhaps when you have time you can run thro the following set up and suggest where I have made a mistake 🙂
+-----------------------------------+
¦ comment ¦ av_dur_ms ¦
¦-----------------------+-----------¦
¦ _JLS_with_Audit ¦ 1523 ¦
¦ _JLS_without_Audit ¦ 175 ¦
¦ _Sergiy_with_Audit ¦ 3109 ¦
¦ _Sergiy_without_Audit ¦ 182 ¦
+-----------------------------------+
I have set up a test table with 1 million rows and am updating three columns for 100 000 rows.....with and without the triggers.
use [tempdb]
GO
IF OBJECT_ID('tempdb..TransData', 'U') IS NOT NULL DROP TABLE tempdb..TransData;
IF OBJECT_ID('tempdb..Audit_JLS', 'U') IS NOT NULL DROP TABLE tempdb..Audit_JLS;
IF OBJECT_ID('tempdb..Audit', 'U') IS NOT NULL DROP TABLE tempdb..Audit;
IF OBJECT_ID('tempdb..Audit_App', 'U') IS NOT NULL DROP TABLE tempdb..Audit_App;
IF OBJECT_ID('tempdb..Audit_TableColumn', 'U') IS NOT NULL DROP TABLE tempdb..Audit_TableColumn;
IF OBJECT_ID('tempdb..Audit_User', 'U') IS NOT NULL DROP TABLE tempdb..Audit_User;
IF OBJECT_ID('Results', 'U') IS NOT NULL DROP TABLE Results;
CREATE TABLE Audit_JLS (
Type CHAR(1),
TableName VARCHAR(128),
PK VARCHAR(1000),
ColumnName VARCHAR(128),
OldValue sql_variant,
NewValue sql_variant,
UpdateDate DATETIME,
UserName VARCHAR(128),
AppName VARCHAR(128)
)
GO
CREATE TABLE Audit_User (
ID INT IDENTITY(1,1) NOT NULL ,
UserName NVARCHAR(128),
PRIMARY KEY (ID),
UNIQUE (UserName)
)
GO
CREATE TABLE Audit_App (
ID SMALLINT IDENTITY(-32678,1) NOT NULL ,
AppName NVARCHAR(128),
PRIMARY KEY (ID),
UNIQUE (AppName)
)
GO
CREATE TABLE Audit_TableColumn (
ID INT IDENTITY(-32678,1) NOT NULL ,
TableName sysname,
ColumnName sysname,
BeingMonitored BIT NOT NULL DEFAULT (1),
PRIMARY KEY NONCLUSTERED (ID),
UNIQUE CLUSTERED (TableName, ColumnName)
)
GO
INSERT INTO Audit_TableColumn -- this added -- need dynamic method of populating for all columns in table
VALUES ('Transdata','WideCol1',1),('Transdata','WideCol2',1), ('Transdata','WideCol3',1)
CREATE TABLE Audit (
ActionDate DATETIME,
ActionType tinyint, -- 1 for INSERT, 0 for UPDATE, 255 for DELETE
TableColumnID int NOT NULL,
Key_Col1 SQL_VARIANT NOT NULL ,
Key_COl2 SQL_VARIANT NULL,
Key_COl3 SQL_VARIANT NULL,
Key_COl4 SQL_VARIANT null,
OldValue sql_variant,
NewValue sql_variant,
UserID int,
AppID INT
)
CREATE CLUSTERED INDEX CX_Audit ON Audit (ActionDate, ActionType, TableColumnID, Key_Col1, Key_Col2, Key_Col3, Key_Col4)
GO
CREATE TABLE Results (
Comment VARCHAR(50)
, StartTime DATETIME
, EndTime DATETIME
, Duration int
)
GO
/*create some test data*/
SELECT TOP 1000000
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),
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
ALTER TABLE [dbo].[TransData] ADD CONSTRAINT [PK_TransData] PRIMARY KEY CLUSTERED ([TranID] ASC)
GO
Create the generic audit trigger I originally posted
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_JLS (
Type,
TableName,
PK,
ColumnName,
OldValue,
NewValue,
UpdateDate,
UserName,
Appname)
select ''' + @Type + ''',''' + @TableName + ''',' + @PKField + ','''
+ @ColumnName + '''' + ',d.' + @ColumnName + ',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 3 columns on 100 000 rows
--run 10 iterations of update with trigger on
--then disable trigger and repeat
ENABLE TRIGGER transdata_Audit ON TransData
GO
SET NOCOUNT ON
DECLARE @StartTime DATETIME = getdate()
DECLARE @EndTime DATETIME
UPDATE TransData
SET
WideCol1 = WideCol1 + 20
, WideCol2 = WideCol2 - 20
, WideCol3 = WideCol3 + 50
WHERE (TranID % 10 = 0)
SELECT @EndTime = getdate()
INSERT Results
SELECT'_JLS_with_Audit', @StartTime, @EndTime ,datediff(MILLISECOND, @StartTime, @EndTime)
GO 10
DISABLE TRIGGER transdata_Audit ON TransData
GO
DECLARE @StartTime datetime = getdate()
DECLARE @EndTime datetime
UPDATE TransData
SET
WideCol1 = WideCol1 + 20
, WideCol2 = WideCol2 - 20
, WideCol3 = WideCol3 + 50
WHERE (TranID % 10 = 0)
SELECT @EndTime = getdate()
INSERT Results
SELECT'_JLS_without_Audit', @StartTime, @EndTime ,datediff(MILLISECOND, @StartTime, @EndTime)
GO 10
SET NOCOUNT OFF
now I use your trigger code
IF OBJECT_ID('[dbo].[transdata_Audit]') IS NOT NULL
DROP TRIGGER [dbo].[transdata_Audit]
GO
CREATE TRIGGER [dbo].[transdata_Audit]
ON [dbo].[TransData]
FOR
--INSERT, ---uncomment if required
--DELETE, ---uncomment if required
UPDATE
AS
DECLARE @TableName sysname
SET @TableName = 'Transdata'
INSERT INTO dbo.Audit_User ( UserName )
SELECT SYSTEM_USER
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_User WHERE UserName = SYSTEM_USER)
INSERT INTO dbo.Audit_App ( AppName )
SELECT APP_NAME()
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_App WHERE AppName = APP_NAME())
INSERT dbo.Audit ( ActionDate, ActionType, TableColumnID,
Key_Col1,Key_COl2, Key_COl3, Key_COl4,
OldValue, NewValue,
UserID, AppID )
SELECT GETDATE(), 0, T.id, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol1], i.[WideCol1],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = 'WideCol1' AND T.BeingMonitored = 1
WHERE UPDATE(WideCol1)
UNION ALL
SELECT GETDATE(), 0, t.id, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol2], i.[WideCol2],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol2' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol2])
UNION ALL
SELECT GETDATE(), 0, t.id, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol3], i.[WideCol3],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol3' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol3])
and perform same update for 3 columns on 100 000 rows
ENABLE TRIGGER transdata_Audit ON TransData
GO
SET NOCOUNT ON
DECLARE @StartTime DATETIME = getdate()
DECLARE @EndTime DATETIME
UPDATE TransData
SET
WideCol1 = WideCol1 + 20
, WideCol2 = WideCol2 - 20
, WideCol3 = WideCol3 + 50
WHERE (TranID % 10 = 0)
SELECT @EndTime = getdate()
INSERT Results
SELECT'_Sergiy_with_Audit', @StartTime, @EndTime ,datediff(MILLISECOND, @StartTime, @EndTime)
GO 10
DISABLE TRIGGER transdata_Audit ON TransData
GO
DECLARE @StartTime datetime = getdate()
DECLARE @EndTime datetime
UPDATE TransData
SET
WideCol1 = WideCol1 + 20
, WideCol2 = WideCol2 - 20
, WideCol3 = WideCol3 + 50
WHERE (TranID % 10 = 0)
SELECT @EndTime = getdate()
INSERT Results
SELECT'_Sergiy_without_Audit', @StartTime, @EndTime ,datediff(MILLISECOND, @StartTime, @EndTime)
GO 10
SET NOCOUNT OFF
and here are the results
SELECT comment
, AVG( duration) AS av_dur_ms
FROM Results
GROUP BY comment
ORDER BY comment
--select * from results
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 9, 2016 at 8:26 am
J Livingston SQL (5/9/2016)
Sergiy (5/9/2016)
J Livingston SQL (5/8/2016)
ok.The original code was found on the web http://www.nigelrivett.net/SQLTriggers/AuditTrailTrigger.html
can you please provide sample to code to explain what you mean?
Many thanks
Sorry, did not have much time to make it up, but this is how the trigger generated by dynamic query should look like:
IF OBJECT_ID('[dbo].[transdata_Audit]') IS NOT NULLDROP TRIGGER [dbo].[transdata_Audit]
GO
CREATE TRIGGER [dbo].[transdata_Audit]
ON [dbo].[TransData]
FOR
--INSERT, ---uncomment if required
--DELETE, ---uncomment if required
UPDATE
AS
DECLARE @TableName sysname
SET @TableName = N'dbo.Transdata'
INSERT INTO dbo.Audit_User ( UserName )
SELECT SYSTEM_USER
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_User WHERE UserName = SYSTEM_USER)
INSERT INTO dbo.Audit_App ( AppName )
SELECT APP_NAME()
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_App WHERE AppName = APP_NAME())
INSERT dbo.Audit ( ActionDate, ActionType, TableColumnID,
Key_Col1,Key_COl2, Key_COl3, Key_COl4,
OldValue, NewValue,
UserID, AppID )
SELECT GETDATE(), 0, T.ID, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol1], i.[WideCol1],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol1' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol1])
UNION ALL
SELECT GETDATE(), 0, t.ID, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol2], i.[WideCol2],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol2' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol2])
UNION ALL
SELECT GETDATE(), 0, t.ID, ISNULL(i.[TranID], d.[TranID]), NULL, NULL, NULL,
d.[WideCol3], i.[WideCol3],
U.ID, A.ID
FROM inserted i
INNER JOIN deleted d ON d.[TranID] = i.[TranID]
INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()
INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER
INNER JOIN dbo.Audit_TableColumn T ON T.TableName = @TableName AND t.ColumnName = N'WideCol3' AND T.BeingMonitored = 1
WHERE UPDATE([WideCol3])
I changed table definitions a bit:
CREATE TABLE Audit_User (ID INT IDENTITY(1,1) NOT NULL ,
UserName NVARCHAR(128),
PRIMARY KEY (ID),
UNIQUE (UserName)
)
GO
CREATE TABLE Audit_App (
ID SMALLINT IDENTITY(-32678,1) NOT NULL ,
AppName NVARCHAR(128),
PRIMARY KEY (ID),
UNIQUE (AppName)
)
GO
CREATE TABLE Audit_TableColumn (
ID INT IDENTITY(-32678,1) NOT NULL ,
TableName sysname,
ColumnName sysname,
BeingMonitored BIT NOT NULL DEFAULT (1),
PRIMARY KEY NONCLUSTERED (ID),
UNIQUE CLUSTERED (TableName, ColumnName)
)
GO
CREATE TABLE Audit (
ActionDate DATETIME,
ActionType tinyint, -- 1 for INSERT, 0 for UPDATE, 255 for DELETE
TableColumnID int NOT NULL,
Key_Col1 SQL_VARIANT NOT NULL ,
Key_COl2 SQL_VARIANT NULL,
Key_COl3 SQL_VARIANT NULL,
Key_COl4 SQL_VARIANT null,
OldValue sql_variant,
NewValue sql_variant,
UserID int,
AppID INT
)
CREATE CLUSTERED INDEX CX_Audit ON Audit (ActionDate, ActionType, TableColumnID, Key_Col1, Key_Col2, Key_Col3, Key_Col4)
GO
Hi...thanks for this....but I think that you need some code somewhere to populate dbo.Audit_TableColumn ??
Even though the solution presented here has serious limitations (and I think serious flaws too), I sense you're willing to go through with it anyway. Let me not be the one to stop you from trying. So here's a procedure to generate a trigger for any table in your db alike the one you presented. I've changed the getdate() into getutcdate() to avoid the pitfall of changing from daylight saving time. Plus I've changed the big union all going over each row many times into a cross apply going over only the columns, as I guess that should be faster (I didn't test it, so don't shoot me over this) 😉 )
Be aware that if your table has a primary key with more than 4 columns, this trigger will not be able to produce the correct key values, as dictated by your dbo.audit table.
And to answer your last question: by inserting rows into the dbo.Audit_TableColumn table you steer which columns should be monitored. As long as you don't put any rows in, any triggers you generate will indeed do nothing. But do not insert an new row for each column like was done for the USER and APP. Only insert audit_columns for those columns you need audited.
CREATE procedure dbo.sp_generate_audit_trigger
@object_id int
as
begin
declare @trigger_object_id int;
declare @stmt nvarchar(max) = null;
select
@trigger_object_id = tr.object_id
from sys.tables tbl
inner join sys.triggers tr on (tr.parent_id = tbl.object_id and tr.name = 'ta' + tbl.name + '_audit')
where tbl.[object_id] = @object_id;
-- First generate a create statement, so we can compare it to an existing definition.
-- Make sure to use uppercase for the keyword 'CREATE' as in the sql_modules.definition
-- the alter keyword is always replaced by an uppercase 'CREATE'.
select @stmt =
N'CREATE TRIGGER ' + quotename(schema_name(tbl.schema_id)) + N'.' + quotename(N'ta' + tbl.name + N'_audit')
+ crlf + N'ON ' + quotename(schema_name(tbl.schema_id)) + N'.' + quotename(tbl.name)
+ crlf + N'FOR'
+ crlf + N'--INSERT, ---uncomment if required'
+ crlf + N'--DELETE, ---uncomment if required'
+ crlf + N'UPDATE'
+ crlf + N'AS'
+ crlf
+ crlf + N'DECLARE @TableName sysname'
+ crlf + N'SET @TableName = N' + quotename(tbl.name, '''')
+ crlf
+ crlf
+ crlf + N'INSERT INTO dbo.Audit_User ( UserName )'
+ crlf + N'SELECT SYSTEM_USER'
+ crlf + N'WHERE NOT EXISTS (SELECT * FROM dbo.Audit_User WHERE UserName = SYSTEM_USER)'
+ crlf
+ crlf + N'INSERT INTO dbo.Audit_App ( AppName )'
+ crlf + N'SELECT APP_NAME()'
+ crlf + N'WHERE NOT EXISTS (SELECT * FROM dbo.Audit_App WHERE AppName = APP_NAME())'
+ crlf
+ crlf
+ crlf + N'INSERT dbo.Audit ( ActionDate, ActionType, TableColumnID,'
+ crlf + N' Key_Col1,Key_COl2,Key_COl3, Key_COl4,'
+ crlf + N' OldValue, NewValue,'
+ crlf + N' UserID, AppID )'
+ crlf
+ crlf + N'SELECT GETUTCDATE(), 0, x.COLID,'
+ crlf + N' ' + kc.keyvalues + N','
+ crlf + N' x.OldValue, x.NewValue,'
+ crlf + N' U.ID, A.ID'
+ crlf + N'FROM inserted i'
+ crlf + N' INNER JOIN deleted d ON ' + matchcolumns
+ crlf + N' INNER JOIN dbo.Audit_App A ON A.AppName = APP_NAME()'
+ crlf + N' INNER JOIN dbo.Audit_User U ON U.UserName = SYSTEM_USER'
+ crlf + N' CROSS APPLY ('
+ stuff((
select
crlf + N' UNION ALL'
+ crlf + N' SELECT T.ID, CONVERT(sql_variant, d.' + quotename(col.name) + N'),CONVERT(sql_variant, i.' + quotename(col.name) + N')'
+ crlf + N' FROM dbo.Audit_TableColumn T'
+ crlf + N' WHERE UPDATE(' + quotename(col.name) + N')'
+ crlf + N' AND T.TableName = ' + quotename(tbl.name,'''') + ' AND t.ColumnName = N' + quotename(col.name,'''') + ' AND T.BeingMonitored = 1'
from sys.columns col
where col.object_id = tbl.object_id
order by col.column_id
for xml path(''), type
).value('.','nvarchar(max)'), 1, 17, N'')
+ crlf + N' ) x (ColID,OldValue,NewValue)'
from (
select char(13) + char(10)
) t (crlf)
cross join sys.tables tbl
inner join (sys.indexes ix
cross apply (
select
stuff((
select N',' +
case when icol.object_id is null
then N'NULL'
else N'ISNULL(i.' + quotename(col_name(icol.object_id, icol.column_id)) + ',d.' + quotename(col_name(icol.object_id, icol.column_id)) + ')'
end as [text()]
from (
select 1 union all select 2 union all select 3 union all select 4
) n (n)
left outer join sys.index_columns icol on (icol.object_id = ix.object_id and icol.index_id = ix.index_id and icol.index_column_id = n.n)
order by n.n
for xml path(''), type
).value('.','nvarchar(max)'), 1, 1, N''),
stuff((
select N' AND d.' + quotename(col_name(icol.object_id, icol.column_id)) + ' = i.' + quotename(col_name(icol.object_id, icol.column_id)) as [text()]
from sys.index_columns icol
where icol.object_id = ix.object_id
and icol.index_id = ix.index_id
order by icol.index_column_id
for xml path(''), type
).value('.','nvarchar(max)'), 1, 5, N'')
) kc (keyvalues, matchcolumns)
) on (ix.object_id = tbl.object_id and ix.is_primary_key = 1)
where tbl.object_id = @object_id;
if @trigger_object_id is not null
begin
-- See if we need to alter the trigger:
if nullif((
select checksum(m.definition)
from sys.sql_modules m
where m.object_id = @trigger_object_id
), checksum(@stmt)) is not null
begin
select @stmt = stuff(@stmt, 1, 6, 'ALTER');
end
end
if @stmt is null
raiserror( 'Unable to create trigger.', 16, 1);
--select @stmt
--for xml path(''), type;
exec sp_executesql @stmt;
end
May 9, 2016 at 4:21 pm
J Livingston SQL (5/9/2016)
I think that you need some code somewhere to populate dbo.Audit_TableColumn ??
The idea is to populate it from some kind of "admin config" front end application.
And the dynamic script creating the trigger on the audited table must be in a trigger on dbo.Audit_TableColumn.
Triggers mayhem. :hehe:
_____________
Code for TallyGenerator
May 9, 2016 at 4:36 pm
R.P.Rozema (5/9/2016)
Even though the solution presented here has serious limitations (and I think serious flaws too),
I wonder who can see more of them - me or you? 🙂
I sense you're willing to go through with it anyway. Let me not be the one to stop you from trying.
Well, I've started. Now what? wipe it all?
Not before I face a dead end.
So here's a procedure to generate a trigger for any table in your db alike the one you presented. I've changed the getdate() into getutcdate() to avoid the pitfall of changing from daylight saving time. Plus I've changed the big union all going over each row many times into a cross apply going over only the columns, as I guess that should be faster (I didn't test it, so don't shoot me over this) Wink )
Thanks for doing this part for me.
I'll complete the UNION version anyway.
It's good to compare the performance of both approaches.
Be aware that if your table has a primary key with more than 4 columns, this trigger will not be able to produce the correct key values, as dictated by your dbo.audit table.
I'm aware that some tables might not have PK at all, and for them I'll have to use a UNIQUE key definitions (that's for tables having a nullable column in a key definition).
As long as you don't put any rows in, any triggers you generate will indeed do nothing.
When there are no records with [BeenMonitored]=1 the trigger won't be generated at all.
The core part of the trigger (UNION ALL) will be NULL, so the header added to NULL will produce NULL and no CREATE TRIGGER statement will be executed.
The " AND T.BeingMonitored = 1 " part in my query is a part of the testing draft.
It will not be in the actual trigger generated by the trigger on dbo.Audit_TableColumn.
Only columns having BeingMonitored = 1 will appear in the code of the trigger.
But do not insert an new row for each column like was done for the USER and APP. Only insert audit_columns for those columns you need audited.
Sorry, did not quite get it.
_____________
Code for TallyGenerator
May 16, 2016 at 3:36 pm
Sergiy (5/9/2016)
R.P.Rozema (5/9/2016)
Even though the solution presented here has serious limitations (and I think serious flaws too),
I wonder who can see more of them - me or you? 🙂
I sense you're willing to go through with it anyway. Let me not be the one to stop you from trying.
Well, I've started. Now what? wipe it all?
Not before I face a dead end.
So here's a procedure to generate a trigger for any table in your db alike the one you presented. I've changed the getdate() into getutcdate() to avoid the pitfall of changing from daylight saving time. Plus I've changed the big union all going over each row many times into a cross apply going over only the columns, as I guess that should be faster (I didn't test it, so don't shoot me over this) Wink )
Thanks for doing this part for me.
I'll complete the UNION version anyway.
It's good to compare the performance of both approaches.
Be aware that if your table has a primary key with more than 4 columns, this trigger will not be able to produce the correct key values, as dictated by your dbo.audit table.
I'm aware that some tables might not have PK at all, and for them I'll have to use a UNIQUE key definitions (that's for tables having a nullable column in a key definition).
As long as you don't put any rows in, any triggers you generate will indeed do nothing.
When there are no records with [BeenMonitored]=1 the trigger won't be generated at all.
The core part of the trigger (UNION ALL) will be NULL, so the header added to NULL will produce NULL and no CREATE TRIGGER statement will be executed.
The " AND T.BeingMonitored = 1 " part in my query is a part of the testing draft.
It will not be in the actual trigger generated by the trigger on dbo.Audit_TableColumn.
Only columns having BeingMonitored = 1 will appear in the code of the trigger.
But do not insert an new row for each column like was done for the USER and APP. Only insert audit_columns for those columns you need audited.
Sorry, did not quite get it.
did we ever reach a conclusion?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 17, 2016 at 8:15 am
In the trigger code presented by segiy, any applications and users are inserted into their respective tables with every run, if they are found not to exist yet:
INSERT INTO dbo.Audit_User ( UserName )
SELECT SYSTEM_USER
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_User WHERE UserName = SYSTEM_USER)
INSERT INTO dbo.Audit_App ( AppName )
SELECT APP_NAME()
WHERE NOT EXISTS (SELECT * FROM dbo.Audit_App WHERE AppName = APP_NAME())
In Post #1784267 you asked:
....but I think that you need some code somewhere to populate dbo.Audit_TableColumn ??
I was just pointing out that for dbo.Audit_TableColumn not a solution similar to that for dbo.Audit_User and dbo.Audit_App should be implemented. 🙂
Other than that, No I don't think there are any more conclusions so far.
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply