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
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 6

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  08:45:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
I am so tired of your "Enterprise" ramblings...


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

Edited by - SwePeso on 04/21/2009 08:45:33
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

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

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



You have to write 120 extra queries VS editing one place in your application layer
You have to maintain 120 extra queries VS doing nothing (if you follow object model)
You have to maintain 120 extra tables VS doing nothing
You have to archive 120 extra tables VS archiving 1 table
You have to backup, the database has to optimize 120 extra tables, fragmentation VS 1 table

Million rows for a single table is not a problem. When you get to 10 million rows in 5 years down the road, it may impact performance....that is when you should look into archiving.

With my design, everything can be centralized/automated. Reporting, querying, Restoring, Backups....they can all be centralized and automated. Not possible with the idiot design (having a history table for each main table).


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

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  08:53:27  Show Profile  Reply with Quote
Thanks Peso, whiteFang and others for ur valuable suggestion of yours.

I will be using one of ur suggestions.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/21/2009 :  08:53:55  Show Profile  Visit spirit1's Homepage  Reply with Quote
anwser for the original poster:
since you can't use triggers there's nothing that you can do to ensure proper logging. As Peso said you can use CDC on sql server 2008.
logging from the application is a viable option only if you're accessing your database only from that one application.

maybe you should tell us why triggers aren't an option?

also take a look at this:
http://www.sqlteam.com/article/centralized-asynchronous-auditing-with-service-broker

since service broker is totaly async there is no perf problem.
___________________________________________________________________________
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 08:57:45
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  08:55:30  Show Profile  Reply with Quote
Maybe because he already has 120 tables and that's 120 extra triggers and queries he has to write which is a significant application modification and requires a significant budget and testing etc.


And also triggers are bad for debugging and only good if you write your applications using notepad and windows 3.1.

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  08:58:38  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Oh, spirit... Don't you know "Service Broker" is "cave man" technology?

See last posts here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123776&whichpage=6

According to OP he was instructed by manager to NOT use triggers
quote:
I was instructed not to use triggers as it is cost expensive



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

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/21/2009 :  08:58:50  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes i'm aware of the 120 limit, but as i said without triggers full and complete logging is imposible (not counting CDC).

___________________________________________________________________________
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 08:59:38
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  09:00:18  Show Profile  Reply with Quote
Impossible to a DBA perhaps, but not to a developer. Application layer is where he can implement the change and it works much better (you can have full logging).

Edited by - whitefang on 04/21/2009 09:02:44
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/21/2009 :  09:04:18  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes of course. but you can only have full logging for the data that comes through your application. i have yet to see an enterprise db in today world that works only with one app.

speaking of logging, what do you use? log4net?

___________________________________________________________________________
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 09:04:42
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  09:04:49  Show Profile  Visit SwePeso's Homepage  Reply with Quote
120 triggers are no problem with t-sql scripting.

And WhiteFang, how can you manage logging in application layer when tables are edited directly?
Heard of SOX?



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 :  09:05:25  Show Profile  Reply with Quote
Hi,

i have convinced my PM to some extent abt using triggers. He is in null state right now.

ok what abt storing previous records as xml nodes to xml datatype.
so i can have a single entry for table in history table

historyid Tablename prevReco
1 SalesTable <SalesTable Amt='100'><SalesTable Amt='200'>
2 orderTable <Order Amt='23'/><Order amt='567'/>

so i just have 1 records for each table.

Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  09:07:03  Show Profile  Reply with Quote
We have enterprise DBs that work with multiple apps. The difference is that each app uses a centralized data access utility. So if we needed to implement a change like this, it would be implemented in the data access utility which would apply to all applications (this feature could be turned on and off). That is why I recommended that this approach be implemented in the data access utility or centralized data CRUD method.

Our object model is build in such a way that it self-logs itself and provides full reporting capability as well as security analysis, that's intelligent design.

If tables are being edited directly, you got a big problem or a security hazard. I have yet to hear a case where someone edits data directly in an enterprise environment but maybe in yours, it's acceptable.

Edited by - whitefang on 04/21/2009 09:10:05
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/21/2009 :  09:08:35  Show Profile  Visit spirit1's Homepage  Reply with Quote
dineshrajan_it, look at the article i've linked to.
even if you don't use service broker you can still use the xml stuff to save all loggin in one table.


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

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/21/2009 :  09:10:08  Show Profile  Visit spirit1's Homepage  Reply with Quote
quote:
Originally posted by whitefang

We have enterprise DBs that work with multiple apps. The difference is that each app uses a centralized data access utility. So if we needed to implement a change like this, it would be implemented in the data access utility which would apply to all applications (this feature could be turned on and off). That is why I recommended that this approach be implemented in the data access utility or centralized data CRUD method.

If tables are being edited directly, you got a big problem or a security hazard. I have yet to hear a case where someone edits data directly in an enterprise environment but maybe in yours, it's acceptable.



well a centralized db layer is great.
how do you handle bulk inserts, ETL processes and stuff like that? do you put that through your layer too?

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

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  09:12:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No, it's not acceptable, but that's also what SOX is about.
You can never deny SA or admin to edit the tables.
No matter what rules apply, some grudged employee with admin rights can always make a mess.



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 :  09:12:30  Show Profile  Reply with Quote
quote:
Originally posted by dineshrajan_it

Hi,

i have convinced my PM to some extent abt using triggers. He is in null state right now.

ok what abt storing previous records as xml nodes to xml datatype.
so i can have a single entry for table in history table

historyid Tablename prevReco
1 SalesTable <SalesTable Amt='100'><SalesTable Amt='200'>
2 orderTable <Order Amt='23'/><Order amt='567'/>

so i just have 1 records for each table.





That's not what I said.

I said have a single history table and EACH "transaction" should be a record!
So it should be:
historyid Tablename prevReco
1 SalesTable <SalesTable Amt='100'>
2 SalesTable <SalesTable Amt='200'>
3 orderTable <Order Amt='23'/>
4 orderTable <Order amt='567'/>

Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/21/2009 :  09:14:11  Show Profile  Reply with Quote
quote:
Originally posted by spirit1

quote:
Originally posted by whitefang

We have enterprise DBs that work with multiple apps. The difference is that each app uses a centralized data access utility. So if we needed to implement a change like this, it would be implemented in the data access utility which would apply to all applications (this feature could be turned on and off). That is why I recommended that this approach be implemented in the data access utility or centralized data CRUD method.

If tables are being edited directly, you got a big problem or a security hazard. I have yet to hear a case where someone edits data directly in an enterprise environment but maybe in yours, it's acceptable.



well a centralized db layer is great.
how do you handle bulk inserts, ETL processes and stuff like that? do you put that through your layer too?


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





Bulk inserts go into a threaded DB utility where it parses the incoming SQL and retrieves the record IDs needed to backup.

Edited by - whitefang on 04/21/2009 09:15:34
Go to Top of Page

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 04/21/2009 :  09:16:07  Show Profile  Reply with Quote
Thanks spirit,
that's the spirit man. i will look into documented. i think ur solution can solve my problem.

Keeping a separate db for audit is indeed a good concept.
thanks man again.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/21/2009 :  09:16:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Parses incoming SQL? How is that relevant to BULK INSERT?



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

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/21/2009 :  09:16:13  Show Profile  Visit spirit1's Homepage  Reply with Quote
@whitefang:
intersting. don't know if i'd go for this but hey... it's your app

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!
Go to Top of Page
Page: of 6 Previous Topic Topic Next Topic  
Previous Page | 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.16 seconds. Powered By: Snitz Forums 2000