SQL server Audit Trigger for INSERT , UPDATE , DELETE
-
- SQL learner22 - Thursday, October 11, 2018 10:22 AM
Hi SQL Central Colleagues,
I was as asked to create DDL trigger to capture only what changed in a table and old values with associated user name and kept in the trigger table. Basically they want to know if the tables are changing or getting updated.
Above picture is what the solution should look like.
thanks.Nice picture. What does your attempt to code this look like?
Also, what (if any) errors did you get when you ran it?
-
If you are asking for help, we'd like to see what you've done, or what you think.
Also, which versions and what is the purpose of the requirement? This can affect what I'd recommend.
-
Steve, I'm a big fan of yours and great full for the things you have been doing for community for a long long time. thanks.
I'm going to tell you why the organization wants to capture data updates in several tables in both SQL server 2012 and 2016.
they want to learn what kind of users are using these tables and what they do. (this is all what i was told:)).GO
/****** Object: Trigger [dbo].[AuditTrigger] Script Date: 10/15/2018 9:41:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER TRIGGER
[dbo].[AuditTrigger]
ON
[dbo].[STATIONS]
AFTER
INSERT , UPDATE , DELETE
AS
declare
@sql_operation as varchar(10)
IF
EXISTS
(
SELECT
NULL
FROM
inserted
)
BEGINIF
EXISTS
(
SELECT
NULL
FROM
deleted
)
BEGINSET @sql_operation = 'U'; -- UPDATE
END
ELSE -- No rows in "deleted" table
BEGINSET @sql_operation = 'I'; -- INSERT
END;
END
ELSE -- No rows in "inserted" table
BEGIN
SET @sql_operation = 'D'; -- DELETE
END;
INSERT INTO dbo.StationsAudit
(
UserName,DateTime, Action, ChangedItem,ChangedTo,ChangedFrom
)SELECT
UserName = SYSTEM_USER,
DateTime = SYSDATETIME() ,
Action = @sql_operation ,
ChangedItem = FieldValues.FieldName ,
ChangedTo = isnull (CAST (FieldValues.AfterValue AS VARCHAR(100)),''),
ChangedFrom = isnull (CAST (FieldValues.BeforeValue AS VARCHAR(100)),'')
FROM
(
SELECT
[STATION_KEY_Before] = deleted.STATION_KEY ,
[STATION_KEY_After] = inserted.STATION_KEY ,
[StationDes_Before] = inserted.[StationDes],
[StationDes_after] = deleted.[StationDes],
[Lat_DD_before] = inserted.[Lat_DD],
[Lat_DD_after] = deleted.[Lat_DD],
[Long_DD_Before] = inserted.[Long_DD],
[Long_DD_after] = deleted.[Long_DD],
[Datum_before]= inserted.[Datum],
[Datum_aftter]= inserted.[Datum],
[CollMethod_before] = inserted.[CollMethod],
[CollMethod_after] = deleted.[CollMethod],
[mapscale_Before] = inserted.[MapScale],
[mapscale_after] = deleted.[MapScale],
[MonLocType_before] = inserted.[MonLocType],
[MonLocType_after] = Deleted.[MonLocType],
[TribalLand_before] = inserted.[TribalLand],
[TribalLand_after] = deleted.[TribalLand],
[TribalName_before]= Inserted.[TribalName],
[TribalName_after]= Deleted.[TribalName],
[AltLocID_before]= Inserted.[AltLocID],
[AltLocID_after]= deleted.[AltLocID],
[AltLocName_before]= inserted.[AltLocName],
[AltLocName_after]= Deleted.[AltLocName],
[InWQX_before]= inserted.[InWQX],
[InWQX_after]= deleted.[InWQX],
[WellType_before]= inserted.WellType,
[WellType_after]= deleted.WellType,
[WellFormType_before]= inserted.[WellFormType],
[WellFormType_after]= deleted.[WellFormType],
[WellDepth_before]= inserted.[WellDepth],
[WellDepth_after]= deleted.[WellDepth],
[WellDepthUnit_before]= inserted.WellDepthUnit ,
[WellDepthUnit_after]= deleted.WellDepthUnit,
[Comments_before]= Inserted.Comments,
[Comments_after]= deleted.Comments,
[IsFinal_before]= inserted.[IsFinal],
[IsFinal_after]= deleted.[IsFinal],
[UserName_before]= inserted.[UserName],
[UserName_after]= deleted.[UserName],
[GlobalID_before]= inserted.[GlobalID],
[GlobalID_after]= deleted.[GlobalID],
[created_user_before]= inserted.[created_user],
[created_user_after]= deleted.[created_user],
[created_date_before]= inserted.[created_date],
[created_date_after]= deleted.[created_date],
[last_edited_user_before]= inserted.[last_edited_user],
[last_edited_user_after]= deleted.[last_edited_user],
[last_edited_date_before]= inserted.[last_edited_date],
[last_edited_date_after]= deleted.[last_edited_date],
[Shape_before]= inserted.[Shape],
[Shape_after]= deleted.[Shape],
[WellAquiferName_before]= inserted.[WellAquiferName],
[WellAquiferName_after]= deleted.[WellAquiferName],
[Reachcode_before]= inserted.[Reachcode],
[Reachcode_after]= deleted.[Reachcode],
[Measure_before]= inserted.[Measure],
[Measure_after]= deleted.[Measure],
[LLID_before]= inserted.[LLID],
[LLID_after]= deleted.[LLID],
[RiverMile_before]= inserted.[RiverMile],
[RiverMile_after]= deleted.[RiverMile]FROM
inserted
FULL OUTER JOIN
deleted
ON
inserted.[STATION_KEY] = deleted.[STATION_KEY])
AS
RawData
CROSS APPLY
(
VALUES
(N'StationKeys' , CAST ([STATION_KEY_Before] AS NVARCHAR(MAX)) , CAST ([STATION_KEY_After] AS NVARCHAR(MAX))) ,
(N'StationDes' , CAST ([StationDes_Before] AS NVARCHAR(MAX)) , CAST ([StationDes_After] AS NVARCHAR(MAX))) ,
(N'Lat_DD' , CAST ([Lat_DD_Before] AS NVARCHAR(MAX)) , CAST ([Lat_DD_After] AS NVARCHAR(MAX))) ,
(N'Long_DD' , CAST ([Long_DD_Before] AS NVARCHAR(MAX)) , CAST ([Long_DD_After] AS NVARCHAR(MAX))) ,
(N'Datum' , CAST ([Datum_Before] AS NVARCHAR(MAX)) , CAST ([Datum_aftter] AS NVARCHAR(MAX))) ,
(N'Collmethod' , CAST ([Collmethod_Before] AS NVARCHAR(MAX)) , CAST ([Collmethod_After] AS NVARCHAR(MAX))),
(N'MapScale' , CAST ([MapScale_Before] AS NVARCHAR(MAX)) , CAST ([MapScale_After] AS NVARCHAR(MAX))) ,
(N'MonLocType' , CAST ([MonLocType_Before] AS NVARCHAR(MAX)) , CAST ([MonLocType_After] AS NVARCHAR(MAX))),
(N'TribalLand' , CAST ([TribalLand_Before] AS NVARCHAR(MAX)) , CAST ([TribalLand_after] AS NVARCHAR(MAX))) ,
(N'TribalName' , CAST ([TribalLand_Before] AS NVARCHAR(MAX)) , CAST ([TribalName_after] AS NVARCHAR(MAX))),
(N'AltLocID' , CAST ([AltLocID_Before] AS NVARCHAR(MAX)) , CAST ([AltLocID_after] AS NVARCHAR(MAX))),
(N'AltLocName' , CAST ([AltLocName_Before] AS NVARCHAR(MAX)) , CAST ([AltLocName_after] AS NVARCHAR(MAX))),
(N'WellType' , CAST ([WellType_before] AS NVARCHAR(MAX)) , CAST ([WellType_after] AS NVARCHAR(MAX))),
(N'WellFormType' , CAST ([WellFormType_before] AS NVARCHAR(MAX)) , CAST ([WellFormType_after] AS NVARCHAR(MAX))),
(N'WellDepth' , CAST ([WellDepth_before] AS NVARCHAR(MAX)) , CAST ([WellDepth_after] AS NVARCHAR(MAX))),
(N'WellDepthUnit', CAST ([WellDepthUnit_before] AS NVARCHAR(MAX)) , CAST ([WellDepthUnit_after] AS NVARCHAR(MAX))),
(N'Comments', CAST ([Comments_before] AS NVARCHAR(MAX)) , CAST ([Comments_after] AS NVARCHAR(MAX))),
(N'IsFinal', CAST ([IsFinal_before] AS NVARCHAR(MAX)) , CAST ([IsFinal_after] AS NVARCHAR(MAX))),
(N'UserName', CAST ([UserName_before] AS NVARCHAR(MAX)) , CAST ([UserName_after] AS NVARCHAR(MAX))),
(N'GlobalID', CAST ([GlobalID_before] AS NVARCHAR(MAX)) , CAST ([GlobalID_after] AS NVARCHAR(MAX))),
(N'created_user', CAST ([created_user_before] AS NVARCHAR(MAX)) , CAST ([created_user_after] AS NVARCHAR(MAX))),
(N'created_date', CAST ([created_date_before] AS NVARCHAR(MAX)) , CAST ([created_date_after] AS NVARCHAR(MAX))),
(N'last_edited_user', CAST ([last_edited_user_before] AS NVARCHAR(MAX)) , CAST ([last_edited_user_after] AS NVARCHAR(MAX))),
(N'last_edited_date', CAST ([last_edited_date_before] AS NVARCHAR(MAX)) , CAST ([last_edited_date_after] AS NVARCHAR(MAX))),
(N'Shape', CAST ([Shape_before] AS NVARCHAR(MAX)) , CAST ([Shape_after] AS NVARCHAR(MAX))),
(N'[WellAquiferName', CAST ([WellAquiferName_before] AS NVARCHAR(MAX)) , CAST ([WellAquiferName_after] AS NVARCHAR(MAX))),
(N'Reachcode', CAST ([Reachcode_before] AS NVARCHAR(MAX)) , CAST ([Reachcode_after] AS NVARCHAR(MAX))),
(N'Measure', CAST ([Measure_before] AS NVARCHAR(MAX)) , CAST ([Measure_after] AS NVARCHAR(MAX))),
(N'RiverMile', CAST ([RiverMile_before] AS NVARCHAR(MAX)) , CAST ([RiverMile_after] AS NVARCHAR(MAX))))
AS
FieldValues (FieldName , BeforeValue , AfterValue); - SQL learner22 - Thursday, October 11, 2018 10:22 AM
Hi SQL Central Colleagues,
I was as asked to create DDL trigger to capture only what changed in a table and old values with associated user name and kept in the trigger table. Basically they want to know if the tables are changing or getting updated.
Above picture is what the solution should look like.
thanks.That design will lead to a huge amount of duplicated data and is totally unnecessary. Every insert in "table" will be instantly duplicated in "inserted". On the first update, the original row will again be duplicated in the "deleted" table. That's 2 copies of the original data when you really only need 1. Worse yet, every update will produce two rows, as well, That makes for a shedload of totally unnecessary duplicated data that isn't going to buy you a thing except provide justification for why you need to at least quadruple your disk estimates for this one table.
You should only audit changes to the original data (only capture the data from the DELETED logical tables in triggers). If there are no changes, the original data will live in "table". If there are changes, then the original row will live in "deleted". There is no need at all for "inserted". None.
And for the love of all that is holy for indexes and disk space, do NOT have both a CREATED_DATE/CREATED_BY and MODIFIED_DATE/MODIFIED_BY set of columns in you "table" table.
There's more to it than that. You can do some absolutely awesome stuff with audit triggers and the right kind of audit/history table (including Point -in-time SCD 6 (Slowly Changing Dimensions Type = 6) but I don't want to waste your time or mine if folks are hell bent on the design you posted (which will kill reporting, disk space, and update speeds). Post back if you need help with SCD 6, which could also be modified to have the historical data in a separate audit table, if that's you're druther. 😛 If you need to know who or what made the change, I'll cover that, as well. While it may sound like a simple addition, you have to ensure that you don't create an "ExpAnsive" situation (as you can with any variable width column) which will destroy your indexes with both logical and physical fragmentation essentially making the table "permanently fragmented" and in need of much unneeded index maintenance.
Here's a decent article on SCDs. Pay particular attention to the "Pure type 6 implementation" implementation (again, could be modified to use an audit table just to keep the main table shorter). And, no... don't use an "Current_Flag" column :sick:, either. It's a waste of time and space and will cause a lot of data movement in your index(es), as well, which is a very bad thing.
https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_6One final thing... don't use 12/31/9999 for the "open" end-date. Just use '9999'. which will convert to 9999-01-01 and leaves you some headroom for some other very important date tricks with the SCD 6 structure in the future. I'll explain that if you're interested, as well.
And, all that NVARCHAR(MAX) junk? It's death by SQL because 1) you can't index such columns and 2) it's a waste of memory allocation when you query such monsters even if empty and 3) they're going to slow things down.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I am doing a similar thing myself for a database and have discovered the joys of SQL Audit. Now that you've given us some sample code, I'm willing to share this thread: https://www.sqlservercentral.com/Forums/FindPost1998992.aspx
EDIT: Please note that if you need to audit INSERTs, store your table data in a different database than the one you're auditing. Otherwise, like Jeff said, you'll end up in an infinite loop of inserting which is NOT GOOD.
-
- SQL learner22 - Tuesday, October 16, 2018 11:17 AM
I replied to this question on the other thread.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply