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.
| Author |
Topic |
|
reddymade
Posting Yak Master
165 Posts |
 Posted - 2006-06-30 : 07:05:46
|
| Can you please tell me, we have almost 120 tables in the project all of them has log tables. we are using triggers to create records in the log table.we are doing on insert or on update create a record in the log table.I was speaking to someone and they said avoid triggers as much as possible and create records via insert query's. because they take the overhead of the sql server.We have spent lot of time on this project and if it is bad thing we will remove them, can someone please share some information what is best and whats not.Thank you very much for the information. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-30 : 07:10:42
|
| Triggers are a lot more efficient than they used to be.The good thing about what you are doing is that you can generate all the audit trail tables and triggers for almost no effort. You will have to weigh that against any performance gain by using another method.You might find that the triggers on some tables cause problems but you can address that on a case by case basis and leave the rest as triggers.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-01 : 05:57:36
|
We use triggers to store "audit data" on [i]most[//i] of the tables in our application.We could do it in the SProcs that save the data - but we might forget, and we definitely import data direct into the underlying tables "Now and Again", and someone might hack the database ...... so triggers suit me best.I'm not quite clear if you have one-log-table-PER-table, or one-log-table-for-ALL-tables.If its the second then IME that's a very bad design, and a very high overhead. We use the first  See also http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Triggers+to+audit+changesKristen |
|
|
|
wernerdejong
Starting Member
7 Posts |
Posted - 2006-07-04 : 10:13:46
|
| My advice is to simply go ahead with your solution and stress test it against the expected concurrent users / simulate a lot of db activity. If the database chokes you''ll might want to reduce some triggers on heavely used tables. Naturally, if the triggers are only on structure changes only then there should be almost no activity. (How often would one change a production database) If they are on insert / update's etc. You might want to consider using the transaction log for trigger notification or as mentioned seperate sql statements to inserts that info into the logging table. |
|
|
|
|
|
|
Use of triggers for on insert and update