473,569 Members | 2,700 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trigger performance

I have a multi-part question regarding trigger performance. First of
all, is there performance gain from issuing the following within a
trigger:

SELECT PrimaryKeyColum n FROM INSERTED

opposed to:

SELECT * FROM INSERTED

Secondly, what is the optimum way to determine which action fired an
AFTER trigger that is defined for INSERT, UPDATE, DELETE? Is it
something similiar to:

IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
ELSE
--sql block for update

or is there a superior method?

Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
best way to differentiate between multiple rows being affected or just
a single row? Can using this @@ROWCOUNT test fail? Are there any
situations where it would return erroneous results?

I realize I'm being somewhat nitpicky on these matters but any feedback
would be greatly appreciated!

Jul 23 '05 #1
6 6537
1.
SELECT PrimaryKeyColum n FROM INSERTED

SELECT * FROM INSERTED
Since those two statements presumably return different results (unless you
only have one colum) I'm not sure what you are trying to compare here. It's
good practice to avoid SELECT * except in an EXISTS subquery. Always list
the columns you require because it makes your code clearer and more
maintainable and it maximizes the chances for the optimizer to use indexes.
In an EXISTS subquery the opposite argument applies: *any* index may be used
to optimize an EXISTS subquery so listing the column names is unnecessary
and makes no difference to the logical meaning of the query.

2. Secondly, what is the optimum way to determine which action fired an
AFTER trigger that is defined for INSERT, UPDATE, DELETE?
Try to keep trigger logic simple and set-based - you may be able to write a
single DML statement rather than use multiple IFs in one trigger. If the
logic is quite different in each case then create separate triggers for each
action.

3. Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
best way to differentiate between multiple rows being affected or just


Why would you care if one or multiple rows were updated? Triggers should use
set-based code so the logic should be identical in any case. Never assume
that only single rows will be updated - this is a classic error and one that
causes a lot of grief judging by posts in this group.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2
oj
1. yes. 'select keycol' returns much less data. thus, it's more efficient.
2. yes. you want to use if...then block for each type.

e.g.
declare @i int, @d int
select @i=count(*) from inserted
select @d=count(*) from deleted

if @i=@d
print 'update'
if @i>@d
print 'insert'
if @i<@d
print 'deleted'

3. yes. @@rowcount in after trigger would tell us if any rows have been
affected. 0 will be returned if no row is affected.
"Scott CM" <sc******@hotma il.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
I have a multi-part question regarding trigger performance. First of
all, is there performance gain from issuing the following within a
trigger:

SELECT PrimaryKeyColum n FROM INSERTED

opposed to:

SELECT * FROM INSERTED

Secondly, what is the optimum way to determine which action fired an
AFTER trigger that is defined for INSERT, UPDATE, DELETE? Is it
something similiar to:

IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
ELSE
--sql block for update

or is there a superior method?

Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
best way to differentiate between multiple rows being affected or just
a single row? Can using this @@ROWCOUNT test fail? Are there any
situations where it would return erroneous results?

I realize I'm being somewhat nitpicky on these matters but any feedback
would be greatly appreciated!

Jul 23 '05 #3
> SELECT PrimaryKeyColum n FROM INSERTED

opposed to:

SELECT * FROM INSERTED
What for? You seldom want to return data from a trigger, so neither is
reall optimum. Can you explain what you are trying to do?
Secondly, what is the optimum way to determine which action fired an
AFTER trigger that is defined for INSERT, UPDATE, DELETE? Is it
something similiar to:
The optimum way to do this is to have three triggers, on for insert, update
and for delete. Then you don't have to spend resources determining which
kind of trigger it is, not to mention that the plan for the trigger will be
better if you don't have conditional execution of blocks:
IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
ELSE
--sql block for update
If you must do this, this is adequate.
Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
best way to differentiate between multiple rows being affected or just
a single row? Can using this @@ROWCOUNT test fail? Are there any
situations where it would return erroneous results?
Yes, it is the best way, but it has to be the FIRST statement (other than
declararation of variables) So you need to do:

AS
declare @rowcount int
set @rowcount = @@rowcount

Then you can check @rowcount. You can just check @@rowcount instead of the
declare, but I generally set the value to a variable, since the number of
rows affected by the trigger can often be useful later in the trigger.
--
----------------------------------------------------------------------------
Louis Davidson - dr***@hotmail.c om
SQL Server MVP

Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)

"Scott CM" <sc******@hotma il.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .I have a multi-part question regarding trigger performance. First of
all, is there performance gain from issuing the following within a
trigger:

SELECT PrimaryKeyColum n FROM INSERTED

opposed to:

SELECT * FROM INSERTED

Secondly, what is the optimum way to determine which action fired an
AFTER trigger that is defined for INSERT, UPDATE, DELETE? Is it
something similiar to:

IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
ELSE
--sql block for update

or is there a superior method?

Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
best way to differentiate between multiple rows being affected or just
a single row? Can using this @@ROWCOUNT test fail? Are there any
situations where it would return erroneous results?

I realize I'm being somewhat nitpicky on these matters but any feedback
would be greatly appreciated!

Jul 23 '05 #4
Scott's posting didn't make it here (I found it on Google), so I'm adding
my replies to David's pos.

David Portas (RE************ *************** *@acm.org) writes:
1.
SELECT PrimaryKeyColum n FROM INSERTED

SELECT * FROM INSERTED


Both of these seems wrong. Trigger should not normally return result sets.
Sometimes you want to do this for debugging, but leaving it around can
cause great confusion, because the client that executed the INSERT/UPDATE/
DElETE statment is not expecting a result set, and not consuming the
result set, can lead to the trigger not completing, the transaction not
being committed, and locks being held blocking other users.

All that said, I can't see any difference in effeciecy in this case,
since "inserted" is a virtual table, and there are no indexes.

And that leads to a question of trigger performance you didn't ask: if
your trigger makes several references to the inserted/deleted tables,
consider doing this first in your trigger:

INSERT @inserted (...)
SELECT ... FROM inserted

and then use the table variables in the following queries. The virtual
tables are slow, because they are read from the transaction log.

It might be tempting to do:

SELECT * FROM #inserted FROM inserted

since you don't have to declare a copy of the table, but since triggers
are always run in a transaction context, this can be costly, since the
creation of the temp table is logged. (Believe me, this is something I've
learnt the hard way.)
2.
Secondly, what is the optimum way to determine which action fired an
AFTER trigger that is defined for INSERT, UPDATE, DELETE?
Try to keep trigger logic simple and set-based - you may be able to
write a single DML statement rather than use multiple IFs in one
trigger. If the logic is quite different in each case then create
separate triggers for each action.


Scott's question is certainly meaningful. I often write a combined
INSERT/UPDATE trigger, because the same validations has to be performed.
For the same reason, I also include the DELETE action for the same trigger.
But some checks or cascading actions may only perform to one of them. In
that case it appears to be good for performance to not run that
check/action.

The way to check is:

IF EXISTS (SELECT * FROM @inserted) AND NOT (SELECT * FROM @deleted)
-- We have an INSERT statement.

3.
Lastly, is checking @@ROWCOUNT at the beginning of an AFTER trigger the
best way to differentiate between multiple rows being affected or just

Probably. Although, I prefer to check SELECT COUNT(*) on inserted/deleted.
This may be due to that in earlier versions of SQL Server, @@rowcount did
not always have the right number.
Why would you care if one or multiple rows were updated? Triggers should
use set-based code so the logic should be identical in any case. Never
assume that only single rows will be updated - this is a classic error
and one that causes a lot of grief judging by posts in this group.


There are indeed cases where you want to differentiate between a
singlerow/multirow operation:

o The primary key is updated, and you need to relate the old and new
value. Unless the table has an alternate key which is left unchanged,
you cannot handle multirow updates in this case. The one way out is
to disallow multi-row updates, by checking @@rowcount.

o The trigger logic for the general case for multi-row updates is much
slower than specific single-row logic, and you expect single-row
updates to be the most common. In this case you check @@rowcount,
and take different paths depending on the value. We have at least one
such trigger in our system.

o A variation of this is that the multi-row handling is complicated, and
there is only a requirement to handle single-row operation. After all,
most development budgets are limited. Of course, in this case too, you
need to check @@rowcount, and raise an error in case of a multi-row
operation.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
On Fri, 10 Dec 2004 15:25:38 -0800, oj wrote:
2. yes. you want to use if...then block for each type.

e.g.
declare @i int, @d int
select @i=count(*) from inserted
select @d=count(*) from deleted

if @i=@d
print 'update'
if @i>@d
print 'insert'
if @i<@d
print 'deleted'


Hi oj,

I'm sorry, but I have to disagree with this part of your message. Using IF
EXISTS or IF NOT EXISTS is quicker thatn actually counting the number of
rows in inserted and deleted, so if you only need to distinguish between
insert, update and delete (as per the original question), you should not
use COUNT(*).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #6
oj
Yes, you probably gain some if this is a large set. Tks.

"Hugo Kornelis" <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message
news:kc******** *************** *********@4ax.c om...
On Fri, 10 Dec 2004 15:25:38 -0800, oj wrote:
2. yes. you want to use if...then block for each type.

e.g.
declare @i int, @d int
select @i=count(*) from inserted
select @d=count(*) from deleted

if @i=@d
print 'update'
if @i>@d
print 'insert'
if @i<@d
print 'deleted'


Hi oj,

I'm sorry, but I have to disagree with this part of your message. Using IF
EXISTS or IF NOT EXISTS is quicker thatn actually counting the number of
rows in inserted and deleted, so if you only need to distinguish between
insert, update and delete (as per the original question), you should not
use COUNT(*).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Jul 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
8590
by: Jason | last post by:
I have a table that matches up Securities and Exchanges. Individual securities can belong on multiple exchanges. One of the columns, named PrimaryExchangeFlag, indicates if a particular exchange is the primary exchange for that symbol. Each symbol can only have one primary exchange. I am trying to write a insert/update/delete trigger that...
1
6146
by: Gent | last post by:
am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the trigger and a not very complex select statement...
3
7273
by: takilroy | last post by:
Hi, Does anyone know of a simple way to do this? I want to create an insert trigger for a table and if the record already exists based on some criteria, I want to update the table with the values that are passed in via the insert trigger without having to use all the 'set' statements for each field (so if we add fields in the future I...
5
3287
by: Bob Stearns | last post by:
I have two (actually many) dates in a table I want to validate on insertion. The following works in the case of only one WHEN clause but fails with two (or more), with the (improper? inappropriate?) error message: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: CREATE TRIGGER IS3.date_later_001i NO C;BEGIN-OF-STATEMENT;<space> which is...
2
3081
by: R.Welz | last post by:
Hello. I want to discuss a problem I have with my database design becourse I feel I cannot decide wheather I am on the right way of doing things. First of all, I am writing a literature and magazine database with web (PHP) and C++ Interface, serving over the web and in a very fast LAN. So my concern is about performance (and aestaetic by...
1
21036
by: filip1150 | last post by:
I'm trying to find if there is any performance diference between explicitly using a sequence in the insert statement to generate values for a column and doing this in an insert trigger. I noticed that th eaccess plan for the 2 situations is quite different. For the case where the trigger is in place, the optimizer applies 2 extra residual...
9
9299
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate...
11
7853
by: tracy | last post by:
Hi, I really need help. I run this script and error message appeal as below: drop trigger log_errors_trig; drop trigger log_errors_trig ERROR at line 1: ORA04080: trigger 'LOG_ERRORS-TRIG' does not exist drop table log_errors_tab;
10
3555
by: JohnO | last post by:
Hi All, This question is related to iSeries V5R4 and db2. I want to implement an AFTER DELETE trigger to save the deleted rows to an archive table, I initially defined it as a FOR EACH STATEMENT trigger that would insert all the deleted rows in one operation like this: CREATE TRIGGER MyTable_TD
0
7698
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7924
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8122
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7673
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5513
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5219
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3653
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2113
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1213
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.