MS SQL Triggers

A database trigger is a set of instructions that run on a defined event on a table or view. The other day I went to add a trigger that would send out an email. I wanted an email anytime a new value of an unknown type was inserted.

MS SQL allow for two different type of triggers – Data Manipulation Language (DML) and Data Definition Language (DDL). DML triggers take action before, after, instead of your typical CRUD (create, read, update and delete) operations minus the R (read). DDL triggers take action on things like create table, drop table, log in, log out, alter table. Today I will be showing an example of a DML trigger for insert.

The first thing I like to do when a create a table, view, procedure and even triggers is to check to see if one already exists, if so delete it. I do not know the best preferred method, but will be more than happy to show you two different methods the last one only works with DML triggers.

 

 

 

— This works for all triggers.

IF EXISTS(

    SELECT *

    FROM sys.triggers

    WHERE name ‘TriggerName’

    )

BEGIN

    DROP TRIGGER TriggerName;

END

GO

 

— This method does not work for DDL Triggers

IF OBJECT_ID(‘TriggerName’‘TR’IS NOT NULL

BEGIN

    DROP TRIGGER TriggerName;

END

GO

I think the object id method is used most often in the MSDN documentation, but I really do not know the pros and cons for each method.

The following is a trigger that is a generic trigger that will send an email.

— Basic Syntax for an insert trigger

CREATE TRIGGER prefix_TriggerName

ON TABLENAME

FOR INSERT

AS

BEGIN

    SET NOCOUNT ON;

 

    DECLARE @valueId INT;

    SELECT @valueId = id FROM myTable WHERE name ‘Value’;

    IF EXISTS(SELECT FROM inserted WHERE referenctId = @valueId)

    BEGIN

        EXEC msdb.dbo.sp_send_dbmail

            @recipients ‘someone@example.com’,

            @profile_name ‘Defalut’,

            @subject ‘Your Subject’,

            @body =‘Your body text’

    END

END

GO

 

FOR INSERT

Can be changed to the following:

AFTER INSERT
INSTEAD OF INSERT

AFTER is the default when FOR is the only keyword supplied. AFTER triggers do not work on views.

The following statement uses a system table “inserted” that contains the value(s) that are being inserted. Alternatively there is a “deleted” table for triggers FOR DELETED.


IF EXISTS(SELECT FROM inserted WHERE referenctId = @valueId)

The “deleted” table stores copies of the affected rows during UPDATE statements as well.

Let me know if you have any questions, or would like to contribute some additional information.

Leave a Reply

Your email address will not be published. Required fields are marked *

9 + 1 =

Post Navigation