Microsoft KB Archive/275064

= ACC2002: Trigger Returns an Error That User Has Deleted Record or Changed Primary Key =

Article ID: 275064

Article Last Modified on 1/26/2005

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



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 &quot;Symptoms&quot; 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



Steps to Reproduce the Behavior
 Start Access 2002, and then in the Task pane, click Project (New Data). In the File New Database dialog box, click Create. Access begins to create Adp1.adp. In the Microsoft SQL Server Database Wizard, enter the name of your SQL Server, Login ID, and Password, and then click Next and Finish. On the File menu, point to Get External Data, and then click Import. In the Import dialog box, click Northwind.mdb, and then click OK.</li> In the Import Objects dialog box, click the Suppliers and the Products tables, and then click OK.</li> Open the Products table in Design view, set ProductID as the Primary key, and then save changes and close the table.</li> Open the Suppliers table in Design view, set SupplierID as the Primary Key, and then on the View menu, click Properties.</li> Click the Relationships tab, and then click New.</li> In the Primary key table list, click Suppliers.</li> In the Foreign key table list, click Products.</li> 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.</li> Click to clear the Enforce relationship for INSERTs and UPDATEs check box.</li> Close the Properties dialog box, and then close the Suppliers table, saving all of your changes when you are prompted.</li> Click Queries in the Database window, and then click New.</li> In the New Query dialog box, click Create Text Stored Procedure, and then click OK.</li>  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 &quot;DelSomeRecs&quot; AS DELETE FROM Products WHERE SupplierID = 29 </li> 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.</li> Right-click Suppliers, and then click Triggers.</li> In the Triggers for Table: Suppliers dialog box, click New.</li>  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 </li> <li>Open the Suppliers table in Datasheet view, and then browse to SupplierID 29.</li> <li>Select this record, and then click Delete Record on the Edit menu.</li> <li>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 &quot;Symptoms&quot; 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.</li></ol>

<div class="references_section">