Are the posted questions getting worse?

  • Lynn Pettis (6/20/2016)


    Sean Lange (6/20/2016)


    Lynn Pettis (6/20/2016)


    Sean Lange (6/20/2016)


    Lynn Pettis (6/20/2016)


    Sorry for the technical question in The Thread, but I just have to ask everyone around the water cooler what they think.

    Does anyone else see a problem with the following code?

    Create Trigger tr_TriggerName on dbo.OperationsSIGACTTable Instead Of Insert, Update

    AS

    Begin

    Insert Into OperationsSIGACTTable (Col1, Col2, Summary, .... , ColN)

    Select Col1, Col2, RemoveSpecialChars(Summary) , .... , ColN

    From inserted i;

    End

    Well I see there is a scalar function in there. I also have never been a big fan of (well ok actually it is something I really hate) silently changing input like that. IMHO you should never just decide to change input data except in some pretty rare situations.

    That is the least of the issues I think I see. What if this code is triggered by an UPDATE?

    Yeah that one too. Of course if you have turned off recursive triggers it at least won't crash the system. https://msdn.microsoft.com/en-us/library/bb522682.aspx

    Instead of triggers don't fire recursively. An insert on the table fires the instead of the trigger, the insert inside the instead of trigger will not fire the instead of trigger again, so that isn't a problem.

    Fun part, this is where they got this idea:

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1041e4b3-5f97-4996-a59f-481a08a5a24f/how-to-change-the-special-character-when-inserted-in-to-database?forum=transactsql

    Maybe they should add a column for soft deletes and then it won't matter that you have dozens of nearly identical rows....kidding of course. That thing is rather a mess for sure.

    _______________________________________________________________

    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/

  • Any of you heavy hitters looking for a DBA (they need a good one) in San Diego? It's for one of our sister companies.

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lynn Pettis (6/20/2016)


    Sorry for the technical question in The Thread, but I just have to ask everyone around the water cooler what they think.

    Does anyone else see a problem with the following code?

    Create Trigger tr_TriggerName on dbo.OperationsSIGACTTable Instead Of Insert, Update

    AS

    Begin

    Insert Into OperationsSIGACTTable (Col1, Col2, Summary, .... , ColN)

    Select Col1, Col2, RemoveSpecialChars(Summary) , .... , ColN

    From inserted i;

    End

    I was going to snigger and snort and gfaw over this one, but after reading the following posts I see the issue has already been pointed out.

    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 (6/20/2016)


    Sean Lange (6/20/2016)


    Lynn Pettis (6/20/2016)


    Sorry for the technical question in The Thread, but I just have to ask everyone around the water cooler what they think.

    Does anyone else see a problem with the following code?

    Create Trigger tr_TriggerName on dbo.OperationsSIGACTTable Instead Of Insert, Update

    AS

    Begin

    Insert Into OperationsSIGACTTable (Col1, Col2, Summary, .... , ColN)

    Select Col1, Col2, RemoveSpecialChars(Summary) , .... , ColN

    From inserted i;

    End

    Well I see there is a scalar function in there. I also have never been a big fan of (well ok actually it is something I really hate) silently changing input like that. IMHO you should never just decide to change input data except in some pretty rare situations.

    That is the least of the issues I think I see. What if this code is triggered by an UPDATE?

    Whoever thought that trigger up must be crazy.

    If there is a primary key or any candidate key defined by an UNIQUE constraint on a set of one or more NOT NULL columns no update can work unless for each row affected it either modifies all such keys or modifies nothing; and if it modifies all those keys, it still doesn't work because the original unmodified rows are still there - in this case in effect it acts as a shorthand for an weird insert, not an update. That's also roughly what it does in the case where the table has no primary or candidate keys, but only roughly because there are additional complications if there are unique constraints on columns sets with some of the columns allowing NULLs.

    Unless of course this was intended to be just a quick and dirty illustration of the core of a way to do the insert part with a trigger, with the update part to be produced by analogy, rather than a full answer.

    Tom

  • Geez, where the heck has the year gone?

    We're just under 2 weeks out from the half-way mark of the year...

  • jasona.work (6/21/2016)


    Geez, where the heck has the year gone?

    We're just under 2 weeks out from the half-way mark of the year...

    Absolute time vs relative time, or don't clock-watch when you're hungry ๐Ÿ˜€

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • jasona.work (6/21/2016)


    Geez, where the heck has the year gone?

    We're just under 2 weeks out from the half-way mark of the year...

    And I'm only at 56,000 miles so far.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Brandie Tarvin (6/21/2016)


    Lynn Pettis (6/20/2016)


    Sorry for the technical question in The Thread, but I just have to ask everyone around the water cooler what they think.

    Does anyone else see a problem with the following code?

    Create Trigger tr_TriggerName on dbo.OperationsSIGACTTable Instead Of Insert, Update

    AS

    Begin

    Insert Into OperationsSIGACTTable (Col1, Col2, Summary, .... , ColN)

    Select Col1, Col2, RemoveSpecialChars(Summary) , .... , ColN

    From inserted i;

    End

    I was going to snigger and snort and gfaw over this one, but after reading the following posts I see the issue has already been pointed out.

    This is what happens when DBA wanna bees do knee jerk reactions to solve data issues without thinking or talking to the only DBA in the company. Trust me, yesterday was not a good day for me, I actually talked out loud about maybe looking for a new job. I got talked down from that ledge.

  • ChrisM@Work (6/21/2016)


    jasona.work (6/21/2016)


    Geez, where the heck has the year gone?

    We're just under 2 weeks out from the half-way mark of the year...

    Absolute time vs relative time, or don't clock-watch when you're hungry ๐Ÿ˜€

    Somebody's been misusing DBCC TIMEWARP() again. I keep telling you guys, switch 666 doesn't mean what you think it means.

    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.

  • Brandie Tarvin (6/21/2016)


    ChrisM@Work (6/21/2016)


    jasona.work (6/21/2016)


    Geez, where the heck has the year gone?

    We're just under 2 weeks out from the half-way mark of the year...

    Absolute time vs relative time, or don't clock-watch when you're hungry ๐Ÿ˜€

    Somebody's been misusing DBCC TIMEWARP() again. I keep telling you guys, switch 666 doesn't mean what you think it means.

    Just to make sure, before I try it and see, does switch [Square root of e] summon Cthulu from Ry'Leh?

    I'm thinking about ending the world and that seems like a fun way to do it...

  • jasona.work (6/21/2016)


    Brandie Tarvin (6/21/2016)


    ChrisM@Work (6/21/2016)


    jasona.work (6/21/2016)


    Geez, where the heck has the year gone?

    We're just under 2 weeks out from the half-way mark of the year...

    Absolute time vs relative time, or don't clock-watch when you're hungry ๐Ÿ˜€

    Somebody's been misusing DBCC TIMEWARP() again. I keep telling you guys, switch 666 doesn't mean what you think it means.

    Just to make sure, before I try it and see, does switch [Square root of e] summon Cthulu from Ry'Leh?

    I'm thinking about ending the world and that seems like a fun way to do it...

    Jason, stop working for a moment and look outside. It's way too nice of a day outside to end the world today. Check back again later.

  • Ed Wagner (6/21/2016)


    jasona.work (6/21/2016)


    Brandie Tarvin (6/21/2016)


    ChrisM@Work (6/21/2016)


    jasona.work (6/21/2016)


    Geez, where the heck has the year gone?

    We're just under 2 weeks out from the half-way mark of the year...

    Absolute time vs relative time, or don't clock-watch when you're hungry ๐Ÿ˜€

    Somebody's been misusing DBCC TIMEWARP() again. I keep telling you guys, switch 666 doesn't mean what you think it means.

    Just to make sure, before I try it and see, does switch [Square root of e] summon Cthulu from Ry'Leh?

    I'm thinking about ending the world and that seems like a fun way to do it...

    Jason, stop working for a moment and look outside. It's way too nice of a day outside to end the world today. Check back again later.

    Yeah, I just came in from a walk around the parking lot...

    I was just looking to get out of work early...

    Maybe I can figure out what the switch is to free the Great Old One that I just *KNOW* is imprisoned below this place...

  • Lynn Pettis (6/21/2016)


    Brandie Tarvin (6/21/2016)


    Lynn Pettis (6/20/2016)


    Sorry for the technical question in The Thread, but I just have to ask everyone around the water cooler what they think.

    Does anyone else see a problem with the following code?

    Create Trigger tr_TriggerName on dbo.OperationsSIGACTTable Instead Of Insert, Update

    AS

    Begin

    Insert Into OperationsSIGACTTable (Col1, Col2, Summary, .... , ColN)

    Select Col1, Col2, RemoveSpecialChars(Summary) , .... , ColN

    From inserted i;

    End

    I was going to snigger and snort and gfaw over this one, but after reading the following posts I see the issue has already been pointed out.

    This is what happens when DBA wanna bees do knee jerk reactions to solve data issues without thinking or talking to the only DBA in the company. Trust me, yesterday was not a good day for me, I actually talked out loud about maybe looking for a new job. I got talked down from that ledge.

    Could somebody explain what's wrong with it please? I know nothing about triggers. I'm the reporting guy so they're not the kind of thing I come across. I've never had any call to use one but I'd be in grave danger of falling into whatever traps lie within that it if I ever needed a trigger.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    โ€”Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • BWFC (6/22/2016)


    Lynn Pettis (6/21/2016)


    Brandie Tarvin (6/21/2016)


    Lynn Pettis (6/20/2016)


    Sorry for the technical question in The Thread, but I just have to ask everyone around the water cooler what they think.

    Does anyone else see a problem with the following code?

    Create Trigger tr_TriggerName on dbo.OperationsSIGACTTable Instead Of Insert, Update

    AS

    Begin

    Insert Into OperationsSIGACTTable (Col1, Col2, Summary, .... , ColN)

    Select Col1, Col2, RemoveSpecialChars(Summary) , .... , ColN

    From inserted i;

    End

    I was going to snigger and snort and gfaw over this one, but after reading the following posts I see the issue has already been pointed out.

    This is what happens when DBA wanna bees do knee jerk reactions to solve data issues without thinking or talking to the only DBA in the company. Trust me, yesterday was not a good day for me, I actually talked out loud about maybe looking for a new job. I got talked down from that ledge.

    Could somebody explain what's wrong with it please? I know nothing about triggers. I'm the reporting guy so they're not the kind of thing I come across. I've never had any call to use one but I'd be in grave danger of falling into whatever traps lie within that it if I ever needed a trigger.

    "Instead Of Insert, Update" means that when someone tries to insert data into or update data in table dbo.OperationsSIGACTTable, the trigger will rollback the insert / update and then log the history of the attempted insert somewhere. This is a good idea in practice. The OP, however, has chosen the exact same table in which to insert the attempted data insert/update. Which will yet again trigger the trigger if recursive triggers are allowed. Think of it as an infinite loop kind of process.

    If you have a trigger preventing inserts that is then trying to do its own insert, it's a self-defeating process. And updates are just as bad.

    The "inserted" table the SQL is pulling from is a non-documented "invisible" table that only exists for the length of the transaction. Everything that gets inserted / updated finds itself in the inserted table until the next update / insert happens elsewhere. There is also a "deleted" table that does the same thing for deletes and updates (because an update is actually a delete followed by an insert that we never see).

    Does that help?

    EDIT: Here is a TechNet article on SQL 2008 R2 INSTEAD OF triggers. Nothing's really changed in SQL 2012 that I can tell.

    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.

  • Brandie Tarvin (6/22/2016)


    BWFC (6/22/2016)


    Lynn Pettis (6/21/2016)


    Brandie Tarvin (6/21/2016)


    Lynn Pettis (6/20/2016)


    Sorry for the technical question in The Thread, but I just have to ask everyone around the water cooler what they think.

    Does anyone else see a problem with the following code?

    Create Trigger tr_TriggerName on dbo.OperationsSIGACTTable Instead Of Insert, Update

    AS

    Begin

    Insert Into OperationsSIGACTTable (Col1, Col2, Summary, .... , ColN)

    Select Col1, Col2, RemoveSpecialChars(Summary) , .... , ColN

    From inserted i;

    End

    I was going to snigger and snort and gfaw over this one, but after reading the following posts I see the issue has already been pointed out.

    This is what happens when DBA wanna bees do knee jerk reactions to solve data issues without thinking or talking to the only DBA in the company. Trust me, yesterday was not a good day for me, I actually talked out loud about maybe looking for a new job. I got talked down from that ledge.

    Could somebody explain what's wrong with it please? I know nothing about triggers. I'm the reporting guy so they're not the kind of thing I come across. I've never had any call to use one but I'd be in grave danger of falling into whatever traps lie within that it if I ever needed a trigger.

    "Instead Of Insert, Update" means that when someone tries to insert data into or update data in table dbo.OperationsSIGACTTable, the trigger will rollback the insert / update and then log the history of the attempted insert somewhere. This is a good idea in practice. The OP, however, has chosen the exact same table in which to insert the attempted data insert/update. Which will yet again trigger the trigger if recursive triggers are allowed. Think of it as an infinite loop kind of process.

    If you have a trigger preventing inserts that is then trying to do its own insert, it's a self-defeating process. And updates are just as bad.

    The "inserted" table the SQL is pulling from is a non-documented "invisible" table that only exists for the length of the transaction. Everything that gets inserted / updated finds itself in the inserted table until the next update / insert happens elsewhere. There is also a "deleted" table that does the same thing for deletes and updates (because an update is actually a delete followed by an insert that we never see).

    Does that help?

    EDIT: Here is a TechNet article on SQL 2008 R2 INSTEAD OF triggers. Nothing's really changed in SQL 2012 that I can tell.

    Thanks Brandie, that's cleared it up nicely. Like I said, they're not the kind of thing I use day to day.


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    โ€”Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 15 posts - 54,646 through 54,660 (of 66,688 total)

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