Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2012
    Posts
    1

    Unanswered: problem with critiria in insert trigger

    i have the following trigger:
    USE [Parcels]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[ITEMS]
    AFTER INSERT

    AS
    declare
    @BARCODE varchar(35),
    @itemno int,
    @PID VARCHAR(50)

    set @BARCODE =(SELECT Barcode from inserted)
    set @itemno = (select itemno from inserted)
    set @PID = (SELECT PID from DBO.V_DataImport WHERE itemno = @itemno)

    BEGIN
    INSERT INTO DBO.FOR_IMPORT(ITEM_NO,MAILITM_FID, MAILITM_PID) VALUES (@itemno, @BARCODE, @PID)
    END

    and the MAILITM_PID field it is not inserterted!! why? somthings wrong with
    set @PID = (SELECT PID from DBO.V_DataImport WHERE itemno = @itemno) ??

    Please help me!!!!!!!!!!!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Have you verified that itemno is present in dbo.V_DataImport?

    How about:
    Code:
    SET @PID = (SELECT a.PID FROM dbo.V_DataImport a INNER JOIN inserted b WHERE a.itemno = b.itemno)
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    yiota, you need to scrap that code and rewrite it from scratch.
    Ask yourself, how does that logic work when more than one record is inserted by a transaction?
    Triggers should NEVER assume single-record transactions.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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