User Defined Fields and Triggers
Question asked by Hassan Shifaz - March 6, 2016 at 11:23 AM
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

1 Reply

Reply to Thread
Mark Culos Replied
March 6, 2016 at 6:35 PM
Employee Post
While assistance with SQL and custom fields/triggers is outside the scope of support, I can let you know that you cannot get what you are trying to do with the trigger being on the invoice table as it is written before the invoice items.  Try using the ar table when the record inserted is for an invoice (i.e., TransType = 1).

Reply to Thread