Trigger AFTER INSERT

Transact-SQL
1

I'm trying to create a trigger on OTShipments where after a record is inserted, it populates a shipment table.

I get an error on: Line 13 [Batch Start Line 0] Incorrect syntax near ','.
This line: SET @MerchDescrip = MerchDescrip, @BillWeight = BillWeight, @TrackNo = TrackNo, @STCompany = STCompany, @STAdd1 = STAdd1,
@STZip = STZip, @STState = STState, @STCity = STCity, @Ref2 = Ref2, @Ref1 = Ref1

I keep staring at the SET statement and I don't see a syntax error. What am I missing?

Thanks!

CREATE TRIGGER dbo.OTInsertShipments
ON OTShipments
AFTER INSERT

AS

SELECT TrackNo, STCompany, STAdd1, STAdd2, STAdd3, STZip, STState, STCity, Ref2, Ref1, MerchDescrip, BillWeight
FROM OTShipments

DECLARE @MerchDescrip varchar(35), @BillWeight varchar(35), @TrackNo varchar(35), @STCompany varchar(35), @STAdd1 varchar(35),
@STZip varchar(35), @STState varchar(35), @STCiy varchar(35), @Ref2 varchar(35), @Ref1 varchar(35)

SET @MerchDescrip = MerchDescrip, @BillWeight = BillWeight, @TrackNo = TrackNo, @STCompany = STCompany, @STAdd1 = STAdd1,
@STZip = STZip, @STState = STState, @STCity = STCity, @Ref2 = Ref2, @Ref1 = Ref1

INSERT INTO Shipments
(Void, Carrier, MerchID, SFCompany, InvNo, PONo, BillTo, ShipperNo, TPShipperNo, ServiceType, NegRate, PubRate, TrackNo, TrackNoPkg,
ActWeight, Weight, BillWeight, STCompany, STAdd1, STCity, STState, STZip, MerchDescrip, ReturnService, Ref1, Ref2, Ref3, Ref4, Ref5)

VALUES
('N', 'OT', 'FREIGHT', 'WAREHOUSE',@Ref2,@Ref1, 'Third Party', '204389', '199480', 'Ground', 0, 0,@TrackNo,@TrackNo,
@BillWeight,@BillWeight,@BillWeight,@STCompany,@STAdd1,@STCiy,@STState,@STZip,@MerchDescrip, 'N',@Ref1,@Ref2,@Ref2,@Ref1,@MerchDescrip)

2

How and what populates OTShipments in the first place?

3

OTShipments is populated from a shipping application. Upon creating the shipment, data is exported into the table OTShipments. The data exported is limited and I have to build a bit, then export into the table: Shipments

thanks!

4

ok a bit confusing

OTShipments populated from app but then data is exported into that same table OTShipments ?
Is this a home grown application or purchased?

5

The application exports data into OTShipments.
AFTER INSERT into OTShipments I want to INSERT that data, and some hard coded values into Shipments.

thanks

6

you cant do this

SET @MerchDescrip = MerchDescrip, @BillWeight = BillWeight, @TrackNo = TrackNo, 
@STCompany = STCompany, @STAdd1 = STAdd1,
@STZip = STZip, @STState = STState, @STCity = STCity, 
@Ref2 = Ref2, @Ref1 = Ref1

you have to use a select statement to assign to these variables. Which rows in OTShipments do you want to copy to Shipment?

7

My intent was to get the above select statement, then SET the variables to the above values.

8

which rows do you want from OTShipments? The ones that just got inserted into it or ?...

9

Yes, the row(s) that just got inserted. In effect, OTShipments will just be a temp table. Once I harvest the data I will delete the records.

10

something like this

CREATE TRIGGER dbo.OTInsertShipments
ON OTShipments
AFTER INSERT

AS

INSERT INTO Shipments
(Void, Carrier, MerchID, SFCompany, InvNo, PONo, BillTo, ShipperNo, TPShipperNo, 
ServiceType, NegRate, PubRate, TrackNo, TrackNoPkg,ActWeight, Weight, 
BillWeight, STCompany, STAdd1, STCity, STState, STZip, MerchDescrip, ReturnService, Ref1, Ref2, Ref3, Ref4, Ref5)    
SELECT 'N', 'OT', 'FREIGHT', 'WAREHOUSE',Ref2,Ref1, 'Third Party', '204389', '199480', 'Ground', 0, 0,TrackNo,TrackNo,
BillWeight,BillWeight,BillWeight,STCompany,STAdd1,STCiy,STState,STZip,MerchDescrip, 'N',Ref1,Ref2,Ref2,Ref1,MerchDescrip
        FROM inserted

looks messy and you have column STCity missing.

11

Good grief! I missed the mark by a long shot. Your code works as I needed. Thank you very much!

12

you can even totally avoid trigger if you have control over the application code.

13

Understood. That's what started this odyssey. I have limited control over the app's export. It won't let me hard code anything.

Thanks!

14

is this a home grown tool? or purchased tool?

15

A small local carrier's application. Not ours.

16

and you are allowed to modify their backend? would that break any agreement you might have with them if it chokes the application. I would go for maybe not a trigger but a SQL Job that extracts the data you want.
Probably if you specified what exactly you are attempting to do folks here could provide you a more sustainable solution.