Database Reference
In-Depth Information
The first statement in the trigger—
if @@rowcount
= 0
—checks if our insert statement did, in fact, insert any rows.
As an example, you can think about the insert/select pattern when the select query did not return any data. You would
like to avoid having a trigger code from being executed in such cases.
The second statement—
set nocount on
—stops SQL Server from returning the message that displays the
number of rows affected by the code in the trigger. Some client libraries do not handle multiple messages correctly.
The last statement—
if not exists(select * from inserted)
—is trickier. While
@@rowcount
can help you
detect when there are no affected rows by
INSERT
,
UPDATE
, or
DELETE
statements, it would not work very well with
MERGE
. That operator, introduced in SQL Server 2008, allows you to combine all three INSERT, DELETE, and UPDATE
actions into the single statement. Triggers would fire even if there were no corresponding actions.
@@rowcount
in the
trigger represents the total number of the rows affected by
MERGE
statement. Let's create a simple table with three
triggers that display the value of
@@rowcount
and the number of the rows in
inserted
and
deleted
tables. You can see
this code in Listing 8-5.
Listing 8-5.
Triggers and MERGE statement: Table and three triggers creation
create table dbo.Data(Col int not null)
go
create trigger trg_Data_AI on dbo.Data
after insert
as
select
'After Insert' as [Trigger]
,@@RowCount as [RowCount]
,(select count(*) from inserted) as [Inserted Cnt]
,(select count(*) from deleted) as [Deleted Cnt]
go
create trigger trg_Data_AU on dbo.Data
after update
as
select
'After Update' as [Trigger]
,@@RowCount as [RowCount]
,(select count(*) from inserted) as [Inserted Cnt]
,(select count(*) from deleted) as [Deleted Cnt]
go
create trigger trg_Data_AD on dbo.Data
after delete
as
select
'After Delete' as [Trigger]
,@@RowCount as [RowCount]
,(select count(*) from inserted) as [Inserted Cnt]
,(select count(*) from deleted) as [Deleted Cnt]
go
