Bluedog67

Random Thoughts About SQL Server and My Dog Blue

About Bluedog67

My name is Stephen Horne. I am a well-trained owner of an Australian Cattle Dog named Blue. I also develop software mainly using SQL Server, ASP.NET, and C#. I love data, databases, T-SQL, and turning raw data into actionable information. My plan for this blog is to write about SQL Server and sometimes my dog Blue. Please contact me at stephen at bluedog67 dot com. Follow me on Twitter at bluedog67.

Blocking Data Modifications Version 2

This is a variation of the code shared in the this post here. Mladen Prajdic (Blog|Twitter) 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
ON dbo.MyTable
INSTEAD OF INSERT, DELETE, UPDATE
AS
BEGIN

SET NOCOUNT ON;

RAISERROR(
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)
 );

END
GO

-- Code to temporarily disable trigger
 ALTER TABLE dbo.MyTable DISABLE TRIGGER tr_MyTable_StopDataMods;
GO

-- Code to reenable trigger
 ALTER TABLE dbo.MyTable ENABLE TRIGGER tr_MyTable_StopDataMods;
GO

Code tested only on SQL Server 2008 Dev Ed.

Posted by stephen on Thursday, January 07, 2010 7:54 PM
E-mail | Kick it! | DZone it! | del.icio.us
Permalink | Comments (4) | Post RSSRSS comment feed

Comments

sqlrunner United States

Thursday, January 07, 2010 8:31 PM

sqlrunner

There are two ways to look at coding.  Do you code anticipating the failure or do you code with failure being considered a rare occurrence?  With the AFTER trigger, the trigger fires AFTER insert into the table, which also means after any constraints (which is handy).  If the data is 'bad' you then need to do a rollback, which some will say is not a pleasant way to work.  I think it is acceptable since it is part of the whole 'transactional' model.  

Ted Krueger (onpnt) United States

Thursday, January 07, 2010 8:46 PM

Ted Krueger (onpnt)

I nice little trick to bypass the trigger for DBA's.  I learned this awhile ago from Andy Warren and a post on SSC here www.sqlservercentral.com/.../

This can be nice for not having to disable/enable.  I know I've made the mistake early in my playings to re-enable something after working on it.  this isn't fool proof on @@SPID but can be an added feature

Modified to add it in.  

CREATE TABLE dbo.MyTable (COL VARCHAR(10), IDENT INT IDENTITY(1,1) PRIMARY KEY)
GO
CREATE TABLE tr_MyTable_StopDataMods_DBA_CONFIG (SPID SMALLINT,ByPass smallint)
GO

CREATE TRIGGER dbo.tr_MyTable_StopDataMods
ON dbo.MyTable
INSTEAD OF INSERT, DELETE, UPDATE
AS
BEGIN
DECLARE @ByPass smallint
SELECT @ByPass = ByPass FROM tr_MyTable_StopDataMods_DBA_CONFIG
  IF (@ByPass IS NULL)
    BEGIN  
      RAISERROR(
      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)
      );
    END
  ELSE
    BEGIN
      TRUNCATE TABLE tr_MyTable_StopDataMods_DBA_CONFIG;
      RETURN;
    END
END
GO

INSERT INTO tr_MyTable_StopDataMods_DBA_CONFIG VALUES (@@SPID,1)
INSERT INTO dbo.MyTable SELECT REPLICATE('X',10)

Ted Krueger (onpnt) United States

Thursday, January 07, 2010 8:54 PM

Ted Krueger (onpnt)

Another method is to take advantage of schemas with permissions.  I typically place all of the objects I don't want anyone altering in any way into a schema setup with only sysadmin rights (DBA groups).  This means 1) the public user and developer areas will not be able to see them but possibly read and 2) prevent any alterations

This is the same as simple security methods to prevent everything but furthering it by managing the objects in the database for what grouping they belong to

Stephen Horne (bluedog67) United States

Friday, January 08, 2010 3:23 AM

Stephen Horne (bluedog67)

sqlrunner: Thanks for the feedback. As I indicated on Twitter I am much more comfortable with AFTER triggers too. I can understand INSTEAD OF triggers associated with a view where within the trigger you INSERT, UPDATE, DELETE data associated with multiple tables. I am ok with the AFTER trigger ROLLBACK too. The strange rows affected side affect for INSTEAD OF triggers is fairly problematic in the context of trying to block data modifications for a given table - this would likely be very confusing for users to see the data mods blocked error message but see >0 rows affected. Of the 2 variations I choose the AFTER trigger I believe.

Ted: Thanks for your replies. Interesting trick to bypass the trigger (Thanks to Andy Warren too). Thanks for sharing. Also thanks for sharing the idea for the sysadmin-only schema to isolate objects/data. I have used that in the past and will definitely use again in the future. Thanks!

Stephen Horne (bluedog67)