July 11, 2008 at 11:36 am
My company's primary business is the management of residential housing and our primary application is a customized accounting software package that uses a SQL Server 2005 DB (set to a compatability level of SQL Server 2000/8.0). I know that it is popular and highly recommended that DDL be provided when asking questions specific to 1 or more DB objects however, due to a NDA (Nondisclosure Agreement) I am unable to provide such details on the structure of the database itself but I can provide some abstract information that should suffice for addressing this question.
In our system there are 2 tables used to store all financial data related to transactions (i.e. Payments, Receipts, etc). The structure of these 2 tables and the relationship between each is very much like the traditional ORDER & ORDER_DETAIL tables referenced in many SQL training material. For this post I'll use these identifiers to represent the tables thereby avoiding any conflicts with the NDA we signed.
We are looking at adding a DELETE Trigger to the ORDER table to capture transaction deletions which should not normally be occurring but based on reports from end users, appears to be occurring at random. We need to determine if deletions are actually occurring as well as when and by whom.
My question is what kind of impact on performance would a DELETE Trigger have on the system as a whole when UPDATE/INSERT commands are issued against the ORDER table? Unless a DELETE against the table is executed, is the effect on performance basically negligible?
We are under a short time frame and so testing this myself is not something I really can do and so I was hoping someone might have some insight into this.
Thanks in advance
Kindest Regards,
Just say No to Facebook!July 11, 2008 at 2:49 pm
A delete trigger will have effectively zero impact on inserts and updates. It will have an impact on deletes, obviously, but if you just dump the "deleted" table contents into a simple heap table, the impact per delete should be minimal.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 4:54 pm
Thank you GSquared
Kindest Regards,
Just say No to Facebook!Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply