Create and Alter DML Triggers

(This is part of the Study Guide series, 70-457)

Microsoft’s Measured Skill description: This objective may include but is not limited to: inserted and deleted tables; nested triggers; types of triggers; update functions; handle multiple rows in a session; performance implications of triggers

What I see:

·         inserted and deleted tables

·         types of triggers

·         performance implications of triggers

inserted and deleted Dynamic Tables

                The inserted and deleted dynamic tables contain the respective data during the duration of the DML trigger.  This data can be used to log data changes, check referential integrity, or find differences in modified data.  Below shows an example using these tables:

use MeasureMySkills;

go

create table TestTriggerDataTable

(

       id int identity(1, 1) not null,

       SomeInt int not null,

       SomeString nvarchar(128) not null

);

go

create table OldData

(

       id int not null,

       SomeInt int not null,

       SomeString nvarchar(128) not null

);

go

create table NewData

(

       id int not null,

       SomeInt int not null,

       SomeString nvarchar(128) not null

);

go

create trigger UpdateTestTriggerDataTable

on TestTriggerDataTable

after update

as

       insert into OldData

       select *

       from deleted;

       insert into NewData

       select *

       from inserted;

go

insert into TestTriggerDataTable

values

       (5, 'hello'),

       (10, 'good bye');

update TestTriggerDataTable

set

       SomeInt = SomeInt + 10,

       SomeString = 'My Text: ' + SomeString;

select *

from OldData;

select *

from NewData;

Types of Triggers

                There are two types of DML triggers:  AFTER and INSTEAD OF.  The difference is that an AFTER trigger (equivalent with the FOR keyword) gets fired after the triggering command has been executed.  An INSTEAD OF trigger overrides the action of the command.  The above example shows the use of an AFTER trigger, and below shows how an INSTEAD OF trigger works:

create table InsteadOfTestData

(

       AnotherInt int not null,

       AnotherString nvarchar(128) not null

);

go

create trigger InsteadOfTestDataTrigger

on InsteadOfTestData

instead of insert

as

       insert into InsteadOfTestData

       (

              AnotherInt,

              AnotherString

       )

       select

              AnotherInt + 100,

              'Instead of: ' + AnotherString

       from inserted;

go

insert into InsteadOfTestData

values(15, 'hi');

select *

from InsteadOfTestData;

Performance Implications of Triggers

                There are many considerations to take into account prior to just creating a large amount of triggers on your tables.  First and foremost is the performance impact:  Triggers are notorious for being the “silent killers” of performance, as they execute for all statements they are defined for.  Be wary on what tables you create them on, and if they are truly necessary.

References

·         BOL reference on the inserted and deleted Tables

·         BOL reference on CREATE TRIGGER

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.