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 database that I created for prior articles.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
<span style="color: #008000; font-size:small;">-- Start with master database USE MASTER; GO -- Delete existing databases. IF EXISTS (SELECT name FROM sys.databases WHERE name = N'AUTOS') DROP DATABASE [AUTOS] GO -- Create a autos database CREATE DATABASE AUTOS; GO -- Use the database USE [AUTOS] GO -- Create a USA schema CREATE SCHEMA USA AUTHORIZATION dbo; GO -- Create a BRANDS table CREATE TABLE USA.BRANDS ( MyId INT PRIMARY KEY CLUSTERED, MyValue VARCHAR(20) ) GO -- Load the table with data INSERT INTO USA.BRANDS (MyId, MyValue) VALUES (1, 'Continental'), (2, 'Edsel'), (3, 'Lincoln'), (4, 'Mercury'), (5, 'Ram') GO </span> |
Triggers are part of the Data Definition Language (DDL). Use the CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER 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 and ENABLE TRIGGER statements.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
<span style="color: #008000; font-size:small;">-- Delete the existing trigger. IF EXISTS (select * from sysobjects where id = object_id('TRG_PREVENT_CHANGES') and type = 'TR') DROP TRIGGER [USA].[TRG_PREVENT_CHANGES] GO -- Create the new trigger. CREATE TRIGGER [USA].[TRG_PREVENT_CHANGES] on [USA].[BRANDS] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION AS BEGIN -- declare local variable DECLARE @MYMSG VARCHAR(250); -- nothing to do? IF (@@rowcount = 0) RETURN; -- do not count rows SET NOCOUNT ON; -- deleted data IF NOT EXISTS (SELECT * FROM inserted) BEGIN SET @MYMSG = 'The read only [USA].[BRANDS] table does not allow records to be deleted!' ROLLBACK TRANSACTION; RAISERROR (@MyMsg, 15, 1); RETURN; END ELSE BEGIN -- inserted data IF NOT EXISTS (SELECT * FROM deleted) SET @MYMSG = 'The read only [USA].[BRANDS] table does not allow new records to be inserted!' -- updated data ELSE SET @MYMSG = 'The read only [USA].[BRANDS] table does not allow records to be updated!' ROLLBACK TRANSACTION; RAISERROR (@MyMsg, 15, 1); RETURN; END END GO </span> |
Deleting the ‘Lincoln’ entry from the table is the first test of firing the trigger. As we can see, this action is rolled back.
|
1 2 3 4 |
<span style="color: #008000; font-size:small;">-- Try to delete a record DELETE FROM USA.BRANDS WHERE MyValue = 'Lincoln'; GO </span> |
Updating the ‘Continental’ entry to ‘Ford’ is the second test of firing the trigger. We get the same roll back.
|
1 2 3 4 |
<span style="color: #008000; font-size:small;">-- Try to update a record UPDATE USA.BRANDS SET MyValue = 'Ford' WHERE MyId = 1; GO </span> |
Inserting a ‘Ford’ entry into the table is the third test of firing the trigger. Thus, the trigger reacts as expected.
|
1 2 3 4 |
<span style="color: #008000; font-size:small;">-- Try to insert a new record INSERT INTO USA.BRANDS (MyId, MyValue) VALUES (6, 'Ford'); GO </span> |
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.
