Creating a Trigger

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:

Quote:

---

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:

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

This trigger sounds expensive - is that ok?

Usually to process a pile of records, we load a table variable and then process one at a time (delete each one after done) and then process in a loop till no records in the table variable.

CHAR(13) puts a new-line between char/varchar data.

What is IF NOT UPDATE() mean?

Can you have an INSERT and UPDATE trigger in one spot?

What does this do?

SELECT @INCNUM = (SELECT INCNUM FROM Updated)
SELECT @INCNUM = (SELECT INCNUM FROM Inserted)

First - SET should be used - not SELECT - SET @INCNUM=(Select...)

But having two in a row will make the second always process - right?

Steve,
Thanks for your post. I have never wrote a trigger before, so what you see is what I pieced together from what I could find during my search for how to write a Trigger. DO you have any suggestions on how I could accoplish what I am trying to do? Thanks

Well...

You have Create Trigger ... For Update

You need to have For Update, Insert

Do you have BOOKS ONLINE installed on this PC - I'm getting good help on Trigger...Creating

If you do not have BOL - try MSDN and search for "Triggers creating"

Quote:

---

When a trigger fires, results are returned to the calling application, just as with stored procedures. To eliminate having results returned to an application due to a trigger firing, do not include either SELECT statements that return results, or statements that perform variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the beginning of the trigger to eliminate the return of any result sets.

---

I'll check back in the AM to see where you get...