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
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
Advertisements

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