Various Triggers – Part 3

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.

 

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.

 

Deleting the ‘Lincoln’ entry from the table is the first test of firing the trigger. As we can see, this action is rolled back.

 

Updating the ‘Continental’ entry to ‘Ford’ is the second test of firing the trigger. We get the same roll back.

 

Inserting a ‘Ford’ entry into the table is the third test of firing the trigger. Thus, the trigger reacts as expected.

 

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.

Related posts

Leave a Comment