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:
|
1 2 |
UPDATE sales.customers SET contactname = 'NewName' |
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’
|
1 2 3 |
select * into sales.customerstest from sales.customers |
I then created a Clustered Index, becuase the SELECT INTO does not copy indexes:
|
1 |
CREATE CLUSTERED INDEX PK_CustomersTest ON sales.customerstest(custid) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TRIGGER PreventBulkUpdate ON sales.customerstest FOR UPDATE AS BEGIN DECLARE @count int SET @count = @@ROWCOUNT; IF @Count >= (SELECT SUM(row_count) FROM sys.dm_db_partition_stats WHERE OBJECT_ID = OBJECT_ID('sales.customerstest') AND index_id = 1) BEGIN RAISERROR('Cannot update all rows',16, 1) ROLLBACK TRANSACTION RETURN; END END |
