June 8, 2009 at 10:14 am
Hi guys,
Is there a whay to create a trigger on all the tables to monitor data being deleted.I have writted someting like this:
CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()), TABLE_NAME sysname, Activity char(6))
GO
DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables
WHILE @TABLE_NAME IS NOT NULL
BEGIN
SELECT @sql = 'CREATE TRIGGER [' + @TABLE_NAME + '_Usage_TR] ON [' + @TABLE_NAME +'] '
+ 'FOR DELETE AS '
+ 'IF EXISTS (SELECT * FROM deleted) '
+ 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''DELETE''' + ' '
+ ' GO'
SELECT @sql
EXEC(@sql)
SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME > @TABLE_NAME
END
SET NOCOUNT OFF
Unfortunaley I also need to capture the data that was deleted.IS there a way i can have this captured?
thanks in advance.
June 8, 2009 at 10:36 am
Use DDL Trigger.
Refer http://msdn.microsoft.com/en-us/library/ms175941.aspx"> http://msdn.microsoft.com/en-us/library/ms175941.aspx
June 8, 2009 at 10:43 am
Thank you praddep for the reply.Unfortunately I cannot use DDL triggers because they do not capture data being deleted from a table .They are usefull only when objects are deleted ,dropped or altered.I also need to capture the data that was deleted .
June 8, 2009 at 10:44 am
Add an XML column to your LOG_TABLE and add a FOR XML subquery:
'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''DELETE'''
+ ' (SELECT * FROM deleted FOR XML PATH, TYPE)'
Of course you understand that this is going to consume a *lot* of diskspace, right?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2009 at 10:46 am
ps (6/8/2009)
Use DDL Trigger.Refer http://msdn.microsoft.com/en-us/library/ms175941.aspx"> http://msdn.microsoft.com/en-us/library/ms175941.aspx
DDL triggers do not fire for data changes. They fire for DDL (data definition language) changes only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2009 at 11:16 am
thanx for the reply
I am getting syntax errors when trying to inplement XML
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
and
Incorrect syntax near the keyword 'FOR'
June 8, 2009 at 11:19 am
You are correct Gila.Is there a way I can implement this by modifying my existing code?At the moment it captures the table on which the data was being deleted but i would also need to capture the data that was deleted.
thanks in advance.
June 8, 2009 at 2:19 pm
Hmm, probably needs this extra comma:
'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''DELETE'''
+ ', (SELECT * FROM deleted FOR XML PATH, TYPE)'
-- ^
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2009 at 2:36 pm
RBarry it is still giving me error saying:
The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
what does the XML PATH and TYPE tables contain?Is the error occuring because we are saying select *?
June 8, 2009 at 2:41 pm
OK, this is a little bit hard to test with out your table. Try this:
'INSERT INTO LOG_TABLE (TABLE_NAME,Activity, YourXMLcolumn) SELECT ''' + @TABLE_NAME + ''', ''DELETE'''
+ ', (SELECT * FROM deleted FOR XML PATH, TYPE)'
-- ^
Note: you have to make the new column name in the INSERT list match what you actually named the column in your table.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 8, 2009 at 4:18 pm
Proactively and helpfully, I'm not sure why you want to do this, because you haven't explained it.
If you want to analyse all deletes, there are log analysis tools or cheaper just using profiler, that can help you do this.
Have I missed your point somewhere?
Tim
.
June 9, 2009 at 1:22 am
I had the same need: record the DML commands on a table in one database. I have used the auditing mechanisms.
With SQL Server 2008 auditing support is native thanks to the command "CREATE AUDIT" with SQL Server 2000 or 2005 we must build a custom solution based on SQL Trace (SQL Profiler is based on SQL Trace), you can find from this link:
http://community.ugiss.org/blogs/sgovoni/archive/2008/09/18/meccanismi-di-auditing.aspx
I used the trigger only for UPDATE commands to know the value that was an attribute before the change and the value which took the attribute after the change.
Bye
June 10, 2009 at 7:48 am
Tim,
I am thankfull that somebody has questioned my intentions.I understand that using triggers are going to be expensive.I am new to this job and I don't know of too many options.In your opinion what is the best way to analyse and record data deletes?Is SQL Broker an option?
thanks
June 10, 2009 at 9:23 am
I think it would be fair to say that logging this level of detail from ALL your tables is an unusual requirement, and I would question why you want to do it.
It's more common to just need some of the tables and when I need to do this I tend to use a specific trigger to hold the audit data in the same structure as the table itself with an additional column to say whether an insert, update, or delete was done.
Assuming that you do have a definite requirement to do this for some reason, here is a link to a way of doing it generically similar to the one that RBarryYoung was proposing. Bear in mind XML is VERY wordy not least because it effectively records the column names against every row and will therefore consume a lot of space.
http://qa.sqlservercentral.com/scripts/SQLInsider+Scripts/32008/
An alternative is to use a log analyzer after the event to analyze the log, E.g. apexsql log. Haven't used it myself so I can't make a recommendation, but it looks like a trial version is available.
http://www.apexsql.com/sql_tools_log.asp
Tim
.
June 10, 2009 at 12:06 pm
There are several 3rd party auditing products - ApexSQL and RedGate both have them IIRC. I think Paul Randall wrote a freebie script that will create the necessary triggers and tables as well you could possibly modify to just do delete auditing.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply