Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Automated Trigger To Require a WHERE Clause Expand / Collapse
Author
Message
Posted Tuesday, January 25, 2011 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:01 AM
Points: 1, Visits: 6
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
Posted Tuesday, January 25, 2011 3:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:49 PM
Points: 1,787, Visits: 5,722
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1052971
    Posted Tuesday, January 25, 2011 3:25 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, August 12, 2014 12:32 PM
    Points: 4, Visits: 26
    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
    Posted Tuesday, January 25, 2011 3:50 AM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Today @ 12:53 AM
    Points: 2,528, Visits: 2,402
    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
    Posted Tuesday, January 25, 2011 4:35 AM


    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Thursday, May 22, 2014 8:32 AM
    Points: 163, Visits: 428
    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
    Posted Tuesday, January 25, 2011 4:36 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Thursday, July 24, 2014 4:28 AM
    Points: 1,100, Visits: 4,898
    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
    Posted Tuesday, January 25, 2011 4:40 AM


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Today @ 12:15 PM
    Points: 43,017, Visits: 36,179
    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
    Posted Tuesday, January 25, 2011 4:43 AM


    SSC-Forever

    SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

    Group: General Forum Members
    Last Login: Today @ 12:15 PM
    Points: 43,017, Visits: 36,179
    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
    Posted Tuesday, January 25, 2011 4:49 AM


    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Thursday, May 22, 2014 8:32 AM
    Points: 163, Visits: 428
    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
    Posted Tuesday, January 25, 2011 5:20 AM


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Today @ 8:52 AM
    Points: 380, Visits: 3,070
    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 »

    Add to briefcase ««12345»»»

    Permissions Expand / Collapse