Home > Automation, Database Administration, SQL Server 2008R2, SQL Server 2012 > Triggers – Save yourself the extra coding and time with automation

Triggers – Save yourself the extra coding and time with automation

Triggers are an often overlooked feature of SQL Server that can automate a LOT of tasks, such as auto-update an activity flag, or auto-insert new rows based on a new row in a different table. Instead of being required to code 100 lines of code to add a new row to a table and update all dependent tables, a trigger can be set to execute on an Update, Insert, or Delete (DML Trigger), Create, Alter, Drop, Grant, Deny, Revoke (DDL Trigger), or even upon login (Login Trigger).  Today, we’ll cover the three different types and how they can be used to automate your database tasks.

DML Triggers (Update, Insert, Delete)

DML Triggers (Data Manipulation Language, by the way) are created on a by-table scale to automate tasks that must be completed when data in a table is changed.  They are highly customizable, and can be set to be run during the statement that triggers them, after, or instead of.  Some of the most common uses of DML triggers are to update Active fields, date/time fields, and to update dimension tables based on new information in a fact table or vice-versa.  Triggers also make use of the “inserted” and “deleted” temp tables, which stores information in TempDB about the operation recently undertaken.

The code:

CREATE TRIGGER UpdateActive ON Table1
AFTER INSERT, UPDATE
AS (UPDATE Customer SET Active = 1
WHERE CustomerId IN (SELECT CustomerId FROM inserted)
)
GO


What did that just do?  It created the trigger UpdateActive on the table Table1, which will update the Active flag on updated or inserted rows automatically.  That’s a pretty simple example, and will actually save you from a “Doh!  I forgot to update the Active flags when I ran this last batch!” facepalm moment.

Triggers can be customized to do almost anything.  For a closer look at DML Triggers, check out the MSDN Article.

DDL Triggers

DDL Triggers (Data Definition Language, for you non-SQL types out there) are used to execute tasks based on the creation, deletion, or alteration of certain DDL tasks, such as the creation of tables.  Common uses of DDL Triggers are for notifications.  For example, in your business maybe you need to update other people when you alter the schema of your database.  You can set up a DDL Trigger to automatically send email using the dbmail stored proc (we’ll cover that on our next lesson) with a custom message.

The code:

CREATE TRIGGER NewTableNotify ON ALL SERVER
FOR CREATE_DATABASE
AS (
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Database Administration’,
@recipients = ‘user@Adventure-Works.com’,
@body = ‘A new table has been added to the AdventureWorks database.  For specific details, please contact myemail@Adventure-Works.com.  Thank you!’,
@subject = ‘Schema Updated – New Table’;

The above code will send an email to said recipients.  One can play around with variables and the likes and even send specific details in the message.

Triggers can also be used to prevent unwanted database changes.  Sending reminders to yourself is a good practice, because lets face it, sometimes even the best of us forget to do something.

The code:

CREATE TRIGGER safety 
ON DATABASE
FOR DROP_TABLE
AS RAISERROR (‘You must disable Trigger “safety” to drop a table!’,10, 1)
ROLLBACK
GO

The above code could save you a LOT of trouble from deleting something you shouldn’t.  Play around with it, see what kind of things you can make triggers do.

For more details, again, visit the MSDN Triggers article.

Logon Triggers

Logon Triggers are executed, you guessed it, upon login to the database.  Security is one of the main goals here.  It can be used to prevent multiple users using the same login.

The code:

CREATE TRIGGER connection_limit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= ‘thomas_anderson’ AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = ‘thomas_anderson’) > 1
    ROLLBACK
END

If thomas_anderson is connected on more than one computer, an error is thrown and the transaction is rolled back.   Great for security, right?

Once more, and finally for more details about Logon, DDL and DML Triggers, see the MSDN article.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: