Delete Trigger Locks

[webuser]

I have created a delete trigger that basically adds a row to a 'log' table. Here is the Trigger:

ALTER TRIGGER [trgPatientsForDelete] ON [dbo].[Patients]
FOR DELETE
AS
Set NoCount On
INSERT INTO Patients_Deleted ([ID],LastUpdated,LastUpdatedBy) SELECT [ID],LastUpdated,LastUpdatedBy FROM Deleted

For some reason, when I try to delete/change records in the Patients_Deleted table directly after the trigger fires, I can't. It seems like the trigger continues to maintain some sort of lock on the Patients_Delted table. The error message I get is :

'The Microsoft Jet Database Engine stopped the process b/c you and another user are attempting to change the same data at the same time'

I am doing all of this through MS Access 2000 w/ the SQL Server ODBC Driver. Note: I CAN delete/edit the records in the patients_delted table through SQL Server Enterprise Manager, but not through Access.
Any ideas will be welcomed...Thanks.

 

[foxdev]

Web, can you try deleting a test record from Query Analyzer and see if the problem occurs? I'm guessing that there may be an open transaction (no COMMIT was issued) that is being mishandled by the interface, so trying this in QA may help isolate the problem. Robert Bradley

http://www.agrainofhope.org

http://www.lowcostprints.com

http://www.foxdev.com

 

[webuser]

Thanks for the reply. When I try to delete a row in the Patients_Deleted table through Query Analyzer, the 'delete' command on the shortcut menu (right-click) is disabled. It's enabled if I go to any other table. BUT like I said, I can delete the records from Enterprise Manager. I'm confused. Hope you can help.