help with trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help with trigger

Author

Topic

avmreddy17
Posting Yak Master

180 Posts

Posted - 2006-01-12 : 16:24:10

Is it nessesary to have a CURSOR in the in the below trigger,
All its doing is taking the MsgOrderID from the Orders table ( on which the trigger fires )
and puting the value in the MsgKeys Table.

Why we have to run a CURSOR on the Inserted table?

CREATE TRIGGER utr_InsertOrdKey
ON dbo.Orders
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @liMaxSeqNo INT
DECLARE @lnMsgOrderID INT

DECLARE Cur_MsgKey CURSOR FOR
SELECT MsgOrderID FROM Inserted

OPEN Cur_MsgKey

FETCH NEXT FROM Cur_MsgKey
INTO @lnMsgOrderID

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @liMaxSeqNo = ISNULL(MAX(SeqNo) ,0) + 1 FROM TradesONLINE..MsgKeys

INSERT INTO TradesONLINE..MsgKeys ( SeqNo , MsgType , Key1 )
SELECT @liMaxSeqNo , 'O' , @lnMsgOrderID

FETCH NEXT FROM Cur_MsgKey
INTO @lnMsgOrderID
END

CLOSE Cur_MsgKey
DEALLOCATE Cur_MsgKey
END

Thx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-12 : 16:32:52

Ummm you don't. Just join to the inserted table. Something like this:

INSERT INTO TradesONLINE..MsgKeys(SeqNo, MsgType, Key1)
SELECT ISNULL(MAX(SeqNo) ,0) + 1, 'O', MsgOrderID
FROM inserted i
INNER JOIN TradesONLINE.MsgKeys m
ON m.YourPK = i.YourPK

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources