CODEWITHSUNDEEP
triggersms-access-2010ms-access-data-macro
tnt
tnt

Reputation: 3591

Access 2010 Insert Trigger

I have a parent table (Assessment) with two children tables with 1 to 1 relationships defined. To make sure that a child row is never added that does not have a parent entry, I want to add an insert trigger to the child table (ConsequenceAssessment) in this case. The following ConsequenceAssessment BeforeChange trigger fires but I cannot find how to reference the INSERTED rowset. There is an OLD recordset that works for an update; but, how do I access the inserted row. The following is my best attempt - but, the ConsequenceAssessment table does not yet include the new row and therefore, the trigger always hits the RaiseError.

UPDATE: Just found out that I can enforce Referential Integrity on a one-to-one relationship within Access (rookie misunderstanding). I would still like to know how to access the updated recordset. With MS SQL Server, this is implemented via the INSERTED table which is available within the scope of an INSERT trigger. So, what is the equivalent in MS Access.

enter image description here

Upvotes: 1

Views: 257

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123839

In a Before Change data macro, [fieldname] refers to the new value and [old].[fieldname] refers to the old value (which would be Null for an insert).

In your particular case [ConsequenceAssessment].[id] appears to be the primary key for that table, not a foreign key referring to the [Assessment] (parent) table. So, the lookup is simply searching for the wrong key value in the parent table.

Upvotes: 1

Related Questions