June 20, 2016 at 3:26 pm
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:
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/
June 20, 2016 at 5:26 pm
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
Change is inevitable... Change for the better is not.
June 21, 2016 at 5:08 am
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.
June 21, 2016 at 5:24 am
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
June 21, 2016 at 6:56 am
Geez, where the heck has the year gone?
We're just under 2 weeks out from the half-way mark of the year...
June 21, 2016 at 7:06 am
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 😀
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
June 21, 2016 at 7:16 am
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
June 21, 2016 at 7:30 am
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.
June 21, 2016 at 8:50 am
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.
June 21, 2016 at 8:55 am
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...
June 21, 2016 at 12:02 pm
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.
June 21, 2016 at 12:36 pm
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...
June 22, 2016 at 1:17 am
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.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 22, 2016 at 4:18 am
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.
June 22, 2016 at 4:56 am
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.
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,712 total)
You must be logged in to reply to this topic. Login to reply