ACC2002: Trigger Returns an Error That User Has Deleted Record or Changed Primary Key (275064)



The information in this article applies to:

  • Microsoft Access 2002

This article was previously published under Q275064
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you try to delete a record from a table that contains a trigger that is designed to delete records from another table, you receive the following error message:
Another user or application has deleted this record or changed the value of its primary key.

CAUSE

You have created tables that are related in a one-to-many relationship. You have also created a trigger on the one-sided table. The trigger is designed to automatically remove child records from the many-sided table whenever a parent record is removed from the one-sided table. If child records exist on the many-side, the trigger functions as expected. But if there are no child records to match the parent record, the trigger fails, and you receive the error message that is mentioned in the "Symptoms" section of this article.

RESOLUTION

Use the following trigger:
Alter Trigger Suppliers_Trigger1
On Suppliers
For Delete
As
If (SELECT COUNT(*) FROM Products, Deleted WHERE Products.SupplierID = Deleted.SupplierID) > 0
BEGIN
  DELETE Products FROM Deleted, Suppliers WHERE Products.SupplierID = Deleted.SupplierID
End
				

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Start Access 2002, and then in the Task pane, click Project (New Data).
  2. In the File New Database dialog box, click Create. Access begins to create Adp1.adp.
  3. In the Microsoft SQL Server Database Wizard, enter the name of your SQL Server, Login ID, and Password, and then click Next and Finish.
  4. On the File menu, point to Get External Data, and then click Import.
  5. In the Import dialog box, click Northwind.mdb, and then click OK.
  6. In the Import Objects dialog box, click the Suppliers and the Products tables, and then click OK.
  7. Open the Products table in Design view, set ProductID as the Primary key, and then save changes and close the table.
  8. Open the Suppliers table in Design view, set SupplierID as the Primary Key, and then on the View menu, click Properties.
  9. Click the Relationships tab, and then click New.
  10. In the Primary key table list, click Suppliers.
  11. In the Foreign key table list, click Products.
  12. Below the Primary key table and Foreign key table lists is a 2-column list box. On the first row of this list box, click SupplierID in the first column, and click SupplierID in the second column.
  13. Click to clear the Enforce relationship for INSERTs and UPDATEs check box.
  14. Close the Properties dialog box, and then close the Suppliers table, saving all of your changes when you are prompted.
  15. Click Queries in the Database window, and then click New.
  16. In the New Query dialog box, click Create Text Stored Procedure, and then click OK.
  17. Enter the following transact SQL, and then close the procedure, clicking Yes to save changes, and clicking OK to save the procedure with the default name of DelSomeRecs:
    CREATE PROCEDURE "DelSomeRecs"
    AS
    	DELETE FROM Products
    	WHERE SupplierID = 29
    					
  18. Double-click DelSomeRecs, and then click OK in the message box.

    NOTE: All parent records in the one-sided table (Suppliers) have at least one or more child records in the many-sided table (Products). This procedure will remove all child records from the many-sided table where the SupplierID equals 29. Therefore, SupplierID 29 in the one-sided table will no longer have any matching records in the many-sided table.
  19. Right-click Suppliers, and then click Triggers.
  20. In the Triggers for Table: Suppliers dialog box, click New.
  21. Enter the following transact SQL, and then close the trigger window, clicking Yes to save changes, and clicking OK to save with the name of Suppliers_Trigger1:
    CREATE TRIGGER Suppliers_Trigger1
    ON Suppliers
    For Delete
    AS
    DELETE Products FROM Deleted, Suppliers WHERE Suppliers.SupplierID = Deleted.SupplierID
    					
  22. Open the Suppliers table in Datasheet view, and then browse to SupplierID 29.
  23. Select this record, and then click Delete Record on the Edit menu.
  24. Click Yes when you are prompted if you are sure that you want to delete the record.

    Note that you receive the error message that is mentioned in the "Symptoms" section of this article. This error occurs because there are no child records matching the parent record (SupplierID 29) that is being deleted from the Suppliers table.

REFERENCES

For more information about triggers, click Microsoft Access Help on the Help menu, type create a trigger in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Modification Type:MinorLast Reviewed:1/26/2005
Keywords:kberrmsg kbpending kbprb KB275064