I want to create a trigger on a table in my DB, called "Assoc_Persons". This table allows me to link persons from the "Persons" Table to the "Incidents" Table. Each entry in the Incident Table can have multiple persons attached to it and the purpose of the Assoc_Persons table is to capture data relative to the time the record was created (snap-shot) such as their Work Unit, their title etc. So what I want to do is that every time there is a Insert, Update or Delete in the Assoc_Persons I want to search the Assoc_Persons table for other Persons attached to this incident, append them togehter and and then copy the data into another table Called Incident_Persons. In between the names I want to insert a "vbNewLine" so that when I display this data in a RIchtextbox they look like this:
Mr. John Smith
Ms. Francine Thompson
Dr. Larry Wilder
A simplified schema of the tables are as follows:
So I decided to create a trigger and here is what I have so far:Quote:
Persons - PERNUM, FNAM, LNAM, UNIT, TITLE
Assoc_Person - APERNUM, PERNUM, INCNUM, TITLE, UNIT
Incidents - INCNUM, DATE, Incident_Number
Incident_Persons - INCNUM, PERSON
VB Code:
CREATE TRIGGER trig_update_Person ON ASSOC_Persons FOR UPDATE AS DECLARE @INCNUM INT DECLARE @TITLE VARCHAR(50) DECLARE @FNAM VARCHAR(50) DECLARE @LNAM VARCHAR(50) DECLARE @PERNUM INT DECLARE @PERSON VARCHAR(1000) IF NOT UPDATE(LNam) AND NOT UPDATE(FNam) BEGIN RETURN END SELECT @INCNUM = (SELECT INCNUM FROM Updated) SELECT @INCNUM = (SELECT INCNUM FROM Inserted) SELECT * FROM ASSOC_Persons WHERE INCNUM = @INCNUM SELECT @PERNUM=PERNUM,@TITLE=PER.TITLE,@LNAM=PER.LNAM,@FNAM=PER.FNAM FROM Persons AS PER INNER JOIN ASSOC_Persons AS APER ON PER.INCNUM = APER.INCNUM WHERE APER.INCNUM = @INCNUM Do While Not EOF BEGIN @PERSON = @PERSON + @TITLE + ' ' + @FNAM + ' ' + @LNAM END INSERT INCIDENT_PERSONS VALUES(@INCNUM,@PERSON)
But I am getting stuck on how do I loop through the Assoc_Persons Table in order to get the records.
Thanks
