Trigger Trouble - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger Trouble

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-07-28 : 06:23:39

I currently have two seperate triggers that run fine and look for variations of the same event. This sounds to me like a good candidate for a single trigger that combines the two operations but I couldn't get it to work.

Trigger 1 is

create trigger trg_RemoveFromWLOnFTA
on clinic for update
as
/* If Not Attended is entered then take the value
of B_DATE and put it into M_DATE for the same record */
if update(counseled)
begin
if (select counseled from inserted) like 'Not Attended'
begin
update clinic
set M_DATE = (select B_DATE from inserted)
end
end
GO

Trigger 2 is

create trigger trg_RemoveFromWLOnAttend
on clinic for update
as
/* If Attended is entered then take the value
of B_DATE and put it into M_DATE for the associated record */
DECLARE @ClinicDate DATETIME,
@WLNumber VARCHAR(10)
set @ClinicDate = (select B_DATE from inserted)
set @WLNumber = (select PREV_CLINICNO from inserted)
if update(counseled)
begin
if (select count(*) from inserted where inserted.counseled like 'Attended')>0
begin
update clinic
set M_DATE = @ClinicDate
where CLINICNO = @WLNumber
end
end

I tried to combine them with a case statement but it didn't seem to like the update statements. Can this be done. Am I fixing somthing that isn't broken? Secondly I am aware that the second trigger may not work correctly if I do a bulk update. What would I need to do to fix that for this unlikely event

thanks in advance

steve

Steve no function beer well without

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-28 : 07:00:08

set @WLNumber = (select PREV_CLINICNO from inserted)
if update(counseled)
begin

if (select count(*) from inserted where inserted.counseled like 'Attended')>0
begin
update clinic
set M_DATE = @ClinicDate
where CLINICNO = @WLNumber
end

ELSE

begin
update clinic
set M_DATE = (select B_DATE from inserted)
end

end

- Vit

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-28 : 07:53:12

>> if (select count(*) from inserted where inserted.counseled like 'Attended')>0

should be (doesn't matter too much but is more efficient)

if exists (select * from inserted where inserted.counseled like 'Attended')
Also doesn't work for more than a single row update.
What is the PK? WLNumber?

update Clinic
set M_Date = i.B_Date
from Clinic c
join inserted i
on i.WLNumber = c.WLNumber
where i.Counseled = 'Not Attended'

update Clinic
set M_Date = i.B_Date
from Clinic c
join inserted i
on i.WLNumber = c.PREV_CLINICNO
where i.Counseled = 'Attended'

you could do it in a single query
update Clinic
set M_Date = i.B_Date
from Clinic c
join inserted i
on (i.WLNumber = c.WLNumber
and i.Counseled = 'Not Attended')
or (i.WLNumber = c.PREV_CLINICNO
and i.Counseled = 'Attended')

but doesn't seem worth it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-07-29 : 03:32:55

Many thanks to both of you

steve

Steve no function beer well without

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2003-07-29 : 09:20:44

I knew my triggers were a bit clunky but I didn't realise quite how bad they were until I saw this! I now have a working system. Thanks again for the tips guys, I'm off to sit in my hammock and drink ice cold beer

mmmmmmm BEEEEER!

steve

Steve no function beer well without

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-29 : 16:26:33

Cheers, Steve!! :)

Take care.

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources