Calculated field - 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 2005 Forums

Transact-SQL (2005)

Calculated field

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-22 : 13:22:42

How do I update a field using a calculation in a trigger?

Have two fields that need updating when an insert is done.

Table is OrderRebateHistory

When an insert is done I need to calculate a Rebate_amt and ext_rebate.

The fields needed to calculate these two fields will be in the inserted table.

Here is a snip it of the calculations I am using in my trigger.
rebate_amt = (inserted.price*(.01*inserted.rebate_pct)),
ext_rebate = (inserted.price*(.01*inserted.rebate_pct)
*inserted.qty_to_ship)

Everything I try seems to put NULL into the rebate_amt and ext_rebate fields.

Any help would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 13:29:12

its best to just create these fields as calaculated members in yourtable rather than calculating them in your trigger. The use of trigger will certainly hurt performance. you just need to include these calculation while defining the tables to make these two columns calaculated members.

CREATE TABLE yourtable

    (

    other fields.....,
price datatype,
rebate_pct datatype,
qty_to_ship datatype,
rebate_amt as (price*(.01*inserted.rebate_pct)),
ext_rebate as (price*(.01*inserted.rebate_pct)
*qty_to_ship)
    )

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-22 : 14:02:52

So by doing that, it won't recalculate each record every time an insert is done? Making all values in the rebate_amt and ext_rebate the same for every record???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 14:23:17

quote:
Originally posted by Vack

So by doing that, it won't recalculate each record every time an insert is done? Making all values in the rebate_amt and ext_rebate the same for every record???

nope. it will store only the definition of column not values. the actual values are calculated only when you try to fetch data from them based on their calculation. Only if you use persisted clause during definition will they be calculated and kept.

http://www.sqlservercentral.com/articles/T-SQL/61764/

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-22 : 15:22:58

Thanks a lot, I just learned something new. that will be very helpful in the future.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources