Trigger variables

[dabdo555]

I have a trigger that I want to do the following...

create calc trigger on tblPIW_DB1
instead of update
as
begin

declare @x money, @y money

select @x= isnull(i.[Acq Price],0), @y= isnull(i.[Over Head],0),... from inserted i

UPDATE dbo.[tblPIW_DB1] SET

[Acq Price] = @x,
[Over Head] = @y,
.......

FROM inserted i
WHERE dbo.[tblPIW_DB1].[prop_num] = i.[prop_num]


Basically if the field is null i want to fill the column with a '0'. But its not doing that, it just stays null when i update the table. What am i doing wrong? The dots mean it have more fields than that in the query and update.

 

[swampBoogie]

You shouldn't use variables like that. Your trigger will not handle the case when multiple records are updated.

You can do the update directly

...
update  dbo.tblPIW_DB1 
SET [Acq Price] = coalesce(i.[Acq Price],0),
[Over Head] = coalesce(i.[Over Head],0)
from dbo.tblPIW_DB1 inner join inserted i
on dbo.tblPIW_DB1.prop_num = i.prop_num

 

[dabdo555]

The reason i am storing the fields in variables is b/c i am going to include a formula that uses multiple values from fields. That result will be stored in a variable and then set to a field in the update. Is there a way to do this?