Trigger help for new user . . .

[DWag]

The table I'm working with is a customer table which the three fields I need to work with are cust# custseq# slsman#.

Exising Data looks like:

cust# custseq# slsman#
1 0 5
1 1 5
1 2 5
2 0 6
2 1 6
etc..

What I need the trigger to do is copy the slsman from the record whose custseq#0 to any new customer record whose custseq# is > 0 and the customer#'s are equal. There are execptions for customers that this rule doesn't apply to.

I have code writtne in Progress DB but for some reason I'm hitting a brick wall on getting the SQL syntax correct.

Any pointers would be appreciated!!

Thanks
Dedra
Progress code:

TRIGGER PROCEDURE FOR WRITE OF SYMIX.customer.
if new symix.customer then do:
IF customer.cust-num GE " 19999" AND customer.cust-num LE " 29999" THEN LEAVE.
IF customer.cust-num GE " 40000" AND customer.cust-num LE " 49999" THEN LEAVE.
IF customer.cust-num = " 238" THEN LEAVE.
IF customer.cust-num = " 362" THEN LEAVE.
IF customer.cust-num = " 374" THEN LEAVE.
IF customer.cust-num = " 617" THEN LEAVE.
IF customer.cust-num = " 522" THEN LEAVE.
IF customer.cust-num = " 1390" THEN LEAVE.
IF customer.cust-num = " 1475" THEN LEAVE.
IF customer.cust-num = " 1813" THEN LEAVE.
IF customer.cust-num = " 1835" THEN LEAVE.
IF customer.cust-num = " 2070" THEN LEAVE.
IF customer.cust-num = " 2299" THEN LEAVE.
IF customer.cust-num = " 2379" THEN LEAVE.
IF customer.cust-num = " 2804" THEN LEAVE.
IF customer.cust-num = " 3380" THEN LEAVE.
IF customer.cust-num = " 3785" THEN LEAVE.
IF customer.cust-num = " 100815" THEN LEAVE.
IF customer.cust-num = " 500000" THEN LEAVE.

DEF BUFFER x-cust FOR symix.customer.
FIND x-cust WHERE x-cust.cust-num = customer.cust-num AND
x-cust.cust-seq = 0 NO-ERROR.

customer.slsman = x-cust.slsman.

END.

 

[NathanGriffiths]

I don't know what the GE and LE in the Progress code are but I'm assuming they are upper and lower limits of some kind.

Also I'm assuming cust# in SQL and cust-num in Progress are the same column.

How about something like this:

CREATE TRIGGER ti_Customer ON Customer
FOR AFTER INSERT
AS

UPDATE 
   cm
SET
   cm.[slsman#] = c.[slsman#]
FROM 
   Customer cm
   JOIN ( SELECT DISTINCT [cust#], [slsman#] FROM Customer WHERE [custseq#] = 0) c          
     ON cm.[cust#] = c.[cust#]
WHERE
   ( cm.[cust#] Not Between 19999 And 29999 ) And
   ( cm.[cust#] Not Between 40000 And 49999 ) And
   ( cm.[cust#] Not In (238, 362, 374, 617, 522 ...etc. ) And
   ( cm.[custseq#] > 0 ) And
   ( cm.[slsman#] <> c.[slsman#] )

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[DWag]

Thanks, worked like a charm I think this will get me on the right track. I kept trying to use progress syntax . . . hard to switch. One question though, can you clarify the c and cm? Are these table aliases?

 

[NathanGriffiths]

Yep, the "c" and "cm" are table aliases. These are a way of making complex statements easier to read by assigning a table name e.g. "Customer" an easy to type alias e.g. "c" (or "cust" or "ctm" or anything you like).

Without an alias, you must explicitly include the table name in every reference e.g

Customer.[cust#]

but with an alias you can write

c.[cust#]

The alias is assigned in the FROM clause e.g.

FROM Customer AS c .... (the AS is optional)

Hope that helps!



Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------