Best Practice triggers and referential integrity

[LTusing]

I have a table, Patient_Visit
v_id (auto_id) (PK)
date
rater
columnA(FK)
columnB(FK)
columnC(FK)

with a 1:M relationship between multiple assessment tables. Each assessment table has a Unique Primary PK which is a FK in tbl.Patient_Visit (ex. column A[FK] There is also a FK (v_id) in each assessment table. Design wise it was resolution for a M:M relationship. I would like to update the Patient_Visit table with the respective FK column value,for each column after each assessment row is populated. As a note, not all assessment tables are populated at each visit.

Further note, there are I,U,and Delete triggers for each table for auditing purposes in place.

THANK YOU!! lt

 

[Crowley16]

erm, hows about an stored proc to do insert on the assessment which includes an insert into assessment table, and another insert into the visit table, utilising the @@identity value?

you may even put a transaction around it if you feel it's necessary...

--------------------
Procrastinate Now!