Trigger - variable versus literal

[Glasgow]

I have a trigger that is behaving strangely. The trigger is actioned when an order record is inserted and updates a customer record with the date and amount of the most recent order.

I am loading some test orders to the database via an INSERT:

SET @OrdTxt   = LTRIM(dbo.FnuExtract(@OrdLst,',',1))
SET @OrdAmt   = CAST(@OrdTxt as smallmoney)
--SET @OrdAmt   = CAST(LTRIM(' 1957.00') as smallmoney)
SET @RetAmt   = ROUND(@OrdAmt*1.175,2)

INSERT Orders(CustId, OrdDate, NetAmt, RetailAmt)
VALUES (23,'01-May-2008',@OrdAmt, @RetAmt)

Although I have verified that @OrdTxt contains valid numeric non-zero data and that both @OrdAmt and @RetAmt contain the correct values, the trigger only updates the date of the last order but puts zero in the amount of the last order. If I reinstate the line commented out, it does exactly what I believe it should do and updates the last order amount to 1957.00.

The FnuExtract function extracts a word from a string based on a supplied delimiter and returns varchar. There is an implication that it may be the culprit. I can supply

I can supply the code of the trigger itself but I cannot see how it could be at fault. I am utterly stumped!

 

[Glasgow]

This may just be down to a badly written trigger whose logic does not correctly cater for being called once on completion of a batch of inserts.

I will report back.

 

[SQLSister]

Rememeber all triggers are only called once whether one record is inserted or 2 million. If your trigger is only written to handle one record, you can have serious data integrity issues from that. If you need help figuring out how to change the trigger to correctly handle inserts, then post the code here. (Whatever you do, don't fix by putting in a cursor!)

"NOTHING is more important in a database than integrity." ESquared

 

[Qik3Coder]

Are you PRINT(ing) values when testing so you can verify what you are getting at explicit points? It should be easy enough to verify that your function is parting out correctly.

-Sometimes the answer to your question is the hack that works

 

[Glasgow]

Thanks - yes that was precisely the avenue I was exploring and, I believe is the root of the problem. I think I've just about got a new version working.