Results 1 to 11 of 11
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Question about triggers and sprocs.

    Hi all. OK, we have decided for our new process to build some triggers that will take the record inserted and convert the dollar values to another currency and update the table.
    The Insert happens in a stored procedure and after the insert statement happens there are other statements that need to run on that record as well.
    My question is, after the insert statement fires off and FINISHES... then the trigger fires to do the conversion I assume, correct? Not during the insert?
    Also, how fast does the trigger fire off? I am concerned that the trigger will not be done before the other statements in the stored procedure will start.
    What you guys think? Would I be better off just writing another stored procedure and running that instead of a trigger?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you only ever accepting values in dollars?
    George
    Home | Blog

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Right now, yes. My company only deals with US $. Now, they are ready to take multi currency but for our accounting, we are still keeping all in USD. So, when a record comes in as Canadian $, I need the trigger to fire off and convert it to USD for accounting and do an update on a table.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This logic should NOT go into a trigger.
    Conversions such as this should be handled in the application layer, or by a sproc.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    OK, thanks blindman.
    But for my future reference, can you shine some light on the trigger execution please. I was reading somewhere that if you are doing an insert into table 1 and you have an AFTER trigger on that table, then the COMMIT of that insert statements will happened only after the trigger if finished. Is this correct?
    So let’s say I have an INSERT statement into table 1 in stored procedure and then a select of some fields into variables from that record to do something with them, is the trigger done by the time I do my select statement?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The real commit/rollback occurres after the last logically executable line in the trigger. I second blindman on that, - the conversion has no place in the trigger. But I would also add: very rarely a trigger has a place in a real app that was properly designed. Of course there are exceptions, but they are and should be very seldom compared to what's out there now
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Within the trigger, you should not be referring to the table anyway. You should reference the virtual INSERTED and DELETED tables to get the new, replaced, and deleted values.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    I agree with both of you and thanks for pointing me in the right direction. I guess what I am trying to figure out now, for my own knowledge is the timing of the trigger.
    I know that I would reference the INSERTED virtual table. I know that lets say I am doing an insert in a stored procedure that the trigger is fired off on insert and then the trigger gets the data from the virtual INSERTED table and does whatever I tell it to do, but my question is, if I am doing an insert in stored procedure on a table with trigger, then I have a select statement after the insert, is the trigger done by the time I do my select statement?

  9. #9
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    I hope you see what I am trying to figure out

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. The trigger operation is included in the transaction of the first select statement. Trigger operation would complete, or be rolled back, before your second statement starts.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Perfect. Thanks for your help

Posting Permissions

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