trigger to insert

This is my question and i have the following scripts  ineed to create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named ShippingLabels.



CREATE TABLE ShippingLabels
(VendorName       varchar(50),
 VendorAddress1   varchar(50),
 VendorAddress2   varchar(50),
 VendorCity       varchar(50),
 VendorState      char(2),
 VendorZipCode    varchar(20))

 I need this The trigger should fire any time the PaymentTotal column of the Invoices table is updated. The structure of the ShippingLabels table is as follows


CREATE TRIGGER addto_shippinglabels

ON invoices

FOR UPDATE

 BEGIN


    INSERT INTO ShippingLabels

        (vendorname,address1,address2)

        SELECT     up.vendorid, up.address1, up.address2

            FROM invoices up  
 
    END

 

GO

tagtekinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuyBDCommented:
Try this

CREATE TRIGGER addto_shippinglabels
ON invoices
FOR UPDATE
AS
IF UPDATE(PaymentTotal)
 BEGIN
    INSERT INTO ShippingLabels
        (vendorname,address1,address2)
        SELECT     up.vendorid, up.address1, up.address2
            FROM invoices up  
    END
GO
0
Anthony PerkinsCommented:
Try it this way:

CREATE TRIGGER addto_shippinglabels  ON invoices

FOR UPDATE

As

IF UPDATE(PaymentTotal)
   Begin
      INSERT ShippingLabels(vendorname,address1,address2)
      SELECT      i.vendorid, i.address1, i.address2
      From      Inserted
    END
0
tagtekinAuthor Commented:
Server: Msg 170, Level 15, State 1, Procedure addto_shippinglabels, Line 20
Line 20: Incorrect syntax near 'up'.

/*
CREATE TABLE ShippingLabels
(VendorName       varchar(50),
 VendorAddress1   varchar(50),
 VendorAddress2   varchar(50),
 VendorCity       varchar(50),
 VendorState      char(2),
 VendorZipCode    varchar(20))

*/
CREATE TRIGGER addto_shippinglabels
ON invoices
FOR UPDATE
AS
IF UPDATE(PaymentTotal)
 BEGIN
    INSERT INTO ShippingLabels
        (vendorname,VendorAddress1,VendorAddress2)
        SELECT     up.vendorid, up.vendoraddress1, up.vendoraddress2
            FROM invoices join vendors on invoices.vendorid=vendors.vendorid up  
    END
GO
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

tagtekinAuthor Commented:
do i use the i in the update statement?
0
tagtekinAuthor Commented:
Server: Msg 107, Level 16, State 2, Procedure addto_shippinglabels, Line 21
The column prefix 'up' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure addto_shippinglabels, Line 21
The column prefix 'up' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure addto_shippinglabels, Line 21
The column prefix 'up' does not match with a table name or alias name used in the query.


CREATE TRIGGER addto_shippinglabels
ON invoices
FOR UPDATE
AS
IF UPDATE(PaymentTotal)
 BEGIN
    INSERT INTO ShippingLabels
        (vendorname,VendorAddress1,VendorAddress2)
        SELECT     up.vendorid, up.vendoraddress1, up.vendoraddress2
            FROM inserted
    END
GO
0
Anthony PerkinsCommented:
Let's try it again:

CREATE TRIGGER addto_shippinglabels  ON invoices

FOR UPDATE

As

IF UPDATE(PaymentTotal)
   Begin
     INSERT ShippingLabels(vendorname,address1,address2)
     SELECT     vendorid, address1, address2
     From     Inserted
    END
0
tagtekinAuthor Commented:
yes but some how i need to join the vendors table becuase that table is the only one with the addresses like
INSERT INTO ShippingLabels
        (vendorname,VendorAddress1,VendorAddress2)
        SELECT     up.vendorid, up.vendoraddress1, up.vendoraddress2
            FROM invoices join vendors on invoices.vendorid=vendors.vendorid up  
0
tagtekinAuthor Commented:
or do the trigger gets those from the update statements?
0
HuyBDCommented:
Try this!

CREATE TRIGGER addto_shippinglabels
ON invoices
FOR UPDATE
AS
DECLARE @vendorid INT
IF UPDATE(PaymentTotal)
 BEGIN
    SELECT @vendorid=vendorid FROM INSERTED
    IF EXISTS (SELECT 1 FROM ShippingLabels WHERE vendorid=@vendorid)
      BEGIN
        INSERT INTO ShippingLabels(vendorname,address1,address2)
        SELECT     up.vendorid, up.address1, up.address2
            FROM INSERTED
     END
   ELSE
    BEGIN
        UPDATE ShippingLabels SET ShippingLabels.vendorname=up.vendorid,ShippingLabels.address1=up.address1,ShippingLabels.address2=up.address2
        FROM INSERTED AS up WHERE ShippingLabels.vendorid=vendorid
    END
 END
GO
0
tagtekinAuthor Commented:
I still dont see the join the rest looks good but again the address values are coming from the different table called vandors so some how i need to get those but do i get thosefrom the update statement that triggers the addto_shippinglabels?
0
tagtekinAuthor Commented:
I guess im explainging wrong here it is

I have 3 tables invoices with the column vendorid , vendors with vendorid, address1 and address2, and shipping labels

 I need this The trigger should fire any time the PaymentTotal column of the Invoices table is updated and trigger should insert the vendorid and address 1 and 2 to the labels.

I m little confused about this one.
0
HuyBDCommented:
because you select from inserted(value was changed), so you use this value for your query,

CREATE TRIGGER addto_shippinglabels
ON invoices
FOR UPDATE
AS
DECLARE @vendorid INT
IF UPDATE(PaymentTotal)
 BEGIN
    SELECT @vendorid=vendorid FROM INSERTED
    IF EXISTS (SELECT 1 FROM ShippingLabels WHERE vendorid=@vendorid)
      BEGIN
        INSERT INTO ShippingLabels(vendorid,vendorname,address1,address2)
        SELECT    vendorid , vendorid,  address1, address2
            FROM INSERTED
     END
   ELSE
    BEGIN
        UPDATE ShippingLabels SET ShippingLabels.vendorname=up.vendorid,ShippingLabels.address1=up.address1,ShippingLabels.address2=up.address2
        FROM INSERTED AS up WHERE ShippingLabels.vendorid=vendorid
    END
 END
GO
0
tagtekinAuthor Commented:
ok i got it this far but there is no address in the invoices table the address columns are in the vendors table
0
Anthony PerkinsCommented:
Try it this way:

CREATE TRIGGER addto_shippinglabels  ON invoices

FOR UPDATE

As

IF UPDATE(PaymentTotal)
   Begin
      INSERT ShippingLabels(vendorname,address1,address2)
      SELECT      v.vendorname, v.vendoraddress1, v.vendoraddress2
      From      Inserted i
                  join vendors v on i.vendorid = v.vendorid
    END
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tagtekinAuthor Commented:
yes thats great im sorry i kept asking the same question i haveone more to go. Thanks for your help.

if you have time the link for the other one is here
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21823920.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.