-
Jul 11th, 2006, 08:01 PM
#1
Thread Starter
Giants World Champs!!!!
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:
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
Regards,
Mark
Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."
-
Jul 11th, 2006, 08:08 PM
#2
Re: Creating a Trigger
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?
-
Jul 11th, 2006, 08:12 PM
#3
Thread Starter
Giants World Champs!!!!
Re: Creating a Trigger
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
Regards,
Mark
Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."
-
Jul 11th, 2006, 08:22 PM
#4
Re: Creating a Trigger
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"
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...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|