April 28, 2009 at 8:39 am
I have a table that i am deleting a row from. When i delete data from this row, a related row in a seperate table is also deleted.
I have checked triggers, and they are only ON UPDATE, and no deleting goes on in them.
I have checked the tables in the triggers and they dont delete any data either.
There are no tables with cascade delete enabled on the db.
How could this data possibly be deleted. all suggestions welcome.
Thanks.
April 28, 2009 at 9:00 am
Hi....
One possible way to detect where it is going wrong is paste the SQL String in the Query Analyzer and click on the display estimated execution plan.
If you follow the output of the estimated query output it will reflect which all tables are getting affected. PUtting the curson on the icons will probably show the statement being getting executed.
On the other hand you can use the profiler to find out what all SQL Statements are running on the background.
YOu can run this way also (suggestion) :
BEGIN TRANS
<>
ROLLBACK
Look at the profiler strings. It will definitely come out with the rogue statement which is giving you the sleepless nights.
With best wishes that you solve the issue at the earliest and get back to your normal sleep :
This is AD signing off ....
GOD Bless you and all ...
April 28, 2009 at 9:26 am
I tried using profiler but it showed up nothing. Thats why i am confused as to how the row is being deleted in the 2nd table.
April 28, 2009 at 9:27 am
How are you doing the deletes?
April 28, 2009 at 9:34 am
is it instant? or is it several minutes later that the related data is removed
just thinking you might have a sql server agent job that purges related data
it can only be one of a few other things
enforced foreign key
delete trigger
or if you have a delete trigger but that trigger does not delete e.g it insterts into an audit table then check if your audit table has triggers on (nested triggers - yuk)
MVDBA
April 28, 2009 at 9:51 am
found the reason, there was a constraint on the 2nd table, which has a cascade delete enabled.
It wasnt showing up in profiler so i was stumped. I can sleep now, thanks for the help folks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply