This is causing me an awful amount of hastle!!!! I have a delphi program which talks to a SQL SERVER database over an ODBC link.... so far so good! ...... The program interacts with the user and, depending on changes made, creates a few SQL statements which run as one transaction.
It gets a bit tricky here but, what follows are the statements I'm trying to run as ONE transaction.
DELETE FROM COLLECTIONS WHERE ID = 24
DELETE FROM DELIVERY WHERE ID = 24
DELETE FROM LOADS WHERE LOAD_NUMBER = '4'
UPDATE dbo.DIARY SET LORRY_ID = 1, DRIVER_ID = 0, TEXT = 'Hello World', DATE = '12/08/98' WHERE ID = 3
DECLARE @@MYLOADID INTEGER
INSERT INTO LOADS (LOAD_NUMBER, TRAILER_ID, TYPE_OF_GOODS, COMPLETED, CUSTOMER_ID, DIARY_ID) VALUES ('4', 0, 'COMPUTERS', 'N', 2, 3)
SELECT @@MYLOADID = @@IDENTITY
INSERT INTO DELIVERY (LOAD_ID, ADDRESS_ID, DATE, DELIVERED, DIARY_ID) VALUES (@@MYLOADID, 1, '12/08/98', 'N',3)
INSERT INTO COLLECTIONS (LOAD_ID, ADDRESS_ID, DATE) VALUES (@@MYLOADID, 3, '12/08/98')
UPDATE DELIVERY SET DELIVERED = 'Y' WHERE (LOAD_ID = (SELECT ID FROM LOADS WHERE LOAD_NUMBER = '4')) AND (ADDRESS_ID = 1)
Now, just so you know there is a certain amount of referential integrity in the database BUT nothing which should cause the problem I'm having.
At the time these statements are run the relevant records are available in the database for deletion, update, etc.
The PROBLEM (now we get to it) is this. Before I run these statements there IS some text in the 'TEXT' column in the DIARY table (Let's say "HERE IS THE TEXT") but when I run the above statements in this order the text gets deleted instead of UPDATED to "Hello World".
If I run the Update statement on its own it works BUT when I run the statements in the above order it dosen't. I DON'T get any error message..
Can some genius help???? PLEASE! before I go round the twist..