Results 1 to 3 of 3

Thread: Update Trigger

  1. #1
    Join Date
    Mar 2004
    Posts
    29

    Unanswered: Update Trigger

    I have created a table with the following columns Jobnumber varchar(20), weight real(4), freightcost money(8), trackingnumber vchar(50), comments varchar(2000) and voidid varchar(3)

    I wrote a trigger that updates this data based on the voidid to update the package table as followed:
    CREATE TIGGER [UPS] ON dbo.UPSSHIPMENT
    FOR INSERT
    AS
    DECLARE @JOBNUMBER CHAR(20)
    DECLARE @WEIGHT REAL(4)
    DECLARE @FREIGHTCOST MONEY(8)
    DECLARE @TRACKINGNUMBER CHAR(25)
    DECLARE @SHIPMETHOD CHAR(50)
    DECLARE @voidid char(2)



    SELECT @JOBNUMBER=JOBNUMBER,
    @WEIGHT=WEIGHT,
    @FREIGHTCOST=FREIGHTCOST,
    @TRACKINGNUMBER=TRACKINGNUMBER,
    @SHIPMETHOD=SHIPMETHOD,
    @VOIDID=VOIDID
    FROM INSERTED



    UPDATE PACKAGE
    SET PACKAGE.WEIGHT = @WEIGHT,
    PACKAGE.FREIGHTCOST = @FREIGHTCOST,
    PACKAGE.TRACKINGNUMBER = @TRACKINGNUMBER,
    PACKAGE.COMMENTS = @SHIPMETHOD
    WHERE PACKAGE.JOBNUMBER = @JOBNUMBER
    AND @VOIDID = 'N'



    UPDATE PACKAGE
    SET PACKAGE.WEIGHT = '',
    PACKAGE.TRACKINGNUMBER = '',
    PACKAGE.COMMENTS = 'UPS VOID',
    PACKAGE.FREIGHTCOST = ''
    WHERE PACKAGE.JOBNUMBER = @JOBNUMBER
    AND @VOIDID = 'Y'

    I am getting the following error see attached.
    Any help would be great Thank you!
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are making the common mistake of assuming that the trigger fires once for each row. Triggers fire once for each transaction, and thus the inserted table may contain more than one record. So your assignment of multiple records to variables is faulty. Use this method instead:
    Code:
    CREATE TIGGER [UPS] ON dbo.UPSSHIPMENT
    FOR INSERT
    AS
    BEGIN
    UPDATE	PACKAGE
    SET	WEIGHT = inserted.WEIGHT,
    	FREIGHTCOST = inserted.FREIGHTCOST,
    	TRACKINGNUMBER = inserted.TRACKINGNUMBER,
    	COMMENTS = inserted.SHIPMETHOD
    FROM	PACKAGE
    	INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE	inserted.VOIDID = 'N'
    
    UPDATE	PACKAGE
    SET	WEIGHT = '',
    	FREIGHTCOST = '',
    	TRACKINGNUMBER = '',
    	COMMENTS = 'UPS VOID'
    FROM	PACKAGE
    	INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
    WHERE	inserted.VOIDID = 'Y'
    END
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Posts
    29
    Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •