trigger causes exception

Using D2005,
ClientDataSets and ADO,
SQL Server 2000, version 8.00.760

Sporadically we will get an exception in ApplyUpdates:
"record not found or changed by another user"

Google suggests that it may be releated to triggers, as more
people report this problem when they have triggers. But I
don't see any solutions.

We do have an OnUpdate trigger to record changes made by users.

All the changes consistently post successfully, both from the CDS
to the primary table and from the trigger to the change log.

The only hint of a problem is the exception.

If we add messages to the trigger, the exception goes away.
This trigger happens a lot, so we don't want it to be any longer than
necessary.

The trigger looks something like this:
CREATE TRIGGER updItemMICR ON [dbo].[ItemMICR] FOR UPDATE AS

IF UPDATE(ABA)
BEGIN
-- INSERT INTO ChangeHistoryLog values ('begin ABA')
INSERT INTO ChangeHistory (DateTime, Username, TableChanged,
ColumnChanged, KeyColumn, OldData, NewData)
SELECT CURRENT_TIMESTAMP, USER_NAME(), 'ItemMICR', 'ABA',
i.DocumentID, d.ABA, i.ABA FROM deleted as d, inserted as i
WHERE d.ABA <> i.ABA
-- INSERT INTO ChangeHistoryLog values (after ABA')
END
--INSERT INTO ChangeHistoryLog values ('done ABA')
IF UPDATE(Account)
BEGIN
-- INSERT INTO ChangeHistoryLog values ('begin Account')
INSERT INTO ChangeHistory (DateTime, Username, TableChanged,
ColumnChanged, KeyColumn, OldData, NewData)
SELECT CURRENT_TIMESTAMP, USER_NAME(), 'ItemMICR', 'Account',
i.DocumentID, d.Account, i.Account FROM deleted as d, inserted as
i
WHERE d.Account <> i.Account
-- INSERT INTO ChangeHistoryLog values ('after Account')
END
--INSERT INTO ChangeHistoryLog values ('done Account')
etc.

ABA is a 9 char field. If it is unchanged and the string is 9 char,
UPDATE(ABA) returns False.
If it is changed or the string is less than 9 char, UPDATE(ABA) returns
True.

If WHERE d.ABA <> i.ABA returns no rows, nothing gets inserted, even if the
string is less
than 9 char.

This is an example. All the UPDATEs work this way.

ChangeHistoryLog is a table created only to track the progress of this
trigger.
Commenting out the inserts brings the exception back. Even so, it is
sporadic.
Uncommenting the inserts makes the exception go away.

We get the exception most when at least one field contains some spaces and
are
editing another field.

Again, we always get the appropriate rows entered into the ChangeHistory
even if there is an exception.
--
Brad.

Vote for CodeRush in future versions of Delphi
http://qc.borland.com/wc/wc.exe/details?reportid=9138


.