Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
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 4 '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 4 '12 at 16:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.