October 13, 2004 at 11:56 am
I have a stored procedure that inserts a record into a table that has a trigger setup for Inserts. I debugged the stored procedure using VS .NET and it seems that execution of the stored procedure ends after the insert statement even though there is more code after the statement. Is it possible to return program control to a stored procedure after completion of a trigger? Thanks,
Chad
October 13, 2004 at 5:54 pm
October 14, 2004 at 7:10 am
Are you sure it is not blocking itself. It cannot work on the current record if the trigger has locked it. Need to see what the trigger code is and the sp code to see if there is a potnetial overlap and blocking occurring.
October 14, 2004 at 7:20 am
Certainly. Here it is.
CREATE PROCEDURE dbo.spStatusReviewInsert
(--Pramater List 35 or so fields)
AS
DECLARE @errorcode int
BEGIN TRAN
INSERT INTO StatusReview(--Fields)
VALUES(--Values)
--Execution stops here after completion of trigger. @@ERROR may be zero anyway, but the code should still finish???
SET @errorcode = @@ERROR
RETURN @errorcode
GO
Trigger code
CREATE TRIGGER trg_StatusReviewInsertUpdate ON dbo.StatusReview
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @CountyID int, @TownshipID int, @StartDate datetime, @CompleteDate datetime
SET @CountyID = (SELECT CountyID FROM inserted)
SET @TownshipID = (SELECT TownshipID FROM inserted)
SET @StartDate = (SELECT StartDate FROM inserted)
SET @CompleteDate = (SELECT CompleteDate FROM inserted)
DECLARE @Count int
--Make sure township belongs to county
SELECT * FROM CountyTownship
WHERE CountyTownship.CountyID=@CountyID and CountyTownship.TownshipID=@TownshipID;
SET @Count = @@ROWCOUNT
IF @Count <> 1
BEGIN
RAISERROR('Township does not belong to county.', 16, 1)
ROLLBACK TRAN
RETURN
END
ELSE IF @CompleteDate < @StartDate
BEGIN
RAISERROR('CompleteDate must be later than StartDate.', 16, 1)
ROLLBACK TRAN
RETURN
END
ELSE
BEGIN
COMMIT TRAN
RETURN
END
October 14, 2004 at 7:05 pm
Alright, even though this will compile, the way you're handling transaction is funky. You're opening a transaction in the procedure, and commiting/rolling back the transaction in the trigger. While technically this will work, what if you try to Insert into the table without calling the proc? You're insert is going to fail.
There's also a recordset being returned by the trigger. I'm assuming this is for testing purposes.
Begin and end transactions within either the proc or the trigger:
CREATE PROCEDURE dbo.spStatusReviewInsert
(--Pramater List 35 or so fields)
AS
DECLARE @errorcode int
BEGIN TRAN
INSERT INTO StatusReview(--Fields)
VALUES(--Values)
--Execution stops here after completion of trigger. @@ERROR may be zero anyway, but the code should still finish???
SET @errorcode = @@ERROR
if @errorcode = 0
BEGIN
Commit Tran
END
Else
BEGIN
Rollback Tran
END
RETURN @errorcode
GO
Trigger code
CREATE TRIGGER trg_StatusReviewInsertUpdate ON dbo.StatusReview
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @CountyID int, @TownshipID int, @StartDate datetime, @CompleteDate datetime
SET @CountyID = (SELECT CountyID FROM inserted)
SET @TownshipID = (SELECT TownshipID FROM inserted)
SET @StartDate = (SELECT StartDate FROM inserted)
SET @CompleteDate = (SELECT CompleteDate FROM inserted)
DECLARE @Count int
--Make sure township belongs to county
---CD: What the heck is this? Do you really want to return a result set from your trigger? If this is really what you're trying to do you should do it in the proc instead
SELECT * FROM CountyTownship
WHERE CountyTownship.CountyID=@CountyID and CountyTownship.TownshipID=@TownshipID;
SET @Count = @@ROWCOUNT
IF @Count <> 1
BEGIN
RAISERROR('Township does not belong to county.', 16, 1)
RETURN
END
ELSE IF @CompleteDate < @StartDate
BEGIN
RAISERROR('CompleteDate must be later than StartDate.', 16, 1)
RETURN
END
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply