Deleting Records Problem - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Deleting Records Problem

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-12-06 : 05:10:39

Hi

I am deleting records from three tables and in one of the tables I keep getting one of the records left over. I can delete this by running the delete statement again but am trying to work out why it is not doing it the first time.

The tables do not have any physical links but there is a common number that logically links the records. My script is like this:

delete from LogRecords
where OrderNumber = '12345678'
GO

delete from OrderHold
where OrderNumber = '12345678'
GO

delete from Orders
where OrderNumber = '12345678'
GO

In the logrecords table there are 4 messages like this:


LogID  LogMsg  
=====  ===============
1      Order Registered
2      Order Printed
3      Order on Hold - Incorrect Invoice
4      Order on hold not verified

In the OrderHold there is one record:

HoldID  HoldReason
======  =================================
1       Order on Hold - Incorrect Invoice

And one record in order:

OrderID Order 
======= ==========
1       Staple Gun

When I run script it deletes all but this line:

LogID  LogMsg  
=====  ===============
3      Order on Hold - Incorrect Invoice

Then if I run the part for deleting from Log it clears this record. I want to be able to run the script once and delete all records, and am wondering why this record is not being removed?

Since there is no real dependancies I cannot see why this is not being removed.

G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 05:16:40

delete from LogRecords
where OrderNumber = '12345678'

That should error as there is no OrderNumber in the table.
Is there a trigger on a table which inserts this row when you do the delete (or a trigger to prevent the delete)?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-12-06 : 05:32:48

Sorry I should have been clearer, each table has ordernumber column in it:


LogID  LogMsg                              OrderNumber
=====  ==================================  ============
1      Order Registered                    12345678
2      Order Printed                       12345678
3      Order on Hold - Incorrect Invoice   12345678
4      Order on hold not verified          12345678
 
In the OrderHold there is one record:

HoldID  HoldReason                         OrderNumber
======  =================================  ===========
1       Order on Hold - Incorrect Invoice  12345678

And one record in order:

OrderID Order       OrderNumber
======= ==========  ===========
1       Staple Gun  12345678

When I run script it deletes all but this line:

LogID  LogMsg                              OrderNumber
=====  ==================================  ===========
3      Order on Hold - Incorrect Invoice   12345678

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 05:41:49

Still think it's probably a trigger (if you don't get an error).
What happens if you change the order of the deletes

delete from OrderHold
where OrderNumber = '12345678'
GO

delete from Orders
where OrderNumber = '12345678'
GO

delete from LogRecords
where OrderNumber = '12345678'
GO

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-12-06 : 06:41:29

[quote]Originally posted by nigelrivett

Still think it's probably a trigger (if you don't get an error).
What happens if you change the order of the deletes

delete from OrderHold
where OrderNumber = '12345678'
GO

delete from Orders
where OrderNumber = '12345678'
GO

delete from LogRecords
where OrderNumber = '12345678'
GO

Hi

There is a LogDeleteTrigger and it puts comments in system log to show what has been deleted. Not sure how this can affect actually deleting a record?

What is your logic for changing the order of deletes?

G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 06:46:35

I suspect the trigger will insert the row in the log table when the row is deleted from the orders table.
If the log delete is last then it will get deleted as well.

Is logid an identity? If so are you sure the value doesn't change with the first delete (to 5 maybe)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-12-06 : 06:46:41

Hi

I've just figured this out it is a trigger in the orderhold table that puts a record back in the log table as this holds all other log comments so it is deleting the log message and then inserting a record to say that the hold has been removed.

G

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2010-12-06 : 07:23:40

Thanks for all helpful points suggested.

G

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-06 : 07:26:24

quote:
Originally posted by Grifter

Hi

I've just figured this out it is a trigger in the orderhold table that puts a record back in the log table as this holds all other log comments so it is deleting the log message and then inserting a record to say that the hold has been removed.

G

Think that's what I said was happening.
You can resolve it by altering the order of the deletes.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources