<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Rahul Kr. Ghosh
»
Automated Trigger To Require a WHERE Clause
87 posts, Page 2 of 9
««
1
2
3
4
5
»
»»
Automated Trigger To Require a WHERE Clause
Rate Topic
Display Mode
Topic Options
Author
Message
darren.beevers
darren.beevers
Posted Tuesday, January 25, 2011 3:13 AM
Forum Newbie
Group: General Forum Members
Last Login: Friday, December 23, 2011 1:13 AM
Points: 1,
Visits: 5
What is the performance hit for this. I administer some databases with 100 million records, being hit millions of time a day, there are stored procedures that have updates, and deletes aplenty. If every one of these is causing this trigger to fire, I am sure there will be a performance cost?
Darren Beevers
Post #1052969
mister.magoo
mister.magoo
Posted Tuesday, January 25, 2011 3:21 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 1,287,
Visits: 3,850
I appreciate your hard work and thank you for sharing - as with all these things I hope that the outcome of the discussion will be a revised article with even better code.
If I am not mistaken (which I often am
) I could set off an update without a where clause and someone else could insert records while the update is running.
The original update may update fewer than the (new) total number of rows now in the table and allow the update....
Also, the title is misleading - there is no requirement for a WHERE clause enforced by this trigger.
MM
Post #1052971
babu-722738
babu-722738
Posted Tuesday, January 25, 2011 3:25 AM
Forum Newbie
Group: General Forum Members
Last Login: Sunday, January 30, 2011 5:25 PM
Points: 4,
Visits: 25
The problem statement is weak as no developer will usually be allowed to issue random Update statements on a financial production environment. Also a developer who forgets to include a WHERE clause may make mistake in forming the WHERE clause and still can cause updating wrong set of data. So, you may consider copying the records to an AUDIT table from DELETED set in your trigger for auditing and reconciliation purpose rather than focusing on row_count.
-SB
Post #1052973
Carlo Romagnano
Carlo Romagnano
Posted Tuesday, January 25, 2011 3:50 AM
SSCommitted
Group: General Forum Members
Last Login: Yesterday @ 2:04 AM
Points: 1,968,
Visits: 1,819
luciano furlan (1/25/2011)
It is a smart workaround, but it doesn't really solve the problem in an elegant way.
I miss a configuration on Sql server that states "Where Clause mandatory". For all tables, for all Sql statements.
I fear every day, that I will forget the where clause eventually and destroy an entire table.
And guess what: Sql doesn't have an Undo button!
But still if you need (and you will need) you can write "Where 1=1", to update/delete every record in a table.
Your workaround treats it like a "missing where".
You can see the difference between an accidentaly forgoten where and one you written on purpose.
I believe this is a huge "security hole" in the Sql language. And yet it stays with us for so many years (and so many tears).
I disagree: what about using transactions?
In general, this trigger affects performance just to prevent programmers errors.
Before any DDL or MDL I use the fantastic "SET IMPLICIT_TRANSACTIONS ON".
and only when I sure of modification I run the COMMIT command.
Post #1052981
Rob Fisk
Rob Fisk
Posted Tuesday, January 25, 2011 4:35 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, June 29, 2012 4:56 AM
Points: 163,
Visits: 427
carlos.ramon.martinez (1/25/2011)
Hello,
just to finish undestanding, I would like to know in which cases @@rowcount can be greater than the number of rows. If anybody could explain I would appreciate.
Kind regards
Rowcount can be greater than the number of rows listed in the statistics if the statistics have not yet been updated for recently added rows.
_______________________________________________________
Change is inevitable... Except from a vending machine.
Post #1052996
jts_2003
jts_2003
Posted Tuesday, January 25, 2011 4:36 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 12:15 PM
Points: 1,060,
Visits: 4,156
A very interesting article, which has stirred up some great debate. I know use BEGIN TRANSACTION whenever running updates - even on test.
What would be great is an easy way of backing up (and restoring) just a table... maybe in Denali?
Post #1052998
GilaMonster
GilaMonster
Posted Tuesday, January 25, 2011 4:40 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 37,637,
Visits: 29,891
Rob Fisk (1/25/2011)
Rowcount can be greater than the number of rows listed in the statistics if the statistics have not yet been updated for recently added rows.
sys.dm_db_partition_stats is not based on index statistics. It's the storage engine's information on the total rows in the table and should be correct in the vast majority of cases.
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1053002
GilaMonster
GilaMonster
Posted Tuesday, January 25, 2011 4:43 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:45 AM
Points: 37,637,
Visits: 29,891
jts_2003 (1/25/2011)
What would be great is an easy way of backing up (and restoring) just a table... maybe in Denali?
No, and I doubt it will ever be a native feature. It allows the database to be in a transactionally inconsistent state. Lots of the 3rd party backup tools can do that, and others allow the backups to be 'mounted' as if they were actual databases (RedGate's Hyperbac for eg)
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1053003
Rob Fisk
Rob Fisk
Posted Tuesday, January 25, 2011 4:49 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Friday, June 29, 2012 4:56 AM
Points: 163,
Visits: 427
GilaMonster (1/25/2011)
jts_2003 (1/25/2011)
What would be great is an easy way of backing up (and restoring) just a table... maybe in Denali?
No, and I doubt it will ever be a native feature. It allows the database to be in a transactionally inconsistent state. Lots of the 3rd party backup tools can do that, and others allow the backups to be 'mounted' as if they were actual databases (RedGate's Hyperbac for eg)
I keep a DB called Guff I use for importing data I need to compare to data in my other DBs (before I arrived the old way was just to import them to the production DBs so there were tables like stuffJan, GavsTable3 all over the shop).
If I am about to muck about with lots of updates to a table I often just export it to Guff beforehand so that I can run data comparisons and so on afterwards.
_______________________________________________________
Change is inevitable... Except from a vending machine.
Post #1053006
Dwayne Dibley
Dwayne Dibley
Posted Tuesday, January 25, 2011 5:20 AM
Old Hand
Group: General Forum Members
Last Login: 2 days ago @ 7:41 AM
Points: 365,
Visits: 2,900
jts_2003 (1/25/2011)
What would be great is an easy way of backing up (and restoring) just a table... maybe in Denali?
I'm pretty sure that this feature was available in 6.5. It was removed in 7 and onwards.
One of the problems for table restores is in handling rows with foreign key relationships.
Post #1053022
« Prev Topic
|
Next Topic »
87 posts, Page 2 of 9
««
1
2
3
4
5
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
