February 5, 2002 at 8:33 am
This trigger works on some updates and does nothing on others? Please advise. thanks
CREATE TRIGGER [CountSAE] ON [dbo].[Demographics]
FOR INSERT, UPDATE
AS
declare @d as int
declare @C as int
declare @a as int
declare @b-2 as int
declare @e as int
declare @htotal as int
declare @stotal as int
declare @ctotal as int
declare @patkey as varchar(12)
declare @questdate as smalldatetime
select @patkey = patkey from INSERTED
select @questdate = questdat from INSERTED
select @a= case UPPER(remsae1) when 'H' then 1 else 0
end
,@b = case UPPER(remsae2) when 'H' then 1 else 0
end
,@c = case UPPER(remsae3) when 'H' then 1 else 0
end
,@d = case UPPER(remsae4) when 'H' then 1 else 0
end
,@e = case UPPER(remsae5) when 'H' then 1 else 0
end
from INSERTED
select @htotal = @a + @b-2 + @C + @d + @e
select @a= case UPPER(remsae1) when 'C' then 1 else 0
end
,@b = case UPPER(remsae2) when 'C' then 1 else 0
end
,@c = case UPPER(remsae3) when 'C' then 1 else 0
end
,@d = case UPPER(remsae4) when 'C' then 1 else 0
end
,@e = case UPPER(remsae5) when 'C' then 1 else 0
end
from INSERTED
select @Ctotal = @a + @b-2 + @C + @d + @e
select @a= case UPPER(remsae1) when 'S' then 1 else 0
end
,@b = case UPPER(remsae2) when 'S' then 1 else 0
end
,@c = case UPPER(remsae3) when 'S' then 1 else 0
end
,@d = case UPPER(remsae4) when 'S' then 1 else 0
end
,@e = case UPPER(remsae5) when 'S' then 1 else 0
end
from INSERTED
select @Stotal = @a + @b-2 + @C + @d + @e
update demographics
set havehosp =@htotal, havese = @stotal, havecancer =@Ctotal
where patkey= @patkey and questdat=@questdate
February 5, 2002 at 11:28 am
Are you sure that the updates should change the values in the table? Also, this will only work for single row updates. You might have strange issues if more than one row is updated.
I'd rework it to update the table in a single statement based on values in the inserted table.
You can do this as
update d
set ...
from demographics d
inner join inserted i
on d.patkey = i.patkey
and d.questdat = i.questdat
I leave it to you to handle the set statement.
Steve Jones
February 5, 2002 at 12:32 pm
Thank you Steve.
I am not getting the desired results still. Let me go over what I want so you have better idea.
remsae1,remsae2,remsae3,remsae4,remsae5 can have one of the following values
C,H and S
this trigger is supposed to update 3 variables after the INSERT or UPDATE
those 3 variables are havese, havehosp and havecancer
so havecancer would tell me how many C's are there for this person looking at remsae1,2,3,4,5 --- havehosp for number of H and havese for S
what happens if someone just update/addNewValue for remsae5 with 'H' ?
assume this was the data before update for remsae1,2,3,4,5
C S S S NULL
after the update
C S S S H
with the tigger i have in place,your modification added, it just does not give me correct count and i know i am doing something wrong,, just dont know what?
February 5, 2002 at 12:33 pm
As previously stated rework it to try to out from the inserted table to the table you want the data. Whether or not it is related a select into or insert with a select statement or any transaction that affects multiple records may get missed as you have nothing to care for this.
February 5, 2002 at 3:25 pm
Are you experiencing problems when doing multiple updates? If so, select everything from the inserted table into a cursor, then loop through the cursor to do the updates.
February 7, 2002 at 9:40 am
Using a cursor is slowing down the database to a crawl?
February 7, 2002 at 9:52 am
It very well could, yes, especially since you're going to see quite a bit of lock escalation if you are also making updates row by row. However, if the process has to run row by row, you've eliminated any possible set operations. Steve was trying to steer back to a set based operation.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply