March 13, 2009 at 7:26 am
I have a delete trigger on a table that contains summary rows.
It simply fetches all associated detail rows from another detail table and attempts to delete them one at a time. If all rows are deleted than it deletes the summary row.
In the detail table I wrote a delete trigger will raise an error if it is unable to delete the row due to any reason.
How do I capture the error message raised from the detail table trigger in the summary table trigger?
I can capture the error number using @@error.
Thanks.
(Code below)
-----------------------------------------------------------------------------------------
-- CREATE TRIGGER to remove a transaction in edi835Summary.
-- Delete each detail transaction. The detail transaction delete TRIGGER
-- will update bill transaciton update paid and adjustment amount
------------------------------------------------------------------------------------------
DROP TRIGGER trDeledi835Summary
go
CREATE TRIGGER trDeledi835Summary ON dbo.edi835Summary
FOR DELETE
AS
DECLARE @summaryTranSeq int, @detailTranSeq int, @sql varchar(255), @err int, @errmsg varchar(250)
SELECT @summaryTranSeq = tranSeq from deleted
-- Get all detail 835 transactions for the summary 835 record
DECLARE dataCursor CURSOR FAST_FORWARD
FOR
SELECT tranSeq
FROM edi835Detail
WHERE summaryTranSeq = @summaryTranSeq
-- Get the data
OPEN dataCursor
/*
0 FETCH statement wAS successful.
-1 FETCH statement failed or the row wAS beyond the result set.
-2 Row fetched is missing.
*/
-- Read next row
FETCH NEXT FROM dataCursor
INTO @detailTranSeq
WHILE @@FETCH_STATUS = 0
BEGIN
-- Attempt to delete the detail row
SELECT @sql= 'delete from edi835Detail where tranSeq = ' + convert(varchar(10), @detailTranSeq)
EXECUTE (@sql)
SELECT @err = @@error
IF @err <> 0
-- If unable to delete, rollback and stop.
BEGIN
SELECT @errmsg = 'Cannot delete payment detail transaction ' + convert(varchar(10), @detailTranSeq)
RAISERROR (@errmsg, 19,1) WITH LOG
ROLLBACK TRANSACTION
BREAK
END
FETCH NEXT FROM dataCursor
INTO @detailTranSeq
END -- WHILE @@FETCH_STATUS = 0
CLOSE dataCursor
DEALLOCATE dataCursor
March 13, 2009 at 7:53 am
It would help us out if you would post in an appropriate forum as this forum does not tell us what version of SQL Server you are using and also makes it so it would never be found if it were not for the Active Threads link.
I would suggest that you do not do this the way you are doing it (using a cursor) because the cursor is lengthening the transaction and using up resources. Why can't you just do this (SQL 2005 or later)?
BEGIN Try
DELETE
FROM
eid835Detail ED
WHERE
EXISTS (SELECT 1 FROM deleted D WHERE D.tranSeq = ED.tranSeq
END TRY
BEGIN CATCH
RAISERROR('Delete failed')
END CATCH
I'd actually prefer to do it in the original T-SQL where I would first delete the detail rows THEN delete the summary row.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 13, 2009 at 7:59 am
Thank you for the response.
I support both SQL 2005 (Express and Standard) and SQL 2000. So whatever I write has to work in both environments.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply