User Defined Fields and Triggers
Question asked by Hassan Shifaz - 3/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

4 Replies

Reply to Thread
Mark Culos Replied
Employee Post Marked As Answer
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).
Hassan Shifaz Replied
Thanx Mark, changing to AR table did the job.
Hassan Shifaz Replied
Although I was wondering is there a possibility to have a variable based user defined view. In that case i can simply write Views and then call them from Trigger. The SQL will look more clean and neat.
Mark Culos Replied
Employee Post
While the view must be straight SQL, this does not stop you from your approach as the view can be your sub-query and include the customer number and invoice number so that your trigger selects the net value based on these two values.

Reply to Thread