Monitoring a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Monitoring a table

Author

Topic

CanadaDBA

583 Posts

Posted - 2008-07-06 : 08:12:05

I want to monitor a table to find out when a delete or table truncate happens. I need to know WHEN and WHO did this. I am going to create a trigger on delete and save date and time but how find out who is deleting?

What other information do you suggest to record?

Thanks,

Canada DBA

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-06 : 13:21:16

Take look at user_name() function.

maninder
Posting Yak Master

100 Posts

Posted - 2008-07-06 : 19:55:00

You can also Capture EventTime,EventType,ServerName,DatabaseName,ObjectType,ObjectName,UserName,CommandText if you are not doing so.. Else make sure you dont have too many delete's and insert's into the table, pr your TRACE might get really BIG and raise other Performance issues.

Maninder
SQL server Architect / Admin (DEV&PROD)

CanadaDBA

583 Posts

Posted - 2008-07-06 : 21:59:38

I have created a table and a trigger will insert a line on deletion.


    INSERT Admin.dbo.DBA_Log 
		SELECT GETDATE(), host_name(), db_name(), suser_sname(), user_name(), app_name()

How can I find out the table name? I mean the table which deletion happend on it.

Canada DBA

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-06 : 23:25:14

object_name()?

CanadaDBA

583 Posts

Posted - 2008-07-06 : 23:31:12

Assume the variables are declared and has correct value.


SET @DynSQL = 'USE [' + @db_name + '] 
GO 

CREATE TRIGGER GLAlert 
   ON  [' + @db_name + '].dbo.GL
   AFTER DELETE
AS 
BEGIN
	SET NOCOUNT ON;

    INSERT Admin.dbo.DBA_BudgetLog 
		SELECT GETDATE(), host_name(), db_name(), ''GL_Tbl'', suser_sname(), user_name(), app_name()

END'
	
PRINT @DynSQL

If use EXEC() instead of PRINT(), it gets this error:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'GO'.
Msg 111, Level 15, State 1, Line 4
'CREATE TRIGGER' must be the first statement in a query batch.

If I use PRINT and run in a quary, it works one by one!! I mean each batch for a database works but if I run whole the print out, it fails. Is it possible to make it work? I have about 100 databases and want to add this trigger to them.

Canada DBA

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-07-07 : 00:17:56

GO is not a valid keyword in sql. it's a batch separator interpreted by certain tools, such as SSMS, query analyzer, osql, sqlcmd, etc.

you need a separate call to exec() for each batch.

elsasoft.org

CanadaDBA

583 Posts

Posted - 2008-07-07 : 00:36:34

Even if I remove the GO still I get an error because the CREATE TRIGGER is not the first statement.

If I use EXEC('USE DB1') and then immediately after that use EXEC('CREATE TRIGGER...'), it doesn't create the trigger in DB1 database. So, I can't loop this technique too.

But there must be a way to repeat creating a trigger in a table in many databases.

Canada DBA

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-07-07 : 01:36:37

you could do this:

1. create the script to a file using sqlcmd or your other favorite method.
2. execute the script using sqlcmd, which *does* interpret the GO keyword correctly.

elsasoft.org

CanadaDBA

583 Posts

Posted - 2008-07-07 : 07:59:53

Jezemine, I didn't undrestand "interpret the GO keyword correctly"

Canada DBA

CanadaDBA

583 Posts

Posted - 2008-07-07 : 08:01:54

I have the triggers in my test environment and noticed the deletion are logged by inserting into the log table but TRUNCATE does not! It doesn't catch truncate table. :(

Canada DBA

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-07 : 10:02:07

To Capture Truncate table , you need to use DDL trigger Coz it's not DML operation.

CanadaDBA

583 Posts

Posted - 2008-07-08 : 08:47:44

quote:
Originally posted by sodeep

To Capture Truncate table , you need to use DDL trigger Coz it's not DML operation.

Can you explain more or give me a right link to read?

Canada DBA

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-07-08 : 10:02:16

do you know about BOL? it's the place to learn about all of SQL Server's features, including DDL triggers.

http://msdn.microsoft.com/en-us/library/ms190989.aspx

elsasoft.org

CanadaDBA

583 Posts

Posted - 2008-07-08 : 11:15:53

quote:
Originally posted by jezemine

do you know about BOL? it's the place to learn about all of SQL Server's features, including DDL triggers.

http://msdn.microsoft.com/en-us/library/ms190989.aspx

elsasoft.org

I don't like BOL and Microsoft links. It wants to cover EVERY situation and I found it mostly unhelpful. Searching Microsoft is worse. Search Google gives me faster results than Microsoft!

Canada DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources