Trigger error handling - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger error handling

Author

Topic

blakmk
Starting Member

45 Posts

Posted - 2005-10-18 : 04:51:01

Im trying to set up a set of a trigger that will validate a whole batch of insert statements.
If any of the insert statements cause the trigger to throw an error I want the entire batch to roll back.
I can get the trigger to throw the appropriate error and roll back an individual transaction.
But when I run a batch of transactions and one fails they all get inputted.

Heres what i am doing:

CREATE TRIGGER TR_Insert_Brochure_Distribution_BD_Map
ON Brochure_Distribution_BD_Map
FOR INSERT, UPDATE
AS

IF EXISTS ( SELECT *
FROM Brochure_Distribution_BD_Map bdm
INNER JOIN inserted i ON bdm.brochure_code = i.brochure_code
AND (i.date_to BETWEEN bdm.date_from AND bdm.date_to
OR i.date_from BETWEEN bdm.date_from AND bdm.date_to)
WHERE NOT(bdm.brochure_code = i.brochure_code AND
i.date_to = bdm.date_to AND
i.date_from = bdm.date_from)
)
BEGIN

RAISERROR ('Date range conflicts with an existing date range',16, 1)
END

set ansi_nulls on
set ansi_warnings on
SET xact_abort ON

BEGIN TRANSACTION T1
INSERT INTO call_centre_agent_asp_map
(Call_centre_agent_id, ext_number, call_centre_agent_valid_from, call_centre_agent_valid_to)
values (4, '1111', '01-Jan-2005', '31-jan-2005')
INSERT INTO call_centre_agent_asp_map
(Call_centre_agent_id, ext_number, call_centre_agent_valid_from, call_centre_agent_valid_to)
values (4, '1111', '01-feb-2005', '27-feb-2005')
INSERT INTO call_centre_agent_asp_map
(Call_centre_agent_id, ext_number, call_centre_agent_valid_from, call_centre_agent_valid_to)
values (4, '1111', '02-feb-2005', '03-feb-2005')
INSERT INTO call_centre_agent_asp_map
(Call_centre_agent_id, ext_number, call_centre_agent_valid_from, call_centre_agent_valid_to)
values (4, '1111', '01-Mar-2005', '31-Mar-2005')

COMMIT TRANSACTION T1

I would have thought the "SET xact_abort ON" would help to catch this error. But unforutanatly it does not.
Does anyone have any ideas how I can rewrite this?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-18 : 04:57:56

You can search for the @@Error on Book online they have same sample which may helps you for the multiple insert in the trigger..

Complicated things can be done by simple thinking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources