February 12, 2009 at 5:22 am
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......
February 12, 2009 at 5:38 am
for a trigger to be handled correctly, you cannot declare variables....
you have to keep everything SET based.
once you declare a variable, you are telling me you are thinking of one row at a time.
inside a trigger, there are two virtual tables named INSERTED and DELETED...in an insert or Update they have the new values for all the fields
something like this is better: it assumes the INSERTED table more than one row ...whether 5 or 5 million...same code.
UPDATE TD_MEMBER
SET sMemberShipNo=.upper(TD_OPERATOR.sOperatorCode)
FROM INSERTED
INNER JOIN TD_OPERATOR ON INSERTED.nCreatedoperatorid = TD_OPERATOR.nOperatorID
WHERE TD_MEMBER.nMemberID= INSERTED.nMemberID
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply