I have a complex (for me that is) page where I'm trying to insert into three tables, or updating two of them if a record already exists and can't quite figure out how to do it using ADDT.
This is for a summer camp registration page for a school. By the time the page is reached, I have the camp id passed along as a GET variable. I then have a form for the student and the student's family info.
I have three tables: the family table, the student table (with a familyID FK), and the campSignup table (with a studentID FK). I first want to check to see if the family exists. If it does, I want to update the family record with any new info. If it doesn't, I want to insert a new record.
If the family exists, I check to see if the student exists. If he does, I update the record. If not I create a new one.
Then I create a new campSignup record with the studentID.
I've created the separate insert and update transactions, but once that's done I'm stuck. I've tried creating a Check Unique trigger but the only thing I can do with it is generate an error message. I want to use it as a conditional to point either to the insert or update record.