Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2004
    Posts
    42

    Unanswered: Trigger ... is this valid?

    Is the following valid syntax? Thanks for your help!

    IF (SELECT chargeID FROM inserted = 0) BEGIN

    END

    OR, should I be doing something like this:

    DECLARE @thisChargeID as int
    SET @thisChargeID = (SELECT chargeID FROM inserted)
    IF (@thisChargeID = 0) BEGIN

    END

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Both of them will cause you problems, because they assume that there is only one row in inserted. A better solution might be:
    Code:
    IF EXISTS (SELECT *
       FROM inserted
       WHERE  0 = charge_id)
       PRINT 'There''s a zero'
    ELSE
       PRINT 'Sorry, no zeros'
    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    42
    There will always only be one inserted ... chargeID is the key field

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    FastCougar, you need to understand that a trigger is only fired once for each batch of inserts/updates/deletes to a table, not once for each record. Even if your application only manipulates a single record at a time, it is important that you code your triggers for multiple records because they have no idea whether they were fired by an application operation or a multi-record operation initiated by a DBA.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Feb 2004
    Posts
    42
    Quote Originally Posted by blindman
    FastCougar, you need to understand that a trigger is only fired once for each batch of inserts/updates/deletes to a table, not once for each record. Even if your application only manipulates a single record at a time, it is important that you code your triggers for multiple records because they have no idea whether they were fired by an application operation or a multi-record operation initiated by a DBA.
    I thought that a trigger fired for each record that was inserted/updated/deleted. So, how would I reference the current record in a trigger?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by FastCougar
    I thought that a trigger fired for each record that was inserted/updated/deleted. So, how would I reference the current record in a trigger?
    That gets complicated, and is nearly always a bad idea from a performance perspective. Anytime you have to break down to handle things one row at a time, SQL Server performance will drop to nearly the level of client side code.

    A much better approach is to re-think your process (in this case your trigger) to allow it to handle any arbitrary group of rows. The code in my first post is a good example of what I mean. This mindset will allow SQL Server to run much faster than client side code.

    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "There will always only be one inserted"

    How many times have I heard that one before? It NEVER holds for long.

    Yes, the trigger fires only once for each batch of records inserted/updated/deleted. But the changes to these records are stored in two virtual tables by SQL Server. These virtual tables are always named "inserted" and "deleted", and have the same layout as the table being modified, including the primary key. You can reference them in your trigger just like any other table:
    select count(*) from inserted
    ...for example, or join them to your production table.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Feb 2004
    Posts
    42
    I'm begining to wonder if this shouldn't be a stored proceedure instead of a trigger???

    OK, here is what I am trying to do:

    I have a table called DispatchRatedTickets layed out like so:
    ratingID [int] primary key
    ticketID [int]
    chargeID [int]
    chargeQuantity [float]
    chargeAmount [money]
    chargeCost [money]
    checkNum [varchar]

    When I enter or change the chargeQuantity, I want to update the chargeAmount and chargeCost fields appropriately. There is some business logic that needs to be attended to, but I have that all figured out. My problem is finding the values of chargeID and the new value for chargeQuantity.

    I currently have a trigger on both update and delete in the chargeAmount and chargeCost columns that updates the totals in the DispatchTickets table. These triggers updated the corresponding fields in the DispatchTickets table with the sum with the same ticketID.

    Any help is greatly appreciated!

  9. #9
    Join Date
    Feb 2004
    Posts
    42

    Would this work?

    Would this work for selecting the primary key field value of the current row?

    SET @thisRatingID = (SELECT ratingID FROM DispatchRatedTickets WHERE ratingID IN (SELECT ratingID FROM inserted))

    I tried the following:
    SET @thisRatingID = inserted.ratingID

    But got this error:
    Server: Msg 107, Level 16, State 2, Procedure trg_update_ratedtickets_cost_charge, Line 27 The column prefix 'inserted' does not match with a table name or alias name used in the query.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You may not need a trigger for this at all. Consider using a computed column in your table to calculate cost:

    CREATE TABLE [dbo].[ComputedColumnTable] (
    [chargeQuantity] [float] NULL ,
    [chargeAmount] [money] NULL ,
    [chargeCost] AS ([chargeQuantity] * [chargeAmount])
    )


    Otherwise, keep in mind that "inserted" is a virtual TABLE, not a single record or a variable.

    "SET @thisRatingID = inserted.ratingID"
    ...would need to be rewritten like this:
    "SET @thisRatingID = ratingID from inserted"
    ...but would still be bad code because more than one ratingID may be returned.

    Just remember that "inserted" and "deleted" are virtual tables with the same layout as your actual tables, so you can join them to the actual tables or other production tables using JOIN clauses on primary and foreign keys.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Feb 2004
    Posts
    42
    I fully understand everything that has been said ... I am 100% aware that inserted and deleted are virtual tables and mimic the layout of the affected tables upon insert/update/delete. What I don't know how to do is select ANY value in the current row being affected. Is this even possible? If so, how would this be done?

    Example:

    ratingID | ticketID | chargeID | chargeQuantity | chargeAmount | chargeCost | checkNum
    21 | 698 | 0 | 457 | 54.84 | 23.58 | NULL

    When I update the chargeQuantity field, I want to fire a trigger that would select the current row's ratingID. From there, I could run a query to gather other values and then do whatever I need to with the trigger. In my case, I would get the current row's chargeID and use it to look up a charge table to run the appropriate calculations to find the new chargeAmount and chargeCost. This CAN NOT be done with a computed column due to multiple lookups to gather the computing information.

    So, I have one simple question: How do I find any value in the current row ONLY? This would be like using javascript's "this" to reference the current field. Any ideas?

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I guess our problem with your problem is getting past your thought of a "current" row.

    If we're talking cursors, then that would be a different story.

    In set based processing, there is no "current" row....well I guess the "current" row would logically be represented by primary keys...where they join to other tables would be (logically, at the time of the materalization of the join) the "current" row...

    I think I see what you want...an update based on a join..

    post the ddl of the tables involved, some sample data (using DML) and your expected results...

    With that, you'll get an answer in seconds....
    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.

  13. #13
    Join Date
    Feb 2004
    Posts
    42
    ratingID | ticketID | chargeID | chargeQuantity | chargeAmount | chargeCost | checkNum
    21 | 698 | 0 | 457 | 54.84 | 23.58 | NULL
    22 | 712 | 10 | 775 | 65.24 | 36.28 | NULL

    If I change the second row's chargeQuantity value from "775" to "900", I want to get both the "current" row's chargeID and ratingID. My desired results are to get @thisRatingID=22 and @thisChargeID=10. The table name is DispatchRatedTickets. THANKS FOR YOUR HELP!

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    As long as you keep talking about "the current row's ratingID" you do not fully understand everything that has been said.

    JOIN your production table to your [inserted] table on the primary key to isolate only those records affected by the transaction, and then JOIN it to the charge table on chargeID to run your calculations and update your production table.
    If it's not practically useful, then it's practically useless.

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

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK...what platform are you on?

    When you say

    If I change the second row's chargeQuantity value from "775" to "900"
    How did you do the change? through an interface? And you want

    desired results are to get @thisRatingID=22 and @thisChargeID=10.
    Don't you have all the data already? How did you do the change?
    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
  •