{"id":1923,"date":"2012-05-01T16:49:46","date_gmt":"2012-05-01T16:49:46","guid":{"rendered":"http:\/\/craftydba.com\/?p=1923"},"modified":"2013-01-23T21:50:28","modified_gmt":"2013-01-23T21:50:28","slug":"various-triggers-part-3","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1923","title":{"rendered":"Various Triggers – Part 3"},"content":{"rendered":"

\"\"<\/a>A typical use of Data Modification Language (DML<\/a>) triggers is to prevent unwanted data modification. AFTER triggers are great at detecting the INSERT, UPDATE or DELETE action and rolling back the changes. <\/p>\n

A good security model can prevent these types of changes and should be the first line of defense. However, there are cases in which keys to the castle have to be given out for political reasons inside the organization. In that case, this type of preventive programming can be handy.<\/p>\n

Today, I am going to revisit the AUTOS database that I created for prior articles.<\/p>\n

-- Start with master database\r\nUSE MASTER;\r\nGO\r\n\r\n-- Delete existing databases.\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'AUTOS')\r\nDROP DATABASE [AUTOS]\r\nGO\r\n\r\n-- Create a autos database\r\nCREATE DATABASE AUTOS;\r\nGO\r\n\r\n-- Use the database\r\nUSE [AUTOS]\r\nGO\r\n\r\n-- Create a USA schema\r\nCREATE SCHEMA USA AUTHORIZATION dbo;\r\nGO\r\n\r\n-- Create a BRANDS table\r\nCREATE TABLE USA.BRANDS\r\n(\r\nMyId INT PRIMARY KEY CLUSTERED,\r\nMyValue VARCHAR(20)\r\n)\r\nGO\r\n\r\n-- Load the table with data\r\nINSERT INTO USA.BRANDS (MyId, MyValue) VALUES\r\n(1, 'Continental'),\r\n(2, 'Edsel'),\r\n(3, 'Lincoln'),\r\n(4, 'Mercury'),\r\n(5, 'Ram')\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
Triggers are part of the Data Definition Language (DDL<\/a>).  Use the CREATE TRIGGER<\/a>, ALTER TRIGGER<\/a>, and DROP TRIGGER<\/a> statements to manipulate the object.  By convention, I am prefixing my trigger with ‘TRG’.  Triggers can be turned on and off by using the DISABLE TRIGGER<\/a> and ENABLE TRIGGER<\/a> statements.<\/p>\n
The snipet below will create a TRIGGER that will prevent all changes on the [USA].[BRANDS] table.  A good use of this trigger is on small, semi-static tables.  A rollback on a large table will cause you other concerns such as unexpected transaction log file growth.<\/p>\n
-- Delete the existing trigger.\r\nIF EXISTS (select * from sysobjects where id = object_id('TRG_PREVENT_CHANGES') and type = 'TR')\r\n   DROP TRIGGER [USA].[TRG_PREVENT_CHANGES]\r\nGO\r\n\r\n-- Create the new trigger.\r\nCREATE TRIGGER [USA].[TRG_PREVENT_CHANGES] on [USA].[BRANDS]\r\nFOR INSERT, UPDATE, DELETE NOT FOR REPLICATION\r\nAS\r\n\r\nBEGIN\r\n\r\n    -- declare local variable\r\n    DECLARE @MYMSG VARCHAR(250);\r\n\r\n    -- nothing to do?\r\n    IF (@@rowcount = 0) RETURN;\r\n\r\n    -- do not count rows\r\n    SET NOCOUNT ON;\r\n\r\n    -- deleted data\r\n    IF NOT EXISTS (SELECT * FROM inserted) \r\n        BEGIN\r\n            SET @MYMSG = 'The read only [USA].[BRANDS] table does not allow records to be deleted!'\r\n            ROLLBACK TRANSACTION;\r\n            RAISERROR (@MyMsg, 15, 1);\r\n            RETURN;\r\n        END\r\n\r\n    ELSE \r\n        BEGIN\r\n        \r\n            -- inserted data\r\n            IF NOT EXISTS (SELECT * FROM deleted)  \t        \r\n                SET @MYMSG = 'The read only [USA].[BRANDS] table does not allow new records to be inserted!'\r\n                \r\n            -- updated data\r\n            ELSE\r\n                SET @MYMSG = 'The read only [USA].[BRANDS] table does not allow records to be updated!'\r\n\r\n            ROLLBACK TRANSACTION;\r\n            RAISERROR (@MyMsg, 15, 1);\r\n            RETURN;\r\n       END\r\n       \t\r\nEND\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
Deleting the ‘Lincoln’ entry from the table is the first test of firing the trigger.  As we can see, this action is rolled back.<\/p>\n
-- Try to delete a record\r\nDELETE FROM USA.BRANDS WHERE MyValue = 'Lincoln';\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
\"\"<\/a><\/p>\n
Updating the ‘Continental’ entry to ‘Ford’ is the second test of firing the trigger.  We get the same roll back.<\/p>\n
-- Try to update a record\r\nUPDATE USA.BRANDS SET MyValue = 'Ford' WHERE MyId = 1;\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
\"\"<\/a><\/p>\n
Inserting a ‘Ford’ entry into the table is the third test of firing the trigger.  Thus, the trigger reacts as expected.<\/p>\n
-- Try to insert a new record\r\nINSERT INTO USA.BRANDS (MyId, MyValue) VALUES (6, 'Ford');\r\nGO\r\n<\/span><\/pre>\n
 <\/p>\n
\"\"<\/a><\/p>\n
In summary, use SECURITY first to prevent unwanted changes by users and\/or admins.  If you find yourself in a situation in which junior colleuges have full access to the database, AFTER triggers might be worth a look.  A good use of this trigger  is on small, semi-static tables.  Next time, I will be talking about how to use AFTER triggers to AUDIT\/LOG changes.<\/p>\n","protected":false},"excerpt":{"rendered":"
A typical use of Data Modification Language (DML) triggers is to prevent unwanted data modification. AFTER triggers are great at detecting the INSERT, UPDATE or DELETE action and rolling back the changes. A good security model can prevent these types of changes and should be the first line of defense. However, there are cases in which keys to the castle have to be given out for political reasons inside the organization. In that case, this type of preventive programming can be handy. Today, I am going to revisit the AUTOS…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[396,398,317,391,400,395,80,399,12,15,28,390,29],"class_list":["post-1923","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-after","tag-alter-trigger","tag-create-trigger","tag-ddl","tag-disable-trigger","tag-dml","tag-drop-trigger","tag-enable-trigger","tag-free-code","tag-john-f-miner-iii","tag-sql-server","tag-triggers","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1923","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1923"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1923\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1923"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1923"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1923"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}