July 31, 2009 at 7:42 am
I have a database that is used strictly for reporting purposes... the database contains various lookup tables used by our reporting system. The tables are fairly static (maybe modified once a quarter)... so I just use RedGate Data Compare to sync the data from the development environment to Production...
Currently, I am trying to create an auditing system that will monitor any DDL and DML changes. The DDL Auditing was easy (lots of good documentation here, thanks everyone!). However, the DML Auditing is a bit more of a challenge.
I am trying to get a trigger to modify the Extended Properties on the table anytime the table is modified. Also, it would be nice to know what kind of modification was done from a data standpoint (INSERT, UPDATE, DELETE).
Here is an example:
-- Create Table, Test Values
CREATE TABLE MyTest (RowNumber INT identity(1,1), MyVal int NOT NULL, MyComment varchar(10) NULL);
Insert into MyTest
SELECT '1','One' UNION
SELECT '2','Two' UNION
SELECT '3','Three'
---- Create Extended Property
EXEC sys.sp_addextendedproperty
@name = N'Version', @value = N'1',
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'TABLE', @level1name = [MyTest];
EXEC sys.sp_addextendedproperty
@name = N'Rev1', @value = N'First Table version, John Doe 4/2/09',
@level0type = N'SCHEMA', @level0name = [dbo],
@level1type = N'TABLE', @level1name = [MyTest];
---- Query All Extended Properties on table
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'MyTest', NULL, NULL);
So far, so good. Now to create a trigger to see if we can capture any data changes on the table:
CREATE TRIGGER [dbo].[MyTestModify]
ON [dbo].[MyTest]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @RowNun int;
SET @RowNun = @@RowCount;
PRINT 'RowCount is '+CAST(@RowNun as varchar);
Then some tests on the trigger itself:
---- INSERT Test
Insert into dbo.MyTest
SELECT '4','Four' UNION
SELECT '5','Five' UNION
SELECT '6','Seven'
---- UPDATE Test
UPDATE dbo.MyTest
SET MyComment = 'Six'
WHERE MyVal = '6'
------ DELETE Test
DELETE from dbo.MyTest
WHERE MyVal IN ('4','5','6');
Again, so far so good. I was able to capture the number of changes made. Now comes the challenge... how does one capture the actual DML event, i.e, was it an INSERT, UPDATE or a DELETE?
According to BOL (Create trigger), section H, Microsoft has provided an example of code for "viewing the events that cause a trigger to fire"... ok... sounds good to me. So we modify the trigger as follows:
ALTER TRIGGER [dbo].[MyTestModify]
ON [dbo].[MyTest]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @RowNun int,
@EVType varchar(20);
SET @RowNun = @@RowCount;
SELECT @EVType = TE.type_Desc
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.name = 'MyTestModify'
PRINT 'There was a '+@EVType + ' of records '+CAST(@RowNun as varchar);
We try the tests again from above... only to get three messages:
There was a DELETE of records 3 ---- from the insert test
There was a DELETE of records 1 ---- from the update test
There was a DELETE of records 3 ---- ok, got that one right, LOL, but I think we have an overall problem here!
Ideally, I could capture the DML event itself along with the User Name and number of records, then create a new Extended Property (Rev2, etc.) along with changing the Version on the table.
Any comments or suggestions?
Thanks! 🙂
Oh... BTW, in case anyone was wondering how to modify the Extended Properties, here is an example of how to get the property and print out a possible change. Just imagine a modified version of this built into a trigger 😉 ... hmmm... might make a good article when I am done!
-- Query Extended Property based on change
DECLARE @CurrentVer sql_variant;
SELECT @CurrentVer = value
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'MyTest', NULL, NULL)
WHERE name = 'Version';
PRINT 'Current Value: '+CAST(@CurrentVer as varchar) + ', New Value: '+CAST((CAST(@CurrentVer as TinyInt)+1) as varchar);
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
July 31, 2009 at 10:09 am
I guess the problem is trigger_events: it contains every event that makes the trigger fire, not the event that fired the trigger NOW.
You could guess the operation type looking at logical tables:
1) No rows in DELETED, rows in INSERTED --> INSERT
2) Rows in DELETED and INSERTED --> UPDATE
3) No rows in INSERTED, rows in DELETED --> DELETE
Hope this helps
Gianluca
-- Gianluca Sartori
July 31, 2009 at 10:13 am
You could also get the full SQL command that fired the trigger:
SELECT text
FROM sys.dm_exec_requests
CROSS APPLY sys.fn_get_sql(sql_handle)
But I don't know if this fits your needs.
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply