trigger insertions into same table

Question

I have many tables in my database which are interrelated. I have a table (table one) which has had data inserted and the id auto increments. Once that row has an ID i want to insert this into a table (table three) with another set of ID's which comes from a form(this data will also be going into a table, so it could from from that table), the same form as the data which went into the first table came from.

The two ID's together make the primary key of the third table.

How can I do this, its to show that more than one ID is joined to a single ID for something else.

Thanks.

Answer 1

So, i would set this up to be constraints rather than triggers?

Does this work when I add data to t1 but before I add data to t2? Won't that crash?

Answer 2

I think you can probably do what you're describing (just write the INSERTs to table 3) in the table 1 trigger) but you'll have to put the additional info for the table 3 rows into your table 1 row, which isn't very smart.

I can't see why you would do that instead of writing the INSERTs in your code, where someone reading it can see what's happening.

The trouble with triggers is that they make it easy to hide business logic in the database. I think (and I believe I'm in the majority here) that it's easier to understand, manage, maintain and generally all-round deal with an application where all the business rules exist in the same general area.

There are reasons to use triggers (for propagating denormalised values, for example) just as there are reasons for useing stored procedures. I'm going to assert that they are largely related to performance-critical areas. Or should be.

Answer 3

Ok, I have a meeting table and a users table and an invited table

so when i set up a meeting, I add a name and description and get the meetingID and I also want to insert the list of invited users so I use the emails they've given to get their userId which is also autoincrement. And I then want to put into the invited table that a user was invited to a meeting, thus I need to insert both into the table at one time and insert(possibly) more than one user per meeting.

Answer 4

You can't do that through a trigger as the trigger only has available to it the data that you already inserted not data that is currenlty only residing in your user interface.

Normally how you handle this situation is that you write a stored proc that inserts the meeting, returns the id value (using scope_identity() in SQL Server, but I'm sure other databases would have method to return the auto-generated id as well). Then you would use that value to insert to the other table with the other values you need for that table. You would of course want to wrap the whole thing in a transaction.