Before/After snapshot of database

[Kate17]

Hi,

We have a web-based program that has a SQL server back end database. I want to be able to see what tables get updated when we add calls via the web-based front end. We are trying to customise this program but we need to see exactly what happens when you do anything in the front end. I am thinking along the lines of a snapshot before and after, and then cross checking to see which tables have changed.

Can anyone recommend the best way to do this?

Thanks Kate

 

[AWithers]

A very simplistic way is to add triggers to all your tables to write records into a table with columns

table, action, datemodified

records might look like

table1,update,28-Aug-2002 12:00:00
table2,append,28-Aug-2002 12:01:00
table3,delete,28-Aug-2002 12:02:00

you will then be able to analyse what tables are being hit and with what actions

Hope this helps

Andy

 

[tlbroadbent]

SQL Profiler is an excellent tool for viewing activity on a database. You can capture the activity as it happens. Profiler creates traces and displays the results in a GUI. It can also store the results in a table of a file. Traces can be filtered by database, login, host name and many other data columns. See details in SQL BOL. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Kate17]

I would prefer to try using the triggers, but I don't know how to set this up - I know where to set them up, but I don't know the code to put in there - could you help me please?

Thanks Kate

 

[AWithers]

Here is a some example code

create trigger audit_trigger_BaseData on BaseData
for DELETE,INSERT,UPDATE
as

DECLARE @count_inserted int
DECLARE @count_deleted int
SELECT @count_deleted = Count(*) FROM deleted
SELECT @count_inserted = Count(*) from inserted
IF @count_deleted = 0 and @count_inserted <> 0
/* Record has been ADDED */
BEGIN
--add code to insert data into the audit table
--e.g.
--insert into audittable values(&quot;thistablename&quot;,&quot;added&quot;,getdate())

END

IF @count_deleted <> 0 and @count_inserted <> 0

/* Record has been MODIFIED */
BEGIN
--add code to insert data into the audit table

--insert into audittable values(&quot;thistablename&quot;,&quot;modified&quot;,getdate())
END

IF @count_deleted <> 0 and @count_inserted = 0
/* Record has been DELETED */
BEGIN
--add code to insert data into the audit table
--insert into audittable values(&quot;thistablename&quot;,&quot;deleted&quot;,getdate())

END

you need to replace &quot;thistablename&quot; with the name of the table on which the trigger is created

I hope you can follow it and/or use the bits you need

Andy