User Defined Fields and Triggers

Question asked by Hassan Shifaz - 3/6/2016 at 11:23 AM

Answered

Hi,

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

Definition:

BEGIN

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;


END

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

3/6/2016 at 6:35 PM

Employee Post Marked As Answer

Hassan,

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

3/7/2016 at 6:55 AM

Thanx Mark, changing to AR table did the job.

Hassan Shifaz Replied

3/7/2016 at 6:57 AM

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

3/7/2016 at 3:24 PM

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.

Back to Community Threads

Please leave this box unchecked

4 Replies

Reply to Thread

Related Knowledge Base Articles