BEFORE TRIGGER Idea Posted to Microsoft Connect

  • JediSQL

    SSCommitted

    Points: 1896

    I have wished for SQL Server BEFORE TRIGGERs for a long time. I have posted to Microsoft Connect (their feedback service) about this a few time. I finally put in a post to which they responded "Thanks to this idea. We understand the requirement and we are putting it into the backlog... If this feature gets more votes it would be reconsidered in future releases." Please check out

    https://connect.microsoft.com/SQLServer/feedback/details/3127222/before-trigger-behavior-with-execute-original-statement

    and, if you like it, please vote it up.

    Sincerely,
    Daniel

  • Jeff Moden

    SSC Guru

    Points: 993909

    I definitely voted that bad boy up.  That was one of the things that I really liked about Oracle for the short 3 year stint that I had to work with it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Ed Wagner

    SSC Guru

    Points: 286955

    I voted it up as well.  It's now 4 up and 1 down.  I have a "few" years of Oracle under my belt and, which I don't use triggers often, it would be handy to have.
  • Jeff Moden

    SSC Guru

    Points: 993909

    I just posted a plea and a link to this post on "the thread".  I'd love to see "BEFORE Triggers" become a reality instead of those gosh-awful "Instead Of" triggers.  I have little love in general for Oracle but this one of those things that Oracle absolutely did right from the git.  It would make writing audit triggers a whole lot easier to prevent unnecessary duplication in audit tables, which become some of the biggest tables of them all.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Jeff Moden - Saturday, May 20, 2017 2:33 PM

    I just posted a plea and a link to this post on "the thread".  I'd love to see "BEFORE Triggers" become a reality instead of those gosh-awful "Instead Of" triggers.  I have little love in general for Oracle but this one of those things that Oracle absolutely did right from the git.  It would make writing audit triggers a whole lot easier to prevent unnecessary duplication in audit tables, which become some of the biggest tables of them all.

    [/quote]

    Another upvote. I like the idea in the item, allthough I'd like just implementing BEFORE triggers even better.
    However, I do not share your dislike for INSTEAD OF triggers. They are a great tool for some specific purposes. I have used them to expose views as if they were the base tables, hiding the actual base tables, and allowing full updatability even when SQL Server itself would consider the view non-updatable or misunderstand how to change the base tables to correspond to the actual change,


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Ed Wagner

    SSC Guru

    Points: 286955

    Hugo Kornelis - Sunday, May 21, 2017 3:03 AM

    Jeff Moden - Saturday, May 20, 2017 2:33 PM

    I just posted a plea and a link to this post on "the thread".  I'd love to see "BEFORE Triggers" become a reality instead of those gosh-awful "Instead Of" triggers.  I have little love in general for Oracle but this one of those things that Oracle absolutely did right from the git.  It would make writing audit triggers a whole lot easier to prevent unnecessary duplication in audit tables, which become some of the biggest tables of them all.

    Another upvote. I like the idea in the item, allthough I'd like just implementing BEFORE triggers even better.
    However, I do not share your dislike for INSTEAD OF triggers. They are a great tool for some specific purposes. I have used them to expose views as if they were the base tables, hiding the actual base tables, and allowing full updatability even when SQL Server itself would consider the view non-updatable or misunderstand how to change the base tables to correspond to the actual change,

    [/quote]
    The more tools we have in our toolbox, the more choices we have to implement functionality.  As long as they're written well, we can pick the right tool for the job.

    Besides, AFTER triggers already exist, so aren't BEFORE triggers just logical?

  • Jeff Moden

    SSC Guru

    Points: 993909

    Hugo Kornelis - Sunday, May 21, 2017 3:03 AM

    Jeff Moden - Saturday, May 20, 2017 2:33 PM

    I just posted a plea and a link to this post on "the thread".  I'd love to see "BEFORE Triggers" become a reality instead of those gosh-awful "Instead Of" triggers.  I have little love in general for Oracle but this one of those things that Oracle absolutely did right from the git.  It would make writing audit triggers a whole lot easier to prevent unnecessary duplication in audit tables, which become some of the biggest tables of them all.

    Another upvote. I like the idea in the item, allthough I'd like just implementing BEFORE triggers even better.
    However, I do not share your dislike for INSTEAD OF triggers. They are a great tool for some specific purposes. I have used them to expose views as if they were the base tables, hiding the actual base tables, and allowing full updatability even when SQL Server itself would consider the view non-updatable or misunderstand how to change the base tables to correspond to the actual change,

    [/quote]

    You misunderstand my dislike for "Instead Of" triggers.  As you say, they ARE great for specific purposes.  A perfect example is when the target of an insert is actually an updateable view built for one reason or another.  Still, you have to control everything there instead of just what you need to control, whatever that may be.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Johan Bijnens

    SSC Guru

    Points: 134254

    Jeff Moden - Sunday, May 21, 2017 11:20 AM

    Hugo Kornelis - Sunday, May 21, 2017 3:03 AM

    Jeff Moden - Saturday, May 20, 2017 2:33 PM

    I just posted a plea and a link to this post on "the thread".  I'd love to see "BEFORE Triggers" become a reality instead of those gosh-awful "Instead Of" triggers.  I have little love in general for Oracle but this one of those things that Oracle absolutely did right from the git.  It would make writing audit triggers a whole lot easier to prevent unnecessary duplication in audit tables, which become some of the biggest tables of them all.

    Another upvote. I like the idea in the item, allthough I'd like just implementing BEFORE triggers even better.
    However, I do not share your dislike for INSTEAD OF triggers. They are a great tool for some specific purposes. I have used them to expose views as if they were the base tables, hiding the actual base tables, and allowing full updatability even when SQL Server itself would consider the view non-updatable or misunderstand how to change the base tables to correspond to the actual change,

    You misunderstand my dislike for "Instead Of" triggers.  As you say, they ARE great for specific purposes.  A perfect example is when the target of an insert is actually an updateable view built for one reason or another.  Still, you have to control everything there instead of just what you need to control, whatever that may be.[/quote]
    Considered the new Pirates of the Caribbean is released in 2 days, guess how I read the  '...ARE....' in this reply ( #Palmface ) 😉

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Brandie Tarvin

    SSC Guru

    Points: 172517

    JediSQL - Friday, May 19, 2017 5:47 PM

    I have wished for SQL Server BEFORE TRIGGERs for a long time. I have posted to Microsoft Connect (their feedback service) about this a few time. I finally put in a post to which they responded "Thanks to this idea. We understand the requirement and we are putting it into the backlog... If this feature gets more votes it would be reconsidered in future releases." Please check out

    https://connect.microsoft.com/SQLServer/feedback/details/3127222/before-trigger-behavior-with-execute-original-statement

    and, if you like it, please vote it up.

    Done.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Lynn Pettis

    SSC Guru

    Points: 442116

    InterBase had both BEFORE and AFTER Triggers.  I liked that as well, and having them in SQL Server would be a great addition.

  • Chris Harshman

    SSC-Forever

    Points: 41661

    Lynn Pettis - Monday, May 22, 2017 10:34 AM

    InterBase had both BEFORE and AFTER Triggers.  I liked that as well, and having them in SQL Server would be a great addition.

    Wait... there's actually someone else besides me who's actually worked with InterBase???  I've worked with BEFORE triggers in Oracle and InterBase, and I've found using INSTEAD OF triggers to be a clumsy substitute... I've also voted for it

  • Ed Wagner

    SSC Guru

    Points: 286955

  • Lynn Pettis

    SSC Guru

    Points: 442116

    Chris Harshman - Monday, May 22, 2017 12:10 PM

    Lynn Pettis - Monday, May 22, 2017 10:34 AM

    InterBase had both BEFORE and AFTER Triggers.  I liked that as well, and having them in SQL Server would be a great addition.

    Wait... there's actually someone else besides me who's actually worked with InterBase???  I've worked with BEFORE triggers in Oracle and InterBase, and I've found using INSTEAD OF triggers to be a clumsy substitute... I've also voted for it

    Briefly, we were actually comparing SCO/Unix, InterBase, and Delphi against Windows NT 4.0, SQL Server 6.5 and VB 6 at a previous employer.  MS won out, but look where I am today.  Not sure that would have happened with other option.  I did like InterBase as a database system, our developer however found it a bit difficult to work with from VB 6.

  • Jeff Moden

    SSC Guru

    Points: 993909

    Ed Wagner - Monday, May 22, 2017 12:16 PM

    Well, it's now up to 20 yes to 1 no.

    Heh... dollars to donuts the No vote was from Michael John.  He hates triggers. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Ed Wagner

    SSC Guru

    Points: 286955

    Jeff Moden - Monday, May 22, 2017 2:27 PM

    Ed Wagner - Monday, May 22, 2017 12:16 PM

    Well, it's now up to 20 yes to 1 no.

    Heh... dollars to donuts the No vote was from Michael John.  He hates triggers. 😉

    He sure does.  He might vote against it just because of that.  The only real question is who would put up the dollars and who would put up the doughnuts.  I enjoy them both. 😛

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

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