SQL Server Performance

auto Abort query if update/delete record more than 1000, possible ?

Discussion in 'SQL Server 2008 General Developer Questions' started by yhchan2005, Aug 11, 2011.

  1. yhchan2005 Member

    hi,

    recently i have make a mistake which i forgot to put in the 'where' statement and cause the all record from a table ( around 3 million ) updated, i have read some post which we can use the Top to limit the record updated / deleted but this is not what i want. What i need is the SQL Server will auto cancel my query if detected the affected record is more than 1000, is it possible ?

    * most of the time i will use begin tran and rollbak to check the number of record affected before run the query but sometime it is really forgot to do this step and cause all record updated.
    yhchan2005, Aug 11, 2011
    #1

  2. satya Moderator

    Its not possible to cancel a query, rather you need to setup a trigger in this case to capture the number of rows that are modified/deleted in the query and kill the corresponding SPID.
    satya, Aug 12, 2011
    #2

  3. yhchan2005 Member

    thanks for your reply and can you share your idea how to do this in trigger ?
    yhchan2005, Aug 12, 2011
    #3

  4. satya Moderator

    Look at the Books online for Triggers functionality, keep an eye on DELETED/INSERTED tables and you have to write your own TSQL to capture the info tho.
    satya, Aug 16, 2011
    #4

  5. Madhivanan Moderator

    You can create after delete trigger to check for the row numbers
    If (select count(*) from deleted)>1000
    rollback
    Madhivanan, Aug 18, 2011
    #5

Share This Page