November 5, 2010 at 6:09 am
This trigger is taking forever and query times out. after update this trigger causes data entry in table two which has similar trigger which updates table3. tab3 has no trigger on it. the if part in query where there is join on inserted and deleted is taking forever. i know its something mechanical, cant find it though. please help.
series of events happens like this. Application inserts/updates data in tab 1----> after insert/update trigger fire----> inserts/updates data in table 2------> trigger on tab2 fire-----> update insert data in tab3.
Trigger looks like this.
alter trigger tri1 on table1
for insert, update,delete as
begin
if @@rowcount = 0
return
set nocount on
declare @inserted BIT,
@deleted BIT
SET @inserted = 0
SET @deleted = 0
if exists(select 1 from INSERTED)
SELECT @inserted = 1
if exists(select 1 from DELETED)
SELECT @deleted = 1
IF NOT UPDATE(col1)
BEGIN
if @inserted = 1 and @deleted = 1
and not exists
(
select ins.cola
from inserted ins
join deleted del on del.ColA = ins.ColA
where ISNULL(ins.ColB ,'') <> ISNULL(del.ColB,'')
OR ins.ColC <> del.ColC
OR ins.ColD<> del.ColD
OR ISNULL(ins.ColE,'') <> ISNULL(del.ColE ,'')
)
RETURN
END
insert into table2
select 'something'
end
November 5, 2010 at 6:36 am
Why so procedural?
Why not just do the insert, and let the Where clause filter it all for you, instead of all those IF statements?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 5, 2010 at 6:40 am
those if else has some other business logic associated. Cant take them off. I knw this trigger has something wrong at
is not update(colA)
begin
if @inserted = 1 and @deleted = 1
and not exists.......
cant find it though.
Thanks
November 5, 2010 at 7:04 am
can you show us the real trigger instead of pseudocode?
it's running too long because there's an issue, but translating the real trigger to pseudo masks the real problem.
Lowell
November 5, 2010 at 7:54 am
Silly questions:
1) Is this ok to define a trigger on an update/delete/insert operations, on a primary key col?
2) Can we join inserted and deleted on this primary key column? would it give me infinite iterations?
November 5, 2010 at 9:38 am
ok got my solution
just add an update function to check if there was anyupate.
query execution time falls from 9 mins to 15 secs:hehe:
November 8, 2010 at 6:21 am
ekant_alone (11/5/2010)
ok got my solutionjust add an update function to check if there was anyupate.
query execution time falls from 9 mins to 15 secs:hehe:
15 seconds is still a LONG time for a trigger. I'd definitely look at what the trigger is doing and why.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply