SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Tracking the Inserts,Updates to Table
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 6

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  03:08:21  Show Profile  Reply with Quote
hi,

I need to track the changes made to table like insertion, deletion and updation and put them into a new table. No triggers pls. How to do that

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  03:41:51  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Make the necessary changes to the existing CRUD procedures.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  04:27:14  Show Profile  Reply with Quote
Hi Peso,

The assignment given to me is to log user actions(i.e. editing profile, updating orders etc) into Db. So i need to get these stuffs loaded into logtable through table tracking method(insert, update, etc). I was instructed not to use triggers as it is cost expensive and also i should not lay my hands on existing procedures. what can i do. Pls help me out
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  04:36:25  Show Profile  Visit SwePeso's Homepage  Reply with Quote
If your assignments doesn't allow you to

1. Use trigger
2. Change existing code

tell your boss you can't do it, unless you install Microsoft SQL Server 2008 Enterprise Edition which supports CDC (Change Data Capture).



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  04:40:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Well, there is one option left.
You can add a new column to the tables you want to monitor, as

ALTER TABLE MyTable
ADD MyColumn DATETIME AS (GETDATE())

But that can break existing code and work for INSERT only, not update.

Another option is to keep a complete copy of database somewhere else and have a job run a stored procedure which compares the two databases. It will be very slow.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  04:40:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why do you think a proper written trigger will be slow?


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 04/21/2009 04:41:18
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  04:43:30  Show Profile  Reply with Quote
Thanks Peso,

I will ask for enterprise edition in my company. anyway, how did Sql developers actually handled those User Actions into table in earlier Sql versions. Will they be inserting to a log table manually in stored procedure each time record gets inserted, updated. That will be time-consuming right. May be if i dont get enterprise edition getting installed in my machine, ur answer may help.
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  04:47:03  Show Profile  Reply with Quote
I dont why not to use triggers as suggested by my PL. may be there are 100 tables. calling triggers for each table will be time consuming? ur suggestions
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  05:08:01  Show Profile  Reply with Quote
Hi Peso,
I have only Sql Server Express Edition. CDC is available only in Sql server Enterprise Edition. Then how can i keep track of user actions into DB. Other than triggers. Pls help me. Ur suggestions r valuable
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  05:16:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Have your PL email me and I can explaing a thing or two for him/her about triggers.



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  05:35:46  Show Profile  Reply with Quote
Yes Sure. thanks
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  06:25:07  Show Profile  Reply with Quote
Hi Peso,

Can i go for 1 table -> 1 history table or 1 history table for entire db to store old values.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 04/21/2009 :  07:20:57  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
Post probably continuing here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=124218



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  07:34:55  Show Profile  Reply with Quote
Triggers are bad for debugging (in a properly written application, triggers are not needed. It's just a tool for lazy developers/dbas), I wouldn't say they affect performance.

I'll quote my other post:
quote:
I'd use one history table for each database with the following columns:
Id, TargetTable, ModifierId, RowData, ModifierDate, CommandType

Where ModifierId is the user who invoked the modification of the row, and RowData is a XML data column containing the whole row compiled into an XML format. The CommandType is either Update or Delete.


Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/21/2009 :  07:48:24  Show Profile  Visit spirit1's Homepage  Reply with Quote
triggers have their use and they can be a powerful tool even in a properly written application.
of course they can cause a perf bottleneck if they use a cursor instead of joining to inserted and deleted pseudo tables.

and yes they are really bad for debugging


___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

Edited by - spirit1 on 04/21/2009 07:48:52
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  07:59:22  Show Profile  Reply with Quote
The topic poster said they cannot use triggers and cannot change the main code.

In that case, there is one last approach left. If your application is using a data access utility (as all properly written applications should) or a centralized data CRUD method, you can modify it so that at the point of an Update/Delete, you can parse the incoming SQL query and get the ID and table name then perform backup of that row before running the incoming query. With this approach, there is only one place to modify the code and the rest of the application is unaffected. The only negative thing about this approach is that you will be using a modified data access utility that is separate from the rest of your applications.

Edited by - whitefang on 04/21/2009 08:01:50
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  08:06:15  Show Profile  Reply with Quote
hi,

i used ur approach by placing the previous query records as xml.
HistoryTable
HistoryId PreviousRec(Xml)
1 <SalesTable salesid='1' amt='500'/><SalesTable salesid='1' amt='700' />

say if i have modified the table records 100 times then 100 nodes will be created for historyid. will that degrade performance. whats suitable size suggested for xml datatype in each row.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  08:09:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And you think this will be faster than a trigger?
And for debugging thingy. Well, if you use SQL Server 2008, it's incorporated into the debugger.
CREATE TABLE	tblA
		(
			i INT
		)
GO
CREATE TABLE	tblB
		(
			j INT
		)
GO
CREATE TRIGGER trgA ON tblA AFTER INSERT
AS INSERT tblB SELECT i FROM inserted
GO
INSERT tblA SELECT 1
GO
SELECT * FROM tblB
GO
PRINT 'Done'
GO



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  08:14:39  Show Profile  Reply with Quote
quote:
Originally posted by dineshrajan_it

hi,

i used ur approach by placing the previous query records as xml.
HistoryTable
HistoryId PreviousRec(Xml)
1 <SalesTable salesid='1' amt='500'/><SalesTable salesid='1' amt='700' />

say if i have modified the table records 100 times then 100 nodes will be created for historyid. will that degrade performance. whats suitable size suggested for xml datatype in each row.



No, that won't degrade performance. If you're using an object model, you can even serialize the entities directly into the XML data column. Also, make sure to check that you don't have any concurrency issues depending on your application reqs.

You really don't have to worry about performance unless you have like over 10 million records. And that is when you should consider archiving.

Edited by - whitefang on 04/21/2009 08:18:35
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  08:16:21  Show Profile  Reply with Quote
quote:
Originally posted by Peso

And you think this will be faster than a trigger?
And for debugging thingy. Well, if you use SQL Server 2008, it's incorporated into the debugger.



Will his client notice a millisecond difference? I think not. We can all assume that the application is not enterprise grade. He already said he cannot use triggers.

Edited by - whitefang on 04/21/2009 08:19:16
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  08:42:40  Show Profile  Reply with Quote
I have 120 tables so using trigger for all 120 tables will be i dont know?
and this single history table theory for an entire db. with previous records as xml format. we can actually avoid keeping history table for each table and also can keep a centralised single history table for entire db. any thoughts will be helpful. thanks
Go to Top of Page
Page: of 6 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000