Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2005
    Posts
    1

    Unanswered: Triggers - run for multiple updated

    I am new to trigger and I have following question.

    I have two tables: "Customer" & "AccountDetails". "Customer" stored customer's personal information, so one customer will be at one row. "AccountDetails" stored all the accounts information which tie to each customer, so there will be multiple rows for a customer since one customer can have mulitple accounts.

    Right now I have a updated trigger on "AccountDetails" table. When there is an updated to this table, it will insert some data to another "CustLog" table for logging when customer does the updates. Let's say Customer "A" has 5 accounts. When "A" updates his accounts' information, all 5 rows will be updated, triggers will be called 5 times, and 5 insert rows will be added to "CustLog". Is that any way to keep track those 5 updated on "AccountDetails" are referred to the same customer (by customer ID or so) so that it will only run the trigger once instead of 5 times?

    I hope I make it clear and please help me on this!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If the customer is updating some element of Account Information that simultaneously for all his accounts, then I think the element should be an attribute of the Customer, and not the account. Consider moving it to your customers table.
    Regardless, if all five accounts are updated by a single statement, then in actuality the trigger will only fire once, not five times. If they are not updated simultaneously, then perhaps you should put the trigger on the Customers table.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Sep 2005
    Posts
    28
    In your triggers, use 'inserted' and if required 'deleted' logical tables that store the data that you have updated. These two tables have the same column definitions of the underlying table. Hence, you will be able to track them by some key (customer Id).

    Quote Originally Posted by jackylee
    I am new to trigger and I have following question.

    I have two tables: "Customer" & "AccountDetails". "Customer" stored customer's personal information, so one customer will be at one row. "AccountDetails" stored all the accounts information which tie to each customer, so there will be multiple rows for a customer since one customer can have mulitple accounts.

    Right now I have a updated trigger on "AccountDetails" table. When there is an updated to this table, it will insert some data to another "CustLog" table for logging when customer does the updates. Let's say Customer "A" has 5 accounts. When "A" updates his accounts' information, all 5 rows will be updated, triggers will be called 5 times, and 5 insert rows will be added to "CustLog". Is that any way to keep track those 5 updated on "AccountDetails" are referred to the same customer (by customer ID or so) so that it will only run the trigger once instead of 5 times?

    I hope I make it clear and please help me on this!

  4. #4
    Join Date
    Sep 2005
    Posts
    28

    Unhappy

    Here you go again! Another misleading info. May be it is time for you to do a little bit of testing prior to posting messages. If there is an update trigger and the underlying table is updated five times by a single update statement then it will be triggered five times. Don't argue before you check the same and be honest when you find the answer. By the way, a trigger is not for the statement but for the actual data.


    Quote Originally Posted by blindman
    If the customer is updating some element of Account Information that simultaneously for all his accounts, then I think the element should be an attribute of the Customer, and not the account. Consider moving it to your customers table.
    Regardless, if all five accounts are updated by a single statement, then in actuality the trigger will only fire once, not five times. If they are not updated simultaneously, then perhaps you should put the trigger on the Customers table.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Oh my God. A trigger gets fired ONCE for each statement, no matter how many rows are updated, be it one, or five, or five hundred.
    "a trigger is not for the statement but for the actual data"? What the heck is that supposed to mean?
    Quote Originally Posted by skrishnamurthy
    In your triggers, use 'inserted' and if required 'deleted' logical tables that store the data that you have updated. These two tables have the same column definitions of the underlying table. Hence, you will be able to track them by some key (customer Id).
    Uhm...I think he alread knows that, or he wouldn't have gotten as far as he has.
    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 2005
    Location
    Utah
    Posts
    73
    Not sure I quite follow the logic here because it seems to violate a fundamental component of databases, namely set theory. If Customer "A" changes a value that affects his accounts (i.e. a set of 5 accounts), then the trigger should be enacted for that DML and fire once altering the set of 5. If as you say, the update is called once and in turn calls the trigger five times, then you have more of a recursive logic that works on one entity at a time. This idea contradicts the reason databases were designed using set theory. Do you have references to backup you multiple calls hypothesis?
    Dandy
    Aspiring Database Dwarf

  7. #7
    Join Date
    Sep 2005
    Posts
    28

    Unhappy

    Apologies! It is my mistake. Blindman has been challenging everything I say as if he knows everything. In the heat, I wrote something that is definitely misleading. As you said, a trigger gets fired for each statement (INSERT, UPDATE, DELETE) and not an data and conceptually works like a stored procedure acting on the data (this is what I meant). I will be careful in my posting going forward.

    Quote Originally Posted by tianmingqing
    Not sure I quite follow the logic here because it seems to violate a fundamental component of databases, namely set theory. If Customer "A" changes a value that affects his accounts (i.e. a set of 5 accounts), then the trigger should be enacted for that DML and fire once altering the set of 5. If as you say, the update is called once and in turn calls the trigger five times, then you have more of a recursive logic that works on one entity at a time. This idea contradicts the reason databases were designed using set theory. Do you have references to backup you multiple calls hypothesis?

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by skrishnamurthy
    Apologies! It is my mistake. Blindman has been challenging everything I say as if he knows everything. In the heat, I wrote something that is definitely misleading. As you said, a trigger gets fired for each statement (INSERT, UPDATE, DELETE) and not an data and conceptually works like a stored procedure acting on the data (this is what I meant). I will be careful in my posting going forward.
    Ahhhh, then it is true, your rage has unbalanced you.

    Sorry to beat a dead horse. Just don't get an opportunity to use that quote much
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by skrishnamurthy
    Here you go again! Another misleading info. May be it is time for you to do a little bit of testing prior to posting messages. If there is an update trigger and the underlying table is updated five times by a single update statement then it will be triggered five times. Don't argue before you check the same and be honest when you find the answer. By the way, a trigger is not for the statement but for the actual data.
    That's just too funny. There is not one thing true in that entire diatribe.

    And if there is, please let me know which one.

    EDIT: Beat it Paul Beat....oooo, never mind....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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