*** edited by: master4eva ***
Please enclose your code in</ code> tags (without the space between the "</" and "code"). This will make your code easier to read online; therefore, encouraging a response to be faster. It is to your own benefit for your question to be answered in future.I have already done the editing to include the <code></ code> tags.
*********
I have a trigger that fires this stored procedure during an update event. But i think i am getting an endless loop. Any idea? Thanks in advance...
<code>
ALTER PROCEDURE TrigRetReqRecIDP1
@.REID int
AS
Declare @.RRID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare @.RetValint
Declare cr cursor
for
select RRID from RequestRecords where REID=@.REID and RRStatus = 'PE'
open cr
fetch next from cr
into
@.RRID
while @.@.fetch_status = 0
Begin
select @.intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk')
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.IDENTITY
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
end
close cr
deallocate cr
GO
and what is the code for the trigger ?|||CREATE TRIGGER trUpdateRequestNewP1
on dbo.Requests
For Update
AS
DECLARE @.REIDint
SELECT @.REID = (SELECT REID from inserted where REStatus ='AC' and REType = 'N')
-- EXEC TrigAssignImpTaskNewP1 @.REID
EXEC TrigRetReqRecIDP1 @.REID|||i dont see any update stmt for records within the proc TrigRetReqRecIDP1...do you have any updates in TrigAssignImpTaskNewP1 ?
hth|||Here's the new version but it is only updating the first record @.RRID...
What I am trying to do is...
I have multiple RRID from RequestRecords where REID = @.REID.
I want to assign each RRID a task from the implementationtasks table...
SO if there are 10 RRID's then ten tasks should be created in the implementationtable. Each @.retval returned from the implementationTasks table will be assigned and updated to each RRID in requestrecords table...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE TrigRetReqRecIDP1
@.REID int
AS
Declare @.RRID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare @.RetValint
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'PE'
open crReqRec
fetch next from crReqRec
into
@.RRID
while @.@.fetch_status = 0
Begin
set @.intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk')
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.IDENTITY
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
FETCH NEXT FROM crReqRec
end
close crReqRec
deallocate crReqRec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|||This one works perfectly but do you have any suggestion to improve it?... Thanks
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOALTER PROCEDURE TrigRetReqRecIDP1
@.REID int
AS
Declare @.RRID int
Declare @.intREID varchar(20)
Declare @.intIMID varchar(20)
Declare @.RetValint
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @.REID and RRSTatus = 'PE'
open crReqRec
fetch next from crReqRec
into
@.RRID
while @.@.fetch_status = 0
Begin
set @.intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk')
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@.intIMID,
'2',
GetDate()
)
SET @.RetVal = @.@.IDENTITY
Update RequestRecords
set ITID = @.RETVal, RRStatus = 'IA'
where REID = @.REID and RRID = @.RRID
FETCH NEXT FROM crReqRec
into
@.RRID
end
close crReqRec
deallocate crReqRec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|||this part of your code :
set @.intIMID = (select IMID from ImplementationGroup where ImGrpName = 'Help Desk')
insert into ImplementationTasks
(IMID,ITStatus,ITStatusDate)
VALUES
(@.intIMID,'2',GetDate())
SET @.RetVal = @.@.IDENTITY
does not have to be in the cursor since it is not dependent on the cursor values..the insert stmt will run for each record in the cursor...unless that is what you want...
you can move this part outside the cursor..so it will run only once...also i'd suggest using SCOPE_IDENTITY() instead of @.@.IDENTITY.
hth
