Problem with calculation Trigger

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

General SQL Server Forums

New to SQL Server Programming

Problem with calculation Trigger

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-07-21 : 15:21:55

Have a table: OrderRebateHistory

When a record is inserted I need the fields rebate_amt and ext_rebate to be calculated.

I've tried many variations of the code below but I keep getting NULL values in those fields.

I also have a rebate table which holds the price and rebat_pct which is in the code below.

BUT the inserted record should also have the fields price and rebate_pct


ALTER TRIGGER [UPDATEEXTRA7WITHCUSTTYPE2] ON [dbo].[OrderRebateHistory] 
for INSERT
AS

begin
update OrderRebateHistory
set rebate_amt = (inserted.price*(.01*inserted.rebate_pct)),
ext_rebate = (inserted.price*(.01*inserted.rebate_pct)*inserted.qty_to_ship)
from inserted INNER JOIN cicmpy ON inserted.cus_no = cicmpy.debcode INNER join rebate on 
cicmpy.AccountTypeCode = Rebate.cd_tp_3_cus_type AND inserted.item_no = Rebate.item_no
where inserted.ord_type = OrderRebateHistory.ord_type and inserted.ord_no = OrderRebateHistory.ord_no and 
inserted.line_no = OrderRebateHistory.line_no
end

begin
update OrderRebateHistory
set rebate_amt = (inserted.price*(.01*inserted.rebate_pct)),
ext_rebate = (inserted.price*(.01*inserted.rebate_pct)*inserted.qty_to_ship)
from inserted INNER JOIN rebate on 
inserted.cus_no = Rebate.cd_tp_1_cus_no AND inserted.item_no = Rebate.item_no
where inserted.ord_type = OrderRebateHistory.ord_type and inserted.ord_no = OrderRebateHistory.ord_no and 
inserted.line_no = OrderRebateHistory.line_no

end

mfemenel
Professor Frink

1421 Posts

Posted - 2008-07-21 : 16:20:38

Well if you're getting a null back I'd say something in your join or where clause isn't matching up. I would take your update statement and turn it into a select and pass in the values you're using. Createa temp table called #inserted, populate it with what should be there and make sure the join isn't the cause of your fall through to null land.

Mike
"oh, that monkey is going to pay"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-21 : 16:29:20

Use COALESCE/ISNULL on each of the columns that can contain NULLs, specifically inserted.price, inserted.rebate_pct, and inserted.qty_to_ship.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources