I know that in MSSQL when you load a trigger onto a table, that you have access to a "cache table" of the record(s) to be operated on (Update, Insert, Delete).

Is there a similar mechanism to utilize from Access? If not, if i want to enforce a 1:1-M from a base table to the Join Tables, how would i do this?

Tried using their "Oh so useful" Relationship Schema, but since i cant find a "unique" index for the PK's i setup and indexed, i cant use the trickle-down delete portion of the Enforce Integrity. Has the "rarely" seen, which seems to be common for me, Relationship status of "Indeterminate".

Design

Table1 (base table):

Fields:

gid
nm
(more fields)

Indexes:

gid + nm (unique identification - gid is not AutoNumber)
gid

Table 2 (join table):

Fields:

gid
cid
(more fields)

Indexes:

gid + cid (unique identification)
gid
cid

Trigger Idea:

Delete (BeforeDelete Trigger)

If (table1.gid is deleted) then Delete * From Table2 Where gid=delete.id
- Where delete is the cache table
Continue validation until all tables have been properly updated/deleted of the entries for the gid from the base table

Edit 2012-09-04 @ 12:20pm

Ok Here is the Data-Macro i have right now, its probably syntactically sloppy but what i have deduced so far from my readings online. Is there anything i should be aware of, or expect, when using this format? And yes deletegroup is a globabl method posted in a Module.

The end-users will be using Runtime Access, should this trigger be hampered in any way by a runtime environment vs a full-version?

The natural next step in macros is to provide a model for business rules. Data macros allow developers to attach logic to record/table events (similar to SQL triggers). This means you write logic in one place and all forms and code that updates those tables inherit that logic. Here are a few data macro scenarios you might find in a typical Donations Management database:

Access 2010 data macros (similar to triggers)

Alternatively, for cascade delete, considering setting up relationships as per my comment to your previous post: Reference to composite primary key in Access 2007