Before Insert or Update Trigger

  • JayK

    SSCrazy

    Points: 2679

    Comments posted to this topic are about the item Before Insert or Update Trigger

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    A nice and easy one.

    Thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Iulian -207023

    SSCertifiable

    Points: 7508

    I missed this one but nice question thanks.

    I was thinking that the row can be updated since on insert operation with trigger on it:

    first inserts the row into the table, then the row is added into the INSERTED table, then the trigger actions are executed and finally commit or rollback.

    But I missed the "updating the row in the INSERTED table" :angry: which is not possible.

    Anyway nice exercise, thanks.

    Regards,

    Iulian

  • DugyC

    Hall of Fame

    Points: 3804

    Good question, but another lost point for me 🙁

    Didn't read the question properly and misunderstood the "INSERTED table" reference. Once I understood that, the answer was easy to find. Bummer.

    Question was worded well enough, I was just stupid.

    😉

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • cengland0

    SSCertifiable

    Points: 6102

    Got this one wrong because I didn't understand the wording of the question.

    Is it possible, using a trigger, to update a data row before commiting the insert to a table by first updating the row in the INSERTED table ?

    What row in the Inserted table? It didn't specify the one that was just inserted. Also, what kind of trigger? Can't you use an update trigger that would update a row before any inserts?

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    cengland0 (1/10/2011)


    Got this one wrong because I didn't understand the wording of the question.

    Is it possible, using a trigger, to update a data row before commiting the insert to a table by first updating the row in the INSERTED table ?

    What row in the Inserted table? It didn't specify the one that was just inserted. Also, what kind of trigger? Can't you use an update trigger that would update a row before any inserts?

    To quote BOL:

    DML triggers use the deleted and inserted logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is tried. The deleted and inserted tables hold the old values or new values of the rows that may be changed by the user action

    So the INSERTED table is the temporary table maintained by the system.

    What kind of trigger? The question asks if it is possible with a trigger. So it is up to you to find out if there is a trigger that can do this. That's the whole point of the question.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • cengland0

    SSCertifiable

    Points: 6102

    Koen (da-zero) (1/10/2011)


    What kind of trigger? The question asks if it is possible with a trigger. So it is up to you to find out if there is a trigger that can do this. That's the whole point of the question.

    I understand that you cannot update data in the TEMPORARY INSERTED table. I thought that question asked about Updating any row in the table was that that you inserted the record in -- not the temporary one. I was confused on the definition of "INSERTED table" thinking that could mean the table you just inserted the record in. Understand the issue now?

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    cengland0 (1/10/2011)


    Koen (da-zero) (1/10/2011)


    What kind of trigger? The question asks if it is possible with a trigger. So it is up to you to find out if there is a trigger that can do this. That's the whole point of the question.

    I understand that you cannot update data in the TEMPORARY INSERTED table. I thought that question asked about Updating any row in the table was that that you inserted the record in -- not the temporary one. I was confused on the definition of "INSERTED table" thinking that could mean the table you just inserted the record in. Understand the issue now?

    I surely understand the issue. I had to read the question a few times in order to get it right.

    The thing that finally made the trick for me was that INSERTED was in upper case, so I thought it had to be the special temp table.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    I understood the question, and I thought it was a good one. I had honestly never considered whether or not the INSERTED table could be updated. I thought it would be pretty cool if it could be, so I answered Yes. Oh well, that's what wishful thinking gets me. 😀

    Ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • mtassin

    SSC-Insane

    Points: 23096

    Koen (da-zero) (1/10/2011)


    cengland0 (1/10/2011)


    Koen (da-zero) (1/10/2011)


    What kind of trigger? The question asks if it is possible with a trigger. So it is up to you to find out if there is a trigger that can do this. That's the whole point of the question.

    I understand that you cannot update data in the TEMPORARY INSERTED table. I thought that question asked about Updating any row in the table was that that you inserted the record in -- not the temporary one. I was confused on the definition of "INSERTED table" thinking that could mean the table you just inserted the record in. Understand the issue now?

    I surely understand the issue. I had to read the question a few times in order to get it right.

    The thing that finally made the trick for me was that INSERTED was in upper case, so I thought it had to be the special temp table.

    Yes and I read it as can you manipulate the inserted data. i.e. if I use an INSTEAD of INSERT or INSTEAD of UPDATE trigger and do something like

    CREATE TRIGGER T1 ON Table1

    INSTEAD OF INSERT

    AS

    BEGIN

    INSERT Table1(columns)

    SELECT

    some values, some with formulae

    FROM

    INSERTED

    END

    In that case you can manipulate the INSERTED or DELETED data, but yes you can't actually manipulate the data in INSERTED or DELETED.

    No biggie, though the only take away I got from this was to learn to read the questions more carefully and try to make sure I'm thinking like the question writer better.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • OCTom

    SSChampion

    Points: 11755

    It seems logical that you can't update a record that isn't physically written, yet.

    Thanks for the question.

  • SanDroid

    SSChampion

    Points: 10068

    Nice question. I also did not quite understand if the question meant could this be done with a trigger, or could you actually update the inserted table to change the inserted data.

  • Sean Lange

    SSC Guru

    Points: 286486

    Nice straight forward question.

    It seems that once again there are people who will complain about the wording of the question no matter how straight forward it is. For those of you who complain about the wording. Stop splitting hairs, answer the question in the very clear and obvious context it was intended. Then write your own "perfect" question that will be 100% clear and unambiguous to every person on the planet regardless of language or cultural differences.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • TomThomson

    SSC Guru

    Points: 104772

    Koen (da-zero) (1/9/2011)


    A nice and easy one.

    Thanks!

    I though so too when I read it; and then saw that more than half the people so far had got it wrong, and found myself seriously wondering how they could get this one wrong

    Sean Lange (1/10/2011)


    Nice straight forward question.

    It seems that once again there are people who will complain about the wording of the question no matter how straight forward it is.

    That certainly appears to be true

    For those of you who complain about the wording. Stop splitting hairs, answer the question in the very clear and obvious context it was intended. Then write your own "perfect" question that will be 100% clear and unambiguous to every person on the planet regardless of language or cultural differences.

    My third QoTD is due tomorrow. I expect to be told it's unclear/ambiguous, although I don't think it is. It just goes with writing questions, I guess. 😉

    Edit: and I have to point out that what is and what isn't splitting hairs isn't something on which everyone will agree. Here I agree with you. In the context of a trigger "the inserted table" is a thoroughly well-defined and unambiguous term - or more accuratelu you and I believe that, while others clearly don't.

    Tom

  • SQLRNNR

    SSC Guru

    Points: 281205

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply