Record lose - 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

Record lose

Author

Topic

edwinastono
Starting Member

3 Posts

Posted - 2007-05-24 : 22:28:00

Dear all,

I'm using SQL Server 2005 as my backend application. I had strange experience on it, sometime some of my records suddenly disappear without any deletion action.I've check the log files and I've already set a trigger on deletion to copy the deleted record to a history table, but I found nothing. Are there anybody that have same experience like me ?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-05-24 : 23:17:33

Losing records unexpectedly would be considered a bit of a bug in a database engine. I'm pretty sure they would have found that in testing.
It will be your application for sure. Probably an unmatched transaction rolling back instead of committing by the sound of it. The record appears to be there in the session and then mysteriously 'goes away' when rolled back - hence no deletion trigger.

edwinastono
Starting Member

3 Posts

Posted - 2007-05-25 : 00:06:30

Thanks for your answer. But I'm a little bit confuse, so which one is correct, bug in database engine or my application fault ?
Fyi, there are no commit or rollback command in my application.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-05-25 : 02:55:15

I dont know if it's the same in 2005 but in earlier versions of SQL Server a delete trigger must make sure that they are handling all the records deleted otherwise in these sort of circumstances it can look as though only one record was deleted. so it oculd be that it is being deleted and that the delete trigger is not working as you think i.e. an application fault

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Kristen
Test

22859 Posts

Posted - 2007-05-25 : 03:21:03

"so which one is correct, bug in database engine or my application fault"

The likelihood of it being in the database engine is as close to zero as you get, so I'm afraid its your application (or something else blundering into your database).

A trigger to Audit the deletions is good, but as Elwoos points out you say "I've already set a trigger on deletion to copy the deleted record to a history table" where in fact you need to make the trigger copy deleted records to your audit table:


CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
AFTER DELETE
AS
SET NOCOUNT ON

INSERT dbo.MyAuditTable
SELECT	[MyAuditDate] = GetDate(),
	D.*
FROM	deleted AS D

Your MyAuditTable table needs to be identical to your main MyTable, EXCEPT that you need to add a DateTime column on the front to whole the "audit" time - i.e. the date/time when the record was deleted.

Kristen

edwinastono
Starting Member

3 Posts

Posted - 2007-05-25 : 04:35:33

It's glad to know that it's not the database engine fault, because if the data lose is cause by the bugs from SQL Server then I have to replace the database and rewrite my application. Fyi, records lose only happen in one table, here is my trigger that I use to send the deleted record into history table:

ALTER TRIGGER [dbo].[del_ivdet] ON [dbo].[Ivdetail]
FOR DELETE
AS
DECLARE @id varchar(20),@sys bigint, @sysdet bigint, @id_prod2 varchar(15)
SELECT @sys = sys, @sysdet = sysdet, @id = id, @id_prod2 = id_prod2 FROM DELETED
INSERT INTO ivdet_his VALUES (@sys, @sysdet, @id, @id_prod2, getdate())

Btw, is it necessary to put COMMIT command after we do some records manipulation ?
Thank you

Kristen
Test

22859 Posts

Posted - 2007-05-25 : 06:47:41

Your trigger won't work. You are making the specific mistake I pointed out in my earlier post and your trigger can only handle a single record, so I suggest you change your trigger as per the example I provided.

"is it necessary to put COMMIT command after we do some records manipulation "

Only if you issues a BEGIN TRANSACTION, or you are using Implicit Transactions. So basically the answer is most probably "No"

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources