October 9, 2009 at 9:27 am
I have a trigger on a table that is slowing performance down when mass inserts are done against the table. The records being inserted don't need to have the trigger fire.
I only want the trigger to fire when a certain value in a field is inserted in the table.
As is, the trigger begins basically with this statement:
IF UPDATE(fieldname) - specifies the fieldname to look for
At this level, is it possible to include "where" information right up front so that the remainder of the trigger doesn't have to occur?
Like
IF UPDATE(fieldname) = 'Test'
-- then do the rest...
Hope this makes sense.
Rog
October 9, 2009 at 10:27 am
When I've had performance issues with large, batch-like tasks, I've usually disabled the trigger temporarily (i.e. right before the INSERT(s) ) and then re-enabled.
Is there concurrent activity against this table while you are doing your mass inserts? Or do you have the luxury of serial access?
October 9, 2009 at 10:34 am
Thanks... but there would be concurrent activity going on and it would be difficult to coordinate disabling the trigger whenever a certain process needs to be run (involved two different departments in the company) that locks up the server because of the trigger.
October 9, 2009 at 10:55 am
With the limited information provided, the best I could suggest for SQL200 would be:
SELECT *
INTO #Inserted
FROM inserted
WHERE fieldname = 'Test'
IF @@ROWCOUNT > 0
BEGIN
-- Use #Inserted instead of inserted
END
If you post the trigger along with DDL etc, someone may be able to help optimize the code.
ps. You could also try using a separate user for the bulk updates and checking for that at the top.
October 11, 2009 at 4:27 pm
IF EXISTS (
select 1 from inserted i
left join deleted d ON d.KeyColumn = i.KeyColumn
where (i.fieldname <> d.fieldname OR d.fieldname IS NULL)
)
BEGIN
......
END
This will run the code only if new values are actually supplied to the column.
If you need to take actions in case of rows been deleted then you need FULL JOIN and check for i.fieldname IS NULL as well.
_____________
Code for TallyGenerator
October 12, 2009 at 7:45 am
Can you post at least pseudo code for the trigger?
Sergiy, is close, but because you said it is an insert trigger you can't use the deleted table. You can either do and IF EXISTS on the inserted table to determine if you want the trigger code to fire or you can add a WHERE clause to the INSERT/UPDATE/DELETE you are doing within the trigger that limits the action to when you have the desired value in the column.
Ken's ps about using a specific user for the batch load is an interesting idea as well, but I'd be concerned someone may incorrectly use that user later on down the road.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 12, 2009 at 8:23 am
You can add a temp table check at the beginning of the trigger like below:
if object_id('tempdb..#skip_trigger) is not null
return
-----------------------------------------------
Then on the occasions when you want to skip the trigger logic and totally control the updating in a stored proc, you create the #skip_trigger table in the proc before the update.
ie.
select 'Y' junk into #skip_trigger
update mytable set ....
October 12, 2009 at 9:59 am
Roger Abram (10/9/2009)
Thanks... but there would be concurrent activity going on and it would be difficult to coordinate disabling the trigger whenever a certain process needs to be run (involved two different departments in the company) that locks up the server because of the trigger.
We have the same issue of concurrent activity.
We use the following code in the batch just prior to the mass insert:
SET @vTrigger = CAST(''TRGAppointmentLMC'' as varbinary(128)) -- trigger to bypass
SET CONTEXT_INFO @vTrigger
SELECT @LogMsg = ''TRGAppointmentLMC trigger bypassed''
After the mass insert code we insert:
SET @vTrigger = CAST('''' as varbinary(128))
SET CONTEXT_INFO @vTrigger
SELECT @LogMsg = ''***Appointment LMC trigger bypass ended.''
WE have the following user defined function:
CREATE FUNCTION [dbo].[BypassTrigger] (@strTrigger varchar(128))
RETURNS int AS
BEGIN
--Determines whether a given trigger is to be bypassed in this session
DECLARE @nBypass int
DECLARE @vTrigger varbinary(128)
SELECT @vTrigger = context_info FROM master.dbo.sysprocesses
WHERE spid = @@spid
if @vTrigger = cast(@strTrigger as varbinary(128))
SELECT @nBypass = 1
else
SELECT @nBypass = 0
RETURN(@nBypass)
END
In the Trigger we have the following code as the first statement:
-- if the trigger is currently bypassed for this process, exit
IF dbo.BypassTrigger(OBJECT_NAME(@@procid)) = 1 RETURN
We don't disable the trigger, the trigger always fires, but the trigger code is not executed when CONTEXT_INFO has the trigger name.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
October 12, 2009 at 5:42 pm
Jack Corbett (10/12/2009)
Can you post at least pseudo code for the trigger?Sergiy, is close, but because you said it is an insert trigger you can't use the deleted table.
Tables "inserted" and "deleted" exist in all triggers.
"deleted" is always empty in INSERT triggers, "inserted" is empty in DELETE ones.
So, you can use it, and LEFT JOIN together with IS NULL check in my script is specifically used for INSERT trigger case.
"<>" check will work for UPDATE trigger.
_____________
Code for TallyGenerator
October 12, 2009 at 7:31 pm
But why confuse things by referencing the deleted table when there is never going to be any data in it? Seems like an unnecessary complication.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply