Creating a Trigger

[Mark Gambo]

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:

Persons - PERNUM, FNAM, LNAM, UNIT, TITLE
Assoc_Person - APERNUM, PERNUM, INCNUM, TITLE, UNIT
Incidents - INCNUM, DATE, Incident_Number
Incident_Persons - INCNUM, PERSON

So I decided to create a trigger and here is what I have so far:

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