After delete trigger

[gpsram]

Hi,



I am new to MS access.

I have to delete records from the 2 tables, when the parent record gets deleted. I have a delete button in my form wherein the user deletes the particular record from the parent table.

When the delete button is pressed, the parent record gets deleted from the table. But I need to write a after delete trigger on the parent table to be able to delete the relevant records from the other two child tables, based on the current parent id.

what code should I write ? and should I locate the records in the before delete trigger and then write the code to delete the child record in after delete trigger ?

Any help is appreciated.

Thanks
gps

[June7]

If you set relationships with Enforce Referential Integrity and Cascade Delete, Access will do the dirty work for you.

[gpsram]

Thanks for the reply. But I would want to use a trigger because my assignnment requires me to use a trigger. It will be of great help if you could assist.

[ItsMe]

I don't believe there is a CREATE TRIGGER available in Access SQL like there is in Transact SQL. If you need to do something at the table level then look into one of the three after events available to create a macro. You can find the After Update options when you open your table in data sheet view.

I have not created them before but was curious so I looked around a bit. You can create a named data macro that will automatically be associated to the table they are created in. You can also create a macro using one of the three after events. Combining these two approaches will allow you to associate one after event in a table to call other macros that are associated to other tables within the database.

I would start by creating a named macro that uses the LookupRecord action. Then, in another table, use one of the after update events to create a macro to employ the RunDataMacro action to call the previously created macro.

[June7]

The real trick is figuring out what event to use.
I do this with VBA code in a button Click event. Save the ID to a variable. Delete the parent record. Delete related records. I would delete the related records with

CurrentDb.Execute "DELETE FROM tablename WHERE [foreign key field] = " & intID

If you need to use table data macro, that is another matter. I don't use macros. AFAIK, macro cannot run SQL statement, have to run an Access query (DELETE action) object. However, macro can call a VBA custom function. That function could run the SQL.

[ItsMe]

I prefer the VBA route also. I maintain constraints using VBA exclusively. I did look at the macros and it seems possible to manage edits, deletes, and maybe a couple other things from macros associated to tables.

It seems using the After Events to create a macro, you can employ RunDataMacro to select previously created named macros that are associated to tables. These named macros appear in the Current Database tree on the right of the screen.

But yah, not sure what the limitations are or how fun it is to use this macro approach vs. VBA. It might be nice to use the macros if you have many forms that allow edits. I view Access as a desktop data base. I am not trying to create rules at the table level so I can offer connections to multiple third party apps.

[orange]

June,

. Delete the parent record. Delete related records. I would delete the related records with

CurrentDb.Execute "DELETE FROM tablename WHERE [foreign key field] = " & intID

You delete the child records first right? Otherwise you would leave orphans.

[June7]

Well, since there aren't any relationships enforced, doesn't really matter which happens first as long as they all get deleted, but probably safest to do the children first.