Declare variables in trigger

[dabdo555]

Below is the trigger code im working on. I need to know a couple of things. One, is it okay to have declared variables in my trigger to store a calculated field's value? Two, is it okay to have multiple sets like i do in the code. any help would be great.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER trigger calcFinancial
on dbo.tblPIW_DB1
for insert, update
as

update dbo.tblPIW_DB1
set Profit =
--these formulas pull data from both tables
case when (i.[fellthroughdate1] is null and i.[fellthroughdate2 is null]) then --formula
case when (i.[fellthroughdate1] is not null and i.[fellthroughdate2 is null]) then --formula
case when (i.[fellthroughdate1] is not null and i.[fellthroughdate2 is not null]) then --formula
end
from dbo.tblPIW_DB1
inner join INSERTED i
on tblPIW_DB1.[prop_num] = i.[prop_num]
inner join tblPIW_DB2
on i.[prop_num] = tblPIW_DB2.[prop_num]

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

[dabdo555]

sorry, there is only one set but use your imagination.

 

[JohnDTampaBay]

I believe the answer to both questions is YES. Variables can be declared and used in triggers and you can SET them as many times as you like.

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[dabdo555]

im sorry, but one more question, can you have if statements in there as well???

 

[nigelrivett]

Yes.
You can put most things in a trigger that you can put in an SP.
Exceptions are things like create table.
See create trigger in bol
and

http://www.nigelrivett.net/Triggers_1_A_beginners_guide.html

Remember that the inserted and deleted tables will have all entries that are affected by the firing statement. Depending on the system you are implementing you maybe shouldn't assume they will have a single row.

======================================
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.