July 29, 2008 at 5:52 am
Hi All,
I need some advice on how to make this trigger perform better.
CREATE TRIGGER [dbo].[ValueFieldUpdateTrig]
ON [dbo].[TABLE1] AFTER INSERT, UPDATE
ASDECLARE @caseIDNUMERIC(18)
IF UPDATE(VALUE)
BEGIN
SELECT@caseID = CASEID
FROMTABLE1
WHERE (COLUMNS_UPDATED()& 8 = 8)
BEGIN
UPDATE TABLE1 SET VALUE=replace(VALUE, char(10), ' ')
WHERE NAME='test' AND CASEID=@caseID
END
END
ELSE
BEGIN
SELECT@caseID = CASEID
FROMINSERTED
BEGIN
UPDATE TABLE1 SET VALUE=replace(VALUE, char(10), ' ')
WHERE NAME='test' AND CASEID=@caseID
END
END
I am confused by the select on table1 with the where clause using column_updated, rather than on inserted.
The column_updated bit map 8 is the value column.
I did not write this trigger but need to get it performing better.
Table1 contains 1+million rows.
All suggestions welcome.
Ronnie
July 29, 2008 at 6:24 am
SELECT @caseID = CASEID
FROM TABLE1
WHERE (COLUMNS_UPDATED()& 8 = 8)
BEGIN
UPDATE TABLE1 SET VALUE=replace(VALUE, char(10), ' ')
WHERE NAME='test' AND CASEID=@caseID
END
Have you checked that the trigger is doing what it should?
You are right to suspect the where clause above... which row out of the 1+million is this supposed to update?
Performancewise... the select statement will scan the whole table, returning you the CASEID for the last row it reads!
Also, the trigger doesn't cope with the scenario where you update or insert >1 row in a single statement.
July 29, 2008 at 6:38 am
strange trigger 🙂
If it's an update AND 8-15, 24-32 or 40-47 (and so on) records were updated (& is a bitwise comparison) then the last record/caseid will be updated (line breaks will be replaced with space). If NOT 8-15, 24-32 (and so on) records were updated then the all records with caseid=NULL and name='test' will be updated (I don't know if caseid can be NULL).
Maybe the first select is slow if the bitwise comparison is true. I suppose the SQL Server has to find all rows in TABLE1 eventhough only the last row is actually used...?
July 30, 2008 at 6:58 am
All,
Could I replace :
SELECT @caseID = CASEID
FROM TABLE1
WHERE (COLUMNS_UPDATED()& 8 = 8)
BEGIN
UPDATE TABLE1 SET VALUE=replace(VALUE, char(10), ' ')
WHERE NAME='test' AND CASEID=@caseID
END
With
SELECT @caseID = CASEID
FROM inserted
BEGIN
UPDATE TABLE1 SET VALUE=replace(VALUE, char(10), ' ')
WHERE NAME='test' AND CASEID=@caseID
END
Thanks
Ronnie
July 30, 2008 at 7:46 am
A couple of points:-
Your new trigger still doesn't allow for the case where an individual statement inserts/updates more than one row in a single statement.
A trigger fires once for each statement. If your trigger doesn't actually insert/update any rows, then this statement:-
SELECT @caseID = CASEID
FROM inserted
will return a NULL @caseID, so if CASEID can be null, you will potentially update the wrong rows.
Stick IF @@rowcount = 0 RETURN at the top of your trigger.
July 31, 2008 at 8:40 am
My recommendation is that you tell us, in detail and preferably with sample data and expected output, EXACTLY what the objective of this trigger is. Then we can help you craft one to achieve that objective most effectively.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 31, 2008 at 9:29 am
It looks like you are running the same code in IF and the ELSE. If your goal is to remove Line Feeds from the value column then you all your trigger needs to do is this:
[font="Courier New"]CREATE TRIGGER [dbo].[ValueFieldUpdateTrig]
ON [dbo].[TABLE1] AFTER INSERT, UPDATE
AS
UPDATE Table1
SET Value = REPLACE(Value, CHAR(10), '')
WHERE
Name = 'Test' AND
-- this line will handle batches and only update
-- rows where there is a line feed
CASEID IN (SELECT CaseID FROM inserted
WHERE CHARINDEX(CHAR(10), value) > 0)[/font]
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply