Create Trigger to prevent bulk update of table

Sometimes you need some protection in place on your important database tables to prvent an accidental bulk update of the table from such a query:

To help prevent such as accident we can create a trigger(s) on the table(s) in question, for the example in this article I am using the following test database:

http://tsql.solidq.com/SampleDatabases/TSQLV4.zip

I first started by creating a duplicate copy of the following table ‘Sales.Customers’  

I then created a Clustered Index, becuase the SELECT INTO does not copy indexes:

I then created the Trigger,  the @@ROWCOUNT is used to get a count of how many rows would be affected, I can then take this and compare the number with the actual number of rows in the table by querying sys.dm_db_partition_stats  if the two match or is grater then raiser an error and rollback the transaction.

 

Leave a Reply

Your email address will not be published. Required fields are marked *