Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 966
  • Last Modified:

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

0
tagtekin
Asked:
tagtekin
  • 9
  • 3
  • 3
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now