This is a variation of the code shared in the this post here
. Mladen Prajdic (Blog
) suggested I use an INSTEAD OF trigger and eliminate the ROLLBACK. The following code shows this idea. Thanks for the suggestion Mladen!
There appears to be an annoying side affect associated with INSTEAD OF triggers. When performing INSERT, UPDATE, and DELETE operations against MyTable when the INSTEAD OF trigger is enable, you see the standard "X row(s) affected" messages if NOCOUNT is set to OFF (default). This message is showing counts greater than zero (0) even when no records are affected. It is showing the counts that would have been affected if the trigger didn't exist or were disabled. Interesting behavior. Might possibly need to expand the error message in some way to communicate this side affect so that users attempting data modifications don't think their operations succeeded even though they get the "data modifications blocked" error message.
Please share any ideas or feedback that you may have. Thanks!
CREATE TRIGGER dbo.tr_MyTable_StopDataMods
INSTEAD OF INSERT, DELETE, UPDATE
SET NOCOUNT ON;
N'The data in table %s is static. All data modification operations are blocked. Disable the trigger %s to modify table data.', -- Message text.
16, -- Severity
1, -- State,
'dbo.MyTable', -- first argument (table name)
'dbo.tr_MyTable_StopDataMods' -- second argument (trigger name)
-- Code to temporarily disable trigger
ALTER TABLE dbo.MyTable DISABLE TRIGGER tr_MyTable_StopDataMods;
-- Code to reenable trigger
ALTER TABLE dbo.MyTable ENABLE TRIGGER tr_MyTable_StopDataMods;
Code tested only on SQL Server 2008 Dev Ed.