SQL Server Performance

Trigger Problem

Discussion in 'General Developer Questions' started by mpolaiah, Feb 12, 2009.

  1. mpolaiah New Member

    Hai all,

    i am inserting the bulk data in my table(at a time 2 or more records).but i am writing the trigger....for after insert the record then update the same record like......


    --drop trigger TRIGG_TD_MEMBER
    CREATE TRIGGER TRIGG_TD_MEMBER
    ON TD_MEMBER AFTER INSERT
    AS
    BEGIN

    DECLARE @pin_sMemberShipID VARCHAR(25)
    DECLARE @pin_sMemberShipNo VARCHAR(50)
    DECLARE @pin_nActionOperatorID int

    set @pin_sMemberShipID =( select max(nMemberid) FROM TD_MEMBER )
    SET @pin_nActionOperatorID= (select nCreatedoperatorid from TD_MEMBER where nMemberid=@pin_sMemberShipID)
    set @pin_sMemberShipNo = (select upper(sOperatorCode) FROM TD_OPERATOR where nOperatorID = @pin_nActionOperatorID)

    set @pin_sMemberShipNo = @pin_sMemberShipNo + @pin_sMemberShipID
    UPDATE TD_MEMBER SET sMemberShipNo= @pin_sMemberShipNo WHERE nMemberID= @pin_sMemberShipID

    END



    but i am insert the 5 records only last record only update remaing records not update.
    how to slove the problem......
  2. Adriaan New Member

    Triggers give you one or two snapshots of the rows affected, which you can use within your trigger as if they were regular tables.
    The trick is to skip all those variables, and do it set-based, using an UPDATE query with a FROM clause that JOINs the underlying table to the snapshot ...

    UPDATE TD_MEMBER
    SET sMemberShipNo = i.MemberShipNo, ..........
    FROM TD_MEMBER
    INNER JOIN inserted i
    ON TD_MEMBER.nMemberID = i.nMemberID
    Not sure that I understand why you're using the MAX() values in your script, but you should be able to apply the same logic here.
  3. FrankKalis Moderator

    As Adriaan mentioned, if you can not 100% rule out that you only have to deal with just one row in the trigger all the time, you have to include the inserted and/or deleted pseudo tables in the trigger. Check out BOL for more explanations on that.

Share This Page