Results 1 to 4 of 4

Thread: Creating a Trigger

  1. #1

    Thread Starter
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    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:
    1. CREATE TRIGGER trig_update_Person
    2. ON ASSOC_Persons
    3. FOR UPDATE
    4. AS
    5.  
    6. DECLARE @INCNUM INT
    7. DECLARE @TITLE VARCHAR(50)
    8. DECLARE @FNAM VARCHAR(50)
    9. DECLARE @LNAM VARCHAR(50)
    10. DECLARE @PERNUM INT
    11. DECLARE @PERSON VARCHAR(1000)
    12.  
    13. IF NOT UPDATE(LNam) AND NOT UPDATE(FNam)
    14. BEGIN
    15. RETURN
    16. END
    17.  
    18. SELECT @INCNUM = (SELECT INCNUM FROM Updated)
    19. SELECT @INCNUM = (SELECT INCNUM FROM Inserted)
    20.  
    21. SELECT * FROM ASSOC_Persons WHERE INCNUM = @INCNUM
    22.  
    23. SELECT @PERNUM=PERNUM,@TITLE=PER.TITLE,@LNAM=PER.LNAM,@FNAM=PER.FNAM
    24. FROM Persons AS PER INNER JOIN ASSOC_Persons AS APER ON
    25. PER.INCNUM = APER.INCNUM
    26. WHERE APER.INCNUM = @INCNUM
    27.  
    28. Do While Not EOF
    29. BEGIN
    30.    @PERSON = @PERSON + @TITLE + ' ' + @FNAM + ' ' + @LNAM
    31. END
    32.  
    33. 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."


  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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?

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    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."


  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width