Record Locking in Access ADP

[IndianaJonesWB]

I'm trying to figure out what happened.

I have have two Tables in SQLServer called "Contacts", and "Jobs"

Whenever someone changes a record in either table I update two fields
called "Modby" and "Moddt" with a trigger. The Trigger may also
contain other things as well.

Here's the wierd thing.

If the Modby Moddt statement is the first statement in the trigger I
don't get a "record changed by another user" but if it comes later in
the trigger I do.

I think it has something to do with the LockDelay Key but I'm not sure.

Any Ideas?

[IndianaJonesWB]

Here's some more detail.

This Trigger causes the problem:
CREATE TRIGGER RnC_CONT_Contacts_Mod
ON dbo.RnC_CONT_Contacts
FOR Insert, update
AS

-- Update Add by and Add Date
update m set
cnt_addby=dbo.fnc_getcurrentuserid(),
cnt_adddt=getdate()
from (rnc_cont_contacts m inner join inserted i on m.cnt_id=i.cnt_id)
left join deleted d on m.cnt_id=d.cnt_id
where isnull(d.cnt_id,0)=0

-- Update Mod by and Mod Date
update m set
cnt_modby=dbo.fnc_getcurrentuserid(),
cnt_moddt=getdate()
from rnc_cont_contacts m inner join inserted i on m.cnt_id=i.cnt_id

But modified like this the problem goes away:
This Trigger causes the problem:
CREATE TRIGGER RnC_CONT_Contacts_Mod
ON dbo.RnC_CONT_Contacts
FOR Insert, update
AS

-- Update Mod by and Mod Date
update m set
cnt_modby=dbo.fnc_getcurrentuserid(),
cnt_moddt=getdate()
from rnc_cont_contacts m inner join inserted i on m.cnt_id=i.cnt_id

-- Update Add by and Add Date
update m set
cnt_addby=dbo.fnc_getcurrentuserid(),
cnt_adddt=getdate()
from (rnc_cont_contacts m inner join inserted i on m.cnt_id=i.cnt_id)
left join deleted d on m.cnt_id=d.cnt_id
where isnull(d.cnt_id,0)=0

[IndianaJonesWB]

I know this wasn't clear. Maybe my typing. Let me try again.

I have one Table for Contacts.

Whenever a record changes I use the triggers above to update the
cnt_modby and cnt_moddt fields.
Whenever a record is added I use the same triggers to update the
cnt_addby and cnt_adddt fields.

If I put the Mod by fields first there's no problem but if I put the
Addby fields first I get a message that the record was changed by
another user.

Why would just changing the order make a difference?

[Malcolm Cook]

The first thing I would sort out is trigger recursion.

Do you have nested triggers turned off?

If not, I would expect recursion problems with your internal updates, which
I would catch with

IF ( (SELECT trigger_nestlevel( object_ID('RnC_CONT_Contacts_Mod') ) ) > 0 )
BEGIN
--track mod by and addby code here
END

"IndianaJonesWB" <jr***@lcra.org> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Here's some more detail.

This Trigger causes the problem:
CREATE TRIGGER RnC_CONT_Contacts_Mod
ON dbo.RnC_CONT_Contacts
FOR Insert, update
AS

-- Update Add by and Add Date
update m set
cnt_addby=dbo.fnc_getcurrentuserid(),
cnt_adddt=getdate()
from (rnc_cont_contacts m inner join inserted i on m.cnt_id=i.cnt_id)
left join deleted d on m.cnt_id=d.cnt_id
where isnull(d.cnt_id,0)=0

-- Update Mod by and Mod Date
update m set
cnt_modby=dbo.fnc_getcurrentuserid(),
cnt_moddt=getdate()
from rnc_cont_contacts m inner join inserted i on m.cnt_id=i.cnt_id

But modified like this the problem goes away:
This Trigger causes the problem:
CREATE TRIGGER RnC_CONT_Contacts_Mod
ON dbo.RnC_CONT_Contacts
FOR Insert, update
AS

-- Update Mod by and Mod Date
update m set
cnt_modby=dbo.fnc_getcurrentuserid(),
cnt_moddt=getdate()
from rnc_cont_contacts m inner join inserted i on m.cnt_id=i.cnt_id

-- Update Add by and Add Date
update m set
cnt_addby=dbo.fnc_getcurrentuserid(),
cnt_adddt=getdate()
from (rnc_cont_contacts m inner join inserted i on m.cnt_id=i.cnt_id)
left join deleted d on m.cnt_id=d.cnt_id
where isnull(d.cnt_id,0)=0