need help with my trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need help with my trigger

Author

Topic

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-10-27 : 15:42:15

Below if the sample of my trigger which I use to copy data from my sales order table to a new one. It works fine however i need to make some changes to add customer phone number from my other table CustomerMaster. Please look below .... How do I query another table to get this field (phone number)?
ALTER TRIGGER [dbo].[ATSQueue1]
ON [companyB].[dbo].[SorMaster]
AFTER UPDATE
AS

IF (UPDATE(OrderStatus))
BEGIN
INSERT INTO Reporting.dbo.ATS_Shipping_Queue
(Pickslip,
Docket,
Account,
Attention,
[Name],
[Address],
Address2,
City,
Province,
Country,
Zip,
Phone,
Email,
Instructions,
Ref,
PO,
[Audit_Timestamp])

SELECT RTRIM(LTRIM(SalesOrder)),'AGPROFFESS', RTRIM(LTRIM(Customer)),'', RTRIM(LTRIM(CustomerName)),
RTRIM(LTRIM(ShipAddress1)),
'',
RTRIM(LTRIM(ShipAddress2)),
RTRIM(LTRIM(ShipAddress3)),
'CA',
RTRIM(LTRIM(ShipPostalCode)),
RTRIM(LTRIM(phone from customer master)),at this point I need to query another table to get the phone number.
RTRIM(LTRIM(Email)),
RTRIM(LTRIM(SpecialInstrs)),
RTRIM(LTRIM(SalesOrder)),
RTRIM(LTRIM(CustomerPoNumber)),
getdate()
FROM inserted
WHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN')
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-27 : 15:44:32

Add a join after FROM inserted.

FROM inserted i
JOIN sometable t
ON i.somecolumn = t.somecolumn

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Patyk
Yak Posting Veteran

74 Posts

Posted - 2014-10-27 : 16:18:26

I have changed the following
RTRIM(LTRIM(companyB.dbo.ArCustomer.Telephone)),
RTRIM(LTRIM(Email)),
RTRIM(LTRIM(ShippingInstrs)),
RTRIM(LTRIM(SalesOrder)),
RTRIM(LTRIM(CustomerPoNumber)),
getdate()
FROM inserted companyB.dbo.SoMaster JOIN companyB.dbo.ArCustomer on companyB.dbo.SoMaster.Customer =companyB.dbo.ArCustomer.Customer WHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN')
END
Getting error Msg 102, Level 15, State 1, Procedure ATSQueue1, Line 42
Incorrect syntax near '.'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-27 : 16:37:32

FROM inserted companyB.dbo.SoMaster sm
JOIN companyB.dbo.ArCustomer ac on ac.Customer = sm.Customer
WHERE OrderStatus = '4' and Branch IN ('AB','BC','SM','IN')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources