0

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)

  1. If (table1.gid is deleted) then Delete * From Table2 Where gid=delete.id
    • Where delete is the cache table
  2. 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.

Before Delete Data-Macro

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

GoldBishop
  • 2,820
  • 4
  • 47
  • 82

1 Answers1

1

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

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • that would be the `gid + cid` indexes, but the Join Table only has one of the fields of either indexes on the tables. – GoldBishop Sep 04 '12 at 16:37
  • Been looking at the other link, but it talks in generics and not alot of details about how to operate with the `old` and `new` cache tables. I ultimately want to delete records from `Table2` where the `old.gid` is contained in `Table2`. I dont want to interrupt the deletion just want to design a trigger mechanism that will delete from the other Join Tables. – GoldBishop Sep 04 '12 at 16:41