trigger help

[DanC]

ok, first time working with triggers and, go figure, I ran into a problem.

We have an application that allows clients to add new personnel records to a database through a web front-end. The client will add name, phone, address info which is written to two tables on SQL: individual1 and address1.

My goal is to be able to write an insert trigger that will pull the new data from both the individual1 table and the address1 table into another table.

I've briefly played around with it, and can get a trigger to work from one table, but I really have no idea on how to get it to pull from two tables.

Thanks in advance for any help.

-D

 

[KrisSimonis]

I take it that the relation between individual1 and adress1 is a 1-1 relation. So they will both contain a field along the lines of (unid char(6) UNIQUE) that corresponds with a field in the other table of the same type and that are filled with the same data.
If so you should write 2 insert triggers ( one on each table ) that checks if the unid exists in the third table if so the update the info there with the information contained in the table that the trigger is added to.
If you do this, they you'll have to use the BEGIN TRANSACTION and COMMIT TRANSACTION statements in your trigger because you don't want to leave a window between checking if something exists and creating it. Otherwise your triggers ( which will most likely be called withing milliseconds from eachother ) might mess eachother up ( trigger A checks and finds no record, meanwhile B that has already checked this creates the record and then A tries to create it aswell generating an error )
The effect of all of this will be that when you insert a record into either table, in the third table a partially filled record will be created that contains the info that this insert put into either of the first two tables, then when the other of the two tables gets a matching record it will fill the other half of the information.

Another way is that your application doesn't write to the individual and address tables but instead writes to the combined table and you put an insert trigger on the this table that generates the records in individual1 and address1. The plus side on that is that your relation between address and individual doesn't have to be a 1-1 relation but could be a 1-many relation ( since people tend to have more than 1 address ).

 

[DanC]

well, changing the app is out of my hands, I just get to be the one to deal with how it's designed. I'm just trying to supply the new data on a real-time basis to another application where the info all resides in one table.

I've tried writing the trigger a couple different ways, but I still can't get it to work. I also tried to put an additional trigger on the destination table to update the information, but that didn't work either.

This is what I have so far:

Individual1 has 3 columns: ind_id, firstname, lastname
address1 has 3 columns: ind_id, add1, city
table2 (destination table) has 4 columns: ind_id, firstname, lastname, address1

As you can see, I'm not very proficient at writing this yet.

create trigger tr_individual_insert
on individual1
for insert
as
begin
insert table2
select i.ind_id, firstname, lastname, null
from inserted i
UPDATE table2
SET address1 = a.add1
from table2 d
inner join address1 a
on d.ind_id = a.ind_id
inner join inserted i
on a.ind_id = i.ind_id
end