asked on

SQL Trigger

Hello, could someone help me, please?
I am wanting to create a SQL Trigger for the following.

When a new sales order is created in the TRANSHEADERS Table where AccountCode = ‘Customer’
I want the following script to trigger.

Update Customers
Set Customers.ZCustref = Transheaders.ExtraText
FROM CUSTOMERS INNER JOIN
TRANSHEADERS ON CUSTOMERS.UniqueID = TRANSHEADERS.AccountID
Where Customers.CustomerCode = 'Customer'

I have tested the script and it works as I want.

Thank you.

Ryan Chong

in general, try this as you claimed that your Update statement is working fine:

CREATE TRIGGER TRANSHEADERS_Insert  
ON Transheaders  
FOR INSERT AS  
BEGIN  
    Update Customers
	Set Customers.ZCustref = Transheaders.ExtraText
	FROM CUSTOMERS INNER JOIN
						  TRANSHEADERS ON CUSTOMERS.UniqueID = TRANSHEADERS.AccountID
	Where Customers.CustomerCode = 'Customer' 

END

Open in new window

jspc

ASKER

Yes great, that does work, thank you!

You would know why the data is half concatenating would you?
The data on the sales order is this:
Jeremy
44 Smith Street
Drouin

After the Trigger runs it appears in the custom field as this: Jeremy44 Smith StreetDrouin

I have had a look at the data field and it appears to be putting a double space between the y and 4 and the t and D

jspc

ASKER

Oh, can that trigger run on INSERT and UPDATE?
If a Sales Order is amended the Trigger can fire?

Ryan Chong

Oh, can that trigger run on INSERT and UPDATE?
If a Sales Order is amended the Trigger can fire?

sure you can.

ALTER TRIGGER TRANSHEADERS_Insert
ON Transheaders
FOR INSERT , UPDATE AS
BEGIN
Update Customers
      Set Customers.ZCustref = Transheaders.ExtraText
      FROM CUSTOMERS INNER JOIN
                                      TRANSHEADERS ON CUSTOMERS.UniqueID = TRANSHEADERS.AccountID
      Where Customers.CustomerCode = 'Customer'

END

jspc

ASKER

Thanks for your help.

Sorry, I have just realized it needs to equal ‘TransactionType SO (For sales orders only, not invoice transactions).
Is there a way I can incorporate that too?

I have also noticed that if you create another sales order (so now I have 2) that the field is not updated with the most recent data.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jspc

ASKER

Awesome!