Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: Trigger Help

  1. #1
    Join Date
    Feb 2006
    Posts
    17

    Unanswered: Trigger Help

    Hi, First of all i am not sure whether a trigger could be associated with two tables if possible here is my problem.

    I have two tablescout and patrol

    in table scout i have patrol_code as the foreign key and in patrol i have number of members(how many members).
    What i would like to do is when a new scout is added, or deleted, the table patrol is updated accordingly...exactly the number of members in that particular patrol where the scout is added...i hope u see what i mean....

    how could i possibly formulate this trigger??

    Thanks for the help

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, a group of triggers can do this. No, a trigger should not do this. A collection of triggers like what it takes to make this work is both difficult to correctly maintain, and because of that very prone to error. If you decide to do this, you are basically creating a problem for yourself at some point in the future.

    Why do you need a count of scouts in a patrol? Once you understand why you need that count, you can find a better way to retrieve that information than using triggers.

    -PatP

  3. #3
    Join Date
    Feb 2006
    Posts
    17
    The reason for this i need to know how many members each patrol has. At the moment when i add a new scout i have to manually update the number of members which is a pain....so what is the other way of doing it...??
    thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I guess I'm not asking the correct question. In order to help you solve your problem, I need to understand why you need the count of scouts in the patrol... There are many ways to solve this kind of problem, and I need to understand the why to help you get to the how. Some are easy, some are hard, some work in specific ways... Until I have a good handle on why you need to know the number of scouts, I don't have any basis to decide which of the possible solutions makes the most sense for your specific case.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Issue sounds familiar....
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64242

    Basically, you should be creating a view that calculates the count dynamically.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2006
    Posts
    17
    if By the why you mean the purpose, there is no particular purpose other than o automate the increament of the count of scouts...i hope that is what you are asking....thanks

  7. #7
    Join Date
    Feb 2006
    Posts
    17

    View!!

    Quote Originally Posted by blindman
    Issue sounds familiar....
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64242

    Basically, you should be creating a view that calculates the count dynamically.
    Sorry, the reason i wanted to use a trigger is to update the table each time a new scout is added...would a view do this??

    If yes what would be the sysntax....for each time a scout is added in scout table add +1 to numberofmembers in the patrol table

    Thanks

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Record counts are derived data. Derived data is not normally stored in a database. Instead, it is calculated when it is needed. A view or stored procedure will do this for you by using the aggregate COUNT() function.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Something like:
    Code:
    SELECT packID, packName
    ,  (SELECT Count(*)
       FROM scouts
       WHERE  scout.packID = pack.packID)
       FROM pack
    -PatP

  10. #10
    Join Date
    Feb 2006
    Posts
    17
    Hi, i better try this derived data but i cant get it....though it seems interesting...This is what i am trying to do...
    2 tables:scout and patrol....when a new record is added to scout it includes a patrol code to which the scout is assigned...And in that case i have to go to patrol table and change the number of members count of that patrol to which he was assigned(patrol code in scout table)..i would like to automate this
    What would be the code for this please...

    i previously used a trigger like this:

    create TRIGGER totalScouts
    ON scout
    FOR INSERT
    AS
    UPDATE patrol
    SET number_of_members = number_of_members+1
    where Patrol_Code=patrol.Patrol_Code


    PROBLEM WITH THIS: ALL PATROL WERE UPDATED WITH AN EXTRA MEMBER...
    pLEASE HELP!!

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it is true that mean old dba's do not store derived data. application developers want to to do it all of the time.

    there is a fix for your trigger, but if i tell you the others will not like it much. if you go to the sql server books online article on CREATE TRIGGER, it's not too long, and your answer is in there.

    i guess what the others are having trouble with is why the count has to be in a table. what happens when we delete a scout when he\she leaves a patrol. now you need a decrementing trigger. oh no.

    it just seems easier to do a little...

    SELECT COUNT(*) FROM SCOUTS where Patrol_Code = 'foo'

    ...when you need to know.

    BTW, derived data and databases are not interesting. It is what some intelligent people pimp their minds out for to make money. If you anyone disagress, please pay attention to the next non programmer you try to explain your job to. You will find them awkwardly looking for another conversation.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    ...there is a fix for your trigger, but if i tell you the others will not like it much...
    Oh, go ahead and tell him how to do things the wrong way. He's not listening to us anyway.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Actually I don't see any problem with the trigger. If you assume that every time a scout is added, all of the patrol totals will be off by a little bit, then you'll have a much better understanding of the data. In this case, you'll know that the total column is wrong all of the time, instead of just suspecting that it is wrong in some cases. Once you understand that, it will help you get to a usable solution much more quickly!

    Of course I'm being facitious here. My point is that triggers will eventually polute the data almost every time, and when dealing with more than one table it is almost a certainty. You've just found a convincing way to demonstrate this for yourself instead of having to take it from me on faith!

    -PatP

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    If you make it through this article you will have a much better context for what this lot are squabbling about.
    http://r937.com/relational.html
    Notice the bit that addresses the very issue at the heart of this thread?

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2006
    Posts
    17
    Quote Originally Posted by Thrasymachus
    it is true that mean old dba's do not store derived data. application developers want to to do it all of the time.

    there is a fix for your trigger, but if i tell you the others will not like it much. if you go to the sql server books online article on CREATE TRIGGER, it's not too long, and your answer is in there.

    i guess what the others are having trouble with is why the count has to be in a table. what happens when we delete a scout when he\she leaves a patrol. now you need a decrementing trigger. oh no.

    it just seems easier to do a little...

    SELECT COUNT(*) FROM SCOUTS where Patrol_Code = 'foo'

    ...when you need to know.

    BTW, derived data and databases are not interesting. It is what some intelligent people pimp their minds out for to make money. If you anyone disagress, please pay attention to the next non programmer you try to explain your job to. You will find them awkwardly looking for another conversation.



    I dont think you guys got my problem...my problem is to find a solution to my problem dont really care whether i use a trigger or not...it seems unpopular reading around....
    Now that we cleared the air could someone tell me how i go about this view...
    Would this one do it for me?
    "SELECT COUNT(*) FROM SCOUTS where Patrol_Code = 'foo' "
    Do i have to run it everytime i want to update scout, patrol???

Posting Permissions

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