I created a user defined field (U_LoyaltyPoints) in cust table. Now i want to updated this field with a Trigger every time when there is a sales. So my Trigger is as follows:
Table Name: Invoice
Action Time: After
Action Type: Insert
DECLARE cn varchar;
declare in varchar;
set cn = Cast(newrow.custnum as varchar);
set in = Cast(newrow.number as varchar);
EXECUTE IMMEDIATE 'Update cust set cust.U_LoyaltyPoints = cast((Select Sum(invitem.Price * invitem.Quantity) - Sum(invitem.Cost * invitem.Quantity) From invitem Where invitem.Number = '+ in +') as varchar(25)) where cust.custNum = ' + cn;
I tried many ways, but all of them ends up with either EMPTY value or doesn't change at all.
Separately both queries work, but together it doesn't.
Please tell me where have i gone wrong.
Thanx in advance