July 5, 2017 at 4:41 am
Probably been asked before but if I want to create a trigger for DML auditing on a particualr table.
Will FOR INSERT, DELETE suffice rather than
FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a Delete
So the question again For I&D or IU&D.
July 5, 2017 at 4:52 am
You say auditing but what is it you're trying to achieve? Are you trying to check it's in a valid format, for example? This might be more easily achieved by using constraints, for example, rather than relying on triggers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 5, 2017 at 6:16 am
Simple audit who change what when and to what.
From a few years back I remember reading that INSERTED UPDATED and DELETED are not all needed.
What I'm trying to find or remember is which one is not needed. I believe it is UPDATED as UPDATEs can be recorded as DELETED.
July 5, 2017 at 6:46 am
Talib123 - Wednesday, July 5, 2017 4:41 AMProbably been asked before but if I want to create a trigger for DML auditing on a particualr table.
Will FOR INSERT, DELETE suffice rather than
FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a DeleteSo the question again For I&D or IU&D.
IMHO, never audit inserts. Original inserts will be in the original table until there's an update or delete. Only log the "DELETED" side of a trigger. Doing otherwise will cause unnecessary duplication or triplication of data and your log table will unnecessarily grow explosively not to mention the problem with sorting out duplicates when you go to query things in it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2017 at 7:13 am
create a table with the same schema as the table you want to audit.
add additional columns for capturing whodunnit information.
then you just need a trigger to handle update and delete;
so say you have a table name dbo.Invoices, you might want to create a new schema History,and create the table History.Invoices to capture changes.
that History table cannot have the same primary key, as the same row could be modified more than once.
So if I assume you created Hostiry.Invoices, here's how i would do it:
--add some auditing columns
ALTER TABLE History.Invoices ADD
[EventAction] VARCHAR(30) NOT NULL,
[EventDate] DATETIME NOT NULL,
[DBName] VARCHAR(128) NULL,
[CurrentUser] VARCHAR(128) NULL,
[HostName] VARCHAR(128) NULL,
[ApplicationName] VARCHAR(128) NULL,
[ProcedureName] VARCHAR(128) NULL,
[Userid] SMALLINT NULL,
[UserName] VARCHAR(128) NULL,
[sUserid] INT NULL,
[sUserName] VARCHAR(128) NULL,
[Is_ServerAdmin_Sysadmin] INT NULL,
[Is_DB_owner] INT NULL,
[Is_DDL_Admin] INT NULL,
[Is_DB_Datareader] INT NULL,
[ORIGINAL_LOGIN] VARCHAR(128) NULL,
[net_transport] VARCHAR(128) NULL,
[protocol_type] VARCHAR(128) NULL,
[auth_scheme] VARCHAR(128) NULL,
[local_net_address] NVARCHAR(128) NULL,
[local_tcp_port] NVARCHAR(128) NULL,
[client_net_address] NVARCHAR(128) NULL,
[physical_net_transport] NVARCHAR(128) NULL
GO
create TRIGGER TR_InvoiceAudit ON [dbo].[Invoices]
WITH EXECUTE AS OWNER --avoid permissions problems
FOR UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Action VARCHAR(30)
SELECT @Action = CASE
WHEN NOT EXISTS(SELECT 1 FROM INSERTED)
THEN 'Deleted'
ELSE 'Update'
END
-- I only need the "old" value, or the deleted value, since the current is in the real table; i can join on keys to compare if needed.
INSERT INTO History.Invoices([OriginalColumnList],
[EventAction],
[EventDate],[DBName],[CurrentUser],[HostName],[ApplicationName],[ProcedureName],[Userid],[UserName],[sUserid],[sUserName],[Is_ServerAdmin_Sysadmin],[Is_DB_owner],[Is_DDL_Admin],[Is_DB_Datareader],[ORIGINAL_LOGIN],[net_transport],[protocol_type],[auth_scheme],[local_net_address],[local_tcp_port],[client_net_address],[physical_net_transport])
SELECT [OriginalColumnList],
@Action AS [EventAction],
getdate() AS EventDate,
CONVERT(varchar(128),DB_NAME()) AS DBName,
CONVERT(varchar(128),CURRENT_USER) AS CurrentUser,
CONVERT(varchar(128),HOST_NAME()) AS HostName,
CONVERT(varchar(128),APP_NAME()) AS ApplicationName,
CONVERT(varchar(128),OBJECT_NAME(@@PROCID)) AS ProcedureName,
USER_ID() AS Userid,
CONVERT(varchar(128),USER_NAME()) AS UserName,
SUSER_ID() AS sUserid,
CONVERT(varchar(128),SUSER_SNAME()) AS sUserName,
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
CONVERT(varchar(128),ORIGINAL_LOGIN()) AS [ORIGINAL_LOGIN],
CONVERT(varchar(128),ConnectionProperty('net_transport')) AS 'net_transport',
CONVERT(varchar(128),ConnectionProperty('protocol_type')) AS 'protocol_type',
CONVERT(varchar(128),ConnectionProperty('auth_scheme')) AS 'auth_scheme',
CONVERT(varchar(128),ConnectionProperty('local_net_address')) AS 'local_net_address',
CONVERT(varchar(128),ConnectionProperty('local_tcp_port')) AS 'local_tcp_port',
CONVERT(varchar(128),ConnectionProperty('client_net_address')) AS 'client_net_address',
CONVERT(varchar(128),ConnectionProperty('physical_net_transport')) AS 'physical_net_transport'
FROM [DELETED] [MySource]
END -- TRIGGER
Lowell
July 5, 2017 at 7:23 am
Jeff Moden - Wednesday, July 5, 2017 6:46 AMTalib123 - Wednesday, July 5, 2017 4:41 AMProbably been asked before but if I want to create a trigger for DML auditing on a particualr table.
Will FOR INSERT, DELETE suffice rather than
FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a DeleteSo the question again For I&D or IU&D.
IMHO, never audit inserts. Original inserts will be in the original table until there's an update or delete. Only log the "DELETED" side of a trigger. Doing otherwise will cause unnecessary duplication or triplication of data and your log table will unnecessarily grow explosively not to mention the problem with sorting out duplicates when you go to query things in it.
1) I'm going to play counterpoint to the recommendation to not log INSERTs and to only log DELETED side. An upside for doing it the other way is that everything you need for any investigation is now in a single table. The production table never need be hit for any investigation you need to do. No join, and in many/most cases you won't need a SORT to get things into a form that would be consumable for an investigation either.
2) Jeff, I don't really understand the statement about data explosion. If I only log INSERTs, DELETEs and the INSERT-side of UPDATES, how do I get (unwanted) data explosion?
3) VERY important: NEVER put more than ONE form of audit code in each trigger. i.e. if you need to audit all three DML actions, you MUST have 3 triggers. I have a client right now that is absolutely crushing their system with 3-fer audit triggers due to the need to join INSERTED/DELETED 3 times just to find out which action the trigger is firing for. Yes, it would be more efficient to do 2 EXISTS instead of the joins they are doing. But it would be even faster so simply do the singular action required.
That last point brings up a good Guruism:
The fastest thing you can do in SQL Server is NOTHING!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 6, 2017 at 3:18 am
Thanks for all the advice guys.
July 6, 2017 at 10:19 pm
TheSQLGuru - Wednesday, July 5, 2017 7:23 AMJeff Moden - Wednesday, July 5, 2017 6:46 AMTalib123 - Wednesday, July 5, 2017 4:41 AMProbably been asked before but if I want to create a trigger for DML auditing on a particualr table.
Will FOR INSERT, DELETE suffice rather than
FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a DeleteSo the question again For I&D or IU&D.
IMHO, never audit inserts. Original inserts will be in the original table until there's an update or delete. Only log the "DELETED" side of a trigger. Doing otherwise will cause unnecessary duplication or triplication of data and your log table will unnecessarily grow explosively not to mention the problem with sorting out duplicates when you go to query things in it.
1) I'm going to play counterpoint to the recommendation to not log INSERTs and to only log DELETED side. An upside for doing it the other way is that everything you need for any investigation is now in a single table. The production table never need be hit for any investigation you need to do. No join, and in many/most cases you won't need a SORT to get things into a form that would be consumable for an investigation either.
2) Jeff, I don't really understand the statement about data explosion. If I only log INSERTs, DELETEs and the INSERT-side of UPDATES, how do I get (unwanted) data explosion?
3) VERY important: NEVER put more than ONE form of audit code in each trigger. i.e. if you need to audit all three DML actions, you MUST have 3 triggers. I have a client right now that is absolutely crushing their system with 3-fer audit triggers due to the need to join INSERTED/DELETED 3 times just to find out which action the trigger is firing for. Yes, it would be more efficient to do 2 EXISTS instead of the joins they are doing. But it would be even faster so simply do the singular action required.
That last point brings up a good Guruism:
The fastest thing you can do in SQL Server is NOTHING!!
Heh... I'll have to disagree but not for the reason you might suspect. You shouldn't be auditing INSERTs to begin with and you should only audit the DELETED logical table. If you're doing whole row auditing (instead of column auditing), you don't even have to look at the INSERTED logical table in triggers. Of course, the exception to that rule is if you want to check every column for an actual change and not just an accidental repeated update.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 8, 2017 at 6:54 pm
Jeff Moden - Thursday, July 6, 2017 10:19 PM0oi]
Jeff Moden - Thursday, July 6, 2017 10:19 PMTheSQLGuru - Wednesday, July 5, 2017 7:23 AMJeff Moden - Wednesday, July 5, 2017 6:46 AMTalib123 - Wednesday, July 5, 2017 4:41 AMProbably been asked before but if I want to create a trigger for DML auditing on a particualr table.
Will FOR INSERT, DELETE suffice rather than
FOR INSERT,UPDATE DELETE. The later if I remember correctly can produce duplicates in the audit table as an UPDATE is also logged as a DeleteSo the question again For I&D or IU&D.
IMHO, never audit inserts. Original inserts will be in the original table until there's an update or delete. Only log the "DELETED" side of a trigger. Doing otherwise will cause unnecessary duplication or triplication of data and your log table will unnecessarily grow explosively not to mention the problem with sorting out duplicates when you go to query things in it.
1) I'm going to play counterpoint to the recommendation to not log INSERTs and to only log DELETED side. An upside for doing it the other way is that everything you need for any investigation is now in a single table. The production table never need be hit for any investigation you need to do. No join, and in many/most cases you won't need a SORT to get things into a form that would be consumable for an investigation either.
2) Jeff, I don't really understand the statement about data explosion. If I only log INSERTs, DELETEs and the INSERT-side of UPDATES, how do I get (unwanted) data explosion?
3) VERY important: NEVER put more than ONE form of audit code in each trigger. i.e. if you need to audit all three DML actions, you MUST have 3 triggers. I have a client right now that is absolutely crushing their system with 3-fer audit triggers due to the need to join INSERTED/DELETED 3 times just to find out which action the trigger is firing for. Yes, it would be more efficient to do 2 EXISTS instead of the joins they are doing. But it would be even faster so simply do the singular action required.
That last point brings up a good Guruism:
The fastest thing you can do in SQL Server is NOTHING!!
Heh... I'll have to disagree but not for the reason you might suspect. You shouldn't be auditing INSERTs to begin with and you should only audit the DELETED logical table. If you're doing whole row auditing (instead of column auditing), you don't even have to look at the INSERTED logical table in triggers. Of course, the exception to that rule is if you want to check every column for an actual change and not just an accidental repeated update.
Sorry... I didn't actually address your second question... If you log INSERTs, that's an automatic doubling of the data because the original data lives both in the original table and the and the audit table. For rows that are never updated, that's mostly a waste of storage. If you record just the INSERTED logical table for updates, then not so bad a data explosion as what a lot of people get because they record both the INSERTED and DELETED logical tables for every blooming update.
I do agree that if you spend a whole lot of time researching what's in an audit table, then recording INSERTs and just the INSERTED logical table for updates (as well as any DELETEs) will make life easier there and will, as you stated, keep you from having to join to the original table. If you don't have the need to do such research very often, then the disk savings by not auditing the original insert can be worth it especially when it comes to whole row auditing.
If you're doing the other type of auditing, columnar auditing, then it's essential that you don't audit inserts because the disk space requirements for the audit table will easily be 5 times the size of the original table just for the INSERTs.
As you say, you can get by the requirement of joining the INSERTED and DELETED tables altogether using EXISTS but I've found that using NOT EXISTS is slightly more effective. Here's that snippet of code from the audit triggers that I have in production. And, if the answer is "I", I simply do a return with no action taken. The code will seem backwards at first until you remember that I'm using NOT EXISTS. Because of the "short circuit" done by the case and the NOT negation, INSERTs only have to look at one of the logical tables instead of both.
--===== Determine the type of trigger action
SELECT @Operation = CASE
WHEN NOT EXISTS (SELECT TOP 1 1 FROM DELETED) THEN 'I'
WHEN NOT EXISTS (SELECT TOP 1 1 FROM INSERTED) THEN 'D'
ELSE 'U'
END
;
It is a shame that SQL Server doesn't have a "TriggerType()" function built into it.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2017 at 1:28 pm
I don't like the unnecessary TOP 1 1 you have in your code, although fortunately the optimizer has been coded to ignore it and do the same thing as a check without it. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 12, 2017 at 8:57 am
TheSQLGuru - Monday, July 10, 2017 1:28 PMI don't like the unnecessary TOP 1 1 you have in your code, although fortunately the optimizer has been coded to ignore it and do the same thing as a check without it. 🙂
Ah... I absolutely agree. It's not there for functionality. It's there to convince the uninformed. I originally couldn't convince folks at work as to what "*" would do there. It's an appeasement for doubters and provides a clue for the uninformed. As you say, it get's ignored by the thing that matters most so I never fixed it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply