A bug on ADO??? and a workaround...
-
I had to implement the following (relatevely simple) task:
I had two tables, say t1 and t2.
I need to create a trigger on t1 that:
for every Insert, Update on t1, delete all rows on t2.
the trigger looked something like this:
CREATE TRIGGER Delete_All_dvl ON [dbo].[aisDvc]
FOR INSERT, UPDATE
AS
delete from t2
if @@error <> 0
begin
rollback tran
end --if
return
===================================================
I create an ADO connection (using Delphi) and a grid pointing to the table t1.
Every time I tried to update t1 I get the error 'record not found or changed by another user' ... or 'the query try to update more than one row'... 🙁
I tested the above situation with DB-Express components and everything worked fine!!
I add (before the delete command) the line:
update t1 set t1.columnX = i.columnX
from t1 inner join inserted i on (i.columnX = t1.columnX)
which actually update column say X (a simple none primary key column) with the same value,.... i.e. refresh the row... and wakes up the sleeping ADO giant!!
then.... everything work fine even with ADO!!
I suppose the above bahviour is not a ... feature.
PS: I use SQL Server 2000, Delphi 7.0 & MDAC 2.7
------------
When you 've got a hammer, everything starts to look like a nail... -
Hi
Try this
set noconut on
delete from t2
set noconut off
if @@error <> 0
begin
rollback tran
set noconut off
end --if
return
-
sorry it sholud be
set nocount on
and
set nocount off
-
It seems it works Fine!!!
Thnx a lot Milan!!!
AJV
------------
When you 've got a hammer, everything starts to look like a nail...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply