SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automated Trigger To Require a WHERE Clause


Automated Trigger To Require a WHERE Clause

Author
Message
darren.beevers
darren.beevers
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
mister.magoo
mister.magoo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10841 Visits: 7891
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 :-D) 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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • babu-722738
    babu-722738
    Forum Newbie
    Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

    Group: General Forum Members
    Points: 6 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
    Carlo Romagnano
    Carlo Romagnano
    SSCertifiable
    SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

    Group: General Forum Members
    Points: 7594 Visits: 3400
    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.

    I run on tuttopodismo
    Rob Fisk
    Rob Fisk
    SSC-Addicted
    SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

    Group: General Forum Members
    Points: 435 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.

    jts2013
    jts2013
    SSCommitted
    SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

    Group: General Forum Members
    Points: 1536 Visits: 5009
    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?
    GilaMonster
    GilaMonster
    SSC Guru
    SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

    Group: General Forum Members
    Points: 230128 Visits: 46344
    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, MVP, M.Sc (Comp Sci)
    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


    GilaMonster
    GilaMonster
    SSC Guru
    SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)SSC Guru (230K reputation)

    Group: General Forum Members
    Points: 230128 Visits: 46344
    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, MVP, M.Sc (Comp Sci)
    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


    Rob Fisk
    Rob Fisk
    SSC-Addicted
    SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)SSC-Addicted (435 reputation)

    Group: General Forum Members
    Points: 435 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.

    Dwayne Dibley
    Dwayne Dibley
    Right there with Babe
    Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

    Group: General Forum Members
    Points: 722 Visits: 3171
    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.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search