May 14, 2012 at 7:12 am
dwain.c (5/13/2012)
Sean Lange (5/11/2012)
Sometime in the somewhat recent past somebody shared a story about a company that went belly up due to their triggers not supporting multiple rows. I brought this up in a recent thread and Dwain was interested to know more about it. Apparently he recently fixed all the triggers at his company because none of them supported multiple row activity. Does anybody remember much about that story or who it was from? If so, care to share the story again?Sean - thanks for trying. Actually it wasn't all triggers at my company, just all triggers in an app my company supports for another. And possibly not all at that (but most).
I'd seriously be interested in any kind of article where single use triggers caused a company financial harm of any sort.
Oh and BTW. I've been using the below terminology. If anyone knows of different, more widely accepted terminology, I'd appreciate knowing what that is.
Single use trigger - A trigger that operates only on a single (i.e., the last) record INSERTed, UPDATEd or DELETEd.
Bulk trigger - A trigger that operates for all records INSERTed, UPDATEd or DELETEd.
The only thing I"d quibble with is where you say "(i.e., the last) record" as ordering is not guaranteed in SQL Server so you aren't guaranteed that the last row affected will be the one processed by the trigger.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2012 at 7:23 am
Jack Corbett (5/14/2012)
dwain.c (5/13/2012)
Sean Lange (5/11/2012)
Sometime in the somewhat recent past somebody shared a story about a company that went belly up due to their triggers not supporting multiple rows. I brought this up in a recent thread and Dwain was interested to know more about it. Apparently he recently fixed all the triggers at his company because none of them supported multiple row activity. Does anybody remember much about that story or who it was from? If so, care to share the story again?Sean - thanks for trying. Actually it wasn't all triggers at my company, just all triggers in an app my company supports for another. And possibly not all at that (but most).
I'd seriously be interested in any kind of article where single use triggers caused a company financial harm of any sort.
Oh and BTW. I've been using the below terminology. If anyone knows of different, more widely accepted terminology, I'd appreciate knowing what that is.
Single use trigger - A trigger that operates only on a single (i.e., the last) record INSERTed, UPDATEd or DELETEd.
Bulk trigger - A trigger that operates for all records INSERTed, UPDATEd or DELETEd.
The only thing I"d quibble with is where you say "(i.e., the last) record" as ordering is not guaranteed in SQL Server so you aren't guaranteed that the last row affected will be the one processed by the trigger.
I'd agree with you here.
I've seen triggers where data was retrieved from both the inserted and deleted virtual tables into variables, without an ORDER BY clause. The assumption being that there was just one record. With multiple records, the variables could be populated from data for different "widgets" (or whatever the PK is for that table).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 14, 2012 at 11:22 am
Wahoo! Just got a note stating that Jason is teaching our users group meeting today. Unfortunately, I'll be the guy that shows up late and has to leave early (please don't be offended), but I'll be there!
Chad
May 14, 2012 at 11:43 am
Jeff Moden (5/14/2012)
Brandie Tarvin (5/14/2012)
dwain.c (5/13/2012)
Oh and BTW. I've been using the below terminology. If anyone knows of different, more widely accepted terminology, I'd appreciate knowing what that is.Single use trigger - A trigger that operates only on a single (i.e., the last) record INSERTed, UPDATEd or DELETEd.
Bulk trigger - A trigger that operates for all records INSERTed, UPDATEd or DELETEd.
I would use the terms Single Row (or record) Trigger and Set-Based Trigger if you want no misunderstandings.
I'd likely use the terms "improperly written single row trigger" and "properly written multi-row-capable trigger". Or, "broken" and "properly written" for short. 😉
Actually, there are three kinds: "bizarre", "broken", and "proper". 😉
This is because there is also such a thing (bizarre though it clearly is) as a "properly written single row trigger". :angry:
There probably ought not to be, but there is.
It starts something like this:-
create trigger tab_update on TAB for update as
if 1<>select count(*) from deleted
begin
rollback -- the businness rules forbid multiple row updates to TAB
declare @details varchar(256)
set @details = <something involving APP_NAME, DBCC INPUT_BUFFER, and so forth)
raiserror(50123,18,1,@details) with LOG
end
else
begin
-- deal with single row update
....
....
end
edit: if the business rules are that bizarre, the spelling rules are too. So it's no surprise to see business spelled "businness" :hehe:.
Tom
May 14, 2012 at 11:43 am
Jack Corbett (5/14/2012)
RBarryYoung (5/10/2012)
Koen Verbeeck (5/10/2012)
Stefan Krzywicki (5/10/2012)
Yeah, I created the import package manually. I ended up getting the table structures by using an openquery in a cursor loop through a list of the Oracle tables obtained with a query against all_tables. It is slow, it is tedious, but it is more reliable. I worked out the permissions, needed to either encrypt the package with a password instead of a user key or set up a proxy, neither of which I'd done before, so at least I'm learning new things!
Never encrypt with user key in SSIS, it only gives troubles ahead, as you probably figured out by now 😀
My experience has been that "Don't Save Sensitive.." and relying on Trusted Connections is by far the easiest way to go.
Wow, the long lost R. Barry Young, returns. Good to hear from you again.
You too, Jack. Are you still at New Tribes down in Florida?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 14, 2012 at 12:12 pm
RBarryYoung (5/14/2012)
Jack Corbett (5/14/2012)
RBarryYoung (5/10/2012)
Koen Verbeeck (5/10/2012)
Stefan Krzywicki (5/10/2012)
Yeah, I created the import package manually. I ended up getting the table structures by using an openquery in a cursor loop through a list of the Oracle tables obtained with a query against all_tables. It is slow, it is tedious, but it is more reliable. I worked out the permissions, needed to either encrypt the package with a password instead of a user key or set up a proxy, neither of which I'd done before, so at least I'm learning new things!
Never encrypt with user key in SSIS, it only gives troubles ahead, as you probably figured out by now 😀
My experience has been that "Don't Save Sensitive.." and relying on Trusted Connections is by far the easiest way to go.
Wow, the long lost R. Barry Young, returns. Good to hear from you again.
You too, Jack. Are you still at New Tribes down in Florida?
Nope, not in with New Tribes nor in Florida. Moved back to NH last summer and now telecommute for NextWave Logisitcs based out of Chicago. Title is Sr. DBA, but really working as a database developer. I haven't done a backup, restore, or anythign with security since I've been there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 14, 2012 at 1:14 pm
All ye All ye, Now hear this
How about commenting on opc.three's post to encourage / discourage him in writing a spackle article:
http://www.sqlservercentral.com/Forums/Topic1298803-1292-2.aspx#bm1299757
May 14, 2012 at 2:58 pm
Jack Corbett (5/14/2012)
I haven't done a backup, restore, or anythign with security since I've been there.
Whaaa? No backups of your laptop? None of your music? No pictures?
😉
May 14, 2012 at 3:01 pm
Steve Jones - SSC Editor (5/14/2012)
Jack Corbett (5/14/2012)
I haven't done a backup, restore, or anythign with security since I've been there.Whaaa? No backups of your laptop? None of your music? No pictures?
Backups? Highly overrated, why would anyone bother?
</tongue in cheek>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2012 at 3:45 pm
GilaMonster (5/14/2012)
Steve Jones - SSC Editor (5/14/2012)
Jack Corbett (5/14/2012)
I haven't done a backup, restore, or anythign with security since I've been there.Whaaa? No backups of your laptop? None of your music? No pictures?
Backups? Highly overrated, why would anyone bother?
</tongue in cheek>
Actually, you are right - any decent Platform as a Service does it for you behind the scenes. (Although in Azure it is called "cloning.")
May 14, 2012 at 4:44 pm
Revenant (5/14/2012)
GilaMonster (5/14/2012)
Steve Jones - SSC Editor (5/14/2012)
Jack Corbett (5/14/2012)
I haven't done a backup, restore, or anythign with security since I've been there.Whaaa? No backups of your laptop? None of your music? No pictures?
Backups? Highly overrated, why would anyone bother?
</tongue in cheek>
Actually, you are right - any decent Platform as a Service does it for you behind the scenes. (Although in Azure it is called "cloning.")
clone/snapshot != backup.
If I cannot restore to an earlier point in time to recover from things like accidental deletes, it is a HA tool, not a backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2012 at 5:18 pm
GilaMonster (5/14/2012)
Revenant (5/14/2012)
GilaMonster (5/14/2012)
Steve Jones - SSC Editor (5/14/2012)
Jack Corbett (5/14/2012)
I haven't done a backup, restore, or anythign with security since I've been there.Whaaa? No backups of your laptop? None of your music? No pictures?
Backups? Highly overrated, why would anyone bother?
</tongue in cheek>
Actually, you are right - any decent Platform as a Service does it for you behind the scenes. (Although in Azure it is called "cloning.")
clone/snapshot != backup.
If I cannot restore to an earlier point in time to recover from things like accidental deletes, it is a HA tool, not a backup.
It depends. 🙂
You do not have to delete data, you can mark rows as inactive or obsolete, which of course allows undo.
I think that by the time the next version of SQLS hits the market (2015?), no-deletes and behind the scenes cloning of SQLS VMs will be common practice.
I admit that I am probably biased, because I am on BI and OI and data are facts for posteriority and we only seldom delete data.
May 14, 2012 at 5:24 pm
I see what you are saying and clone/snapshots make some backup scenarios work, but changes, incorrect loads, any number of issues occur that require backups. I suppose logs work, but logs go across time, so we'd need to make sure that any snapshot/clone would allow reversal of items since the previous snapshot/clone.
Overall, I agree with Gail. I think snapshot/clones are a partial solution and not a good solution for database backups. Hopefully SQL Server will think this through thoroughly.
May 14, 2012 at 5:25 pm
Revenant (5/14/2012)
You do not have to delete data, you can mark rows as inactive or obsolete, which of course allows undo.
Which won't help an iota when a DBA connected to the wrong environment issues a truncate table. Stuff happens, from deletes or updates without the where clause, hacks (SQL injection), malicious actions, to auditors wanting to see the data exactly as it was at last year-end.
I think that by the time the next version of SQLS hits the market (2015?), no-deletes and behind the scenes cloning of SQLS VMs will be common practice.
Personally, I disagree, especially in OLTP environments. For OLTP 'logical' deletes make all queries more complex, and you've got the storage volumes to consider as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2012 at 6:18 pm
Jack Corbett (5/14/2012)
The only thing I"d quibble with is where you say "(i.e., the last) record" as ordering is not guaranteed in SQL Server so you aren't guaranteed that the last row affected will be the one processed by the trigger.
I actually thought about that when I wrote it. The only experience I've had with them, and that is admittedly limited, was applying the operation on the last record in the INSERT. It was that one experience that led me to rewrite them all.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 36,076 through 36,090 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply