CREATE TRIGGER [TestTrigger] ON [dbo].[someTable]

AFTER insert, update, delete AS 
BEGIN

    	SET NOCOUNT ON;

	BEGIN TRY

		EXEC [dbo].[TestProcedure] 'someValue'

	END TRY
  
    	BEGIN CATCH

		raiserror('Error in Trigger!', 16, -1)

    	END CATCH;
    	
END;


CREATE PROCEDURE [dbo].[TestProcedure] 
	(
		@someValue varchar(50) 
	)
AS

BEGIN

	BEGIN TRY

	
	END TRY
	
	BEGIN CATCH

		raiserror('Error in TestProcedure!', 16, -1)

	END CATCH;

END;

quote:

---

Originally posted by rmason

If I've got this right, an After trigger will execute the trigger code AFTER the insert/update/delete on the parent table has occured but you are still within the same transaction. Therefore if your trigger code fails for some reason then the WHOLE transaction has to roll back including the insert/update/delete. The only way you could avoid this is if you were to submit the statement without it being in a transaction, that way the insert/update/delete should remain even if there is a subsequent error.

---

quote:

---

Personally I would keep the whole thing ina transaction. The likelyhood of an error occuring while writing to an audit table has to be very slight and in my opinion if that was to error then it show's there's something majorly wrong with my app and I'd want the whole thing to rollback.
Rob.

---

quote:

---

Originally posted by blindman

So why is your trigger crashing?

---

quote:

---

Triggers should be simple code, and thus relatively easy to debug.

---

quote:

---

All the logic for a trigger should be encapsulated within the trigger, making the table somewhat like an OOP object which is sufficient and complete unto itself.

---

quote:

---

Originally posted by Ogreite
Hmmm. One of the core priniciples of Object Orientation design is encapsulation and code re-use. However you are suggesting that I avoid using a stored procedure and have all my code in the trigger. So the same code that deals with this auditing would then appear in every trigger on every table that needs auditing.

---

quote:

---

Originally posted by Ogreite

Thanks for the discussion guys.

The fact that I am using an SP here (called from within the trigger) is actually irrelevant to my problem.
Jen, you are right that this solution of mine is not the best in terms of optimization, but unfortunately I am forced to go down this route. I don’t pay my wages, my boss does ;)
blindman, I respect your opinions but I think I’ll have to agree to disagree on that one.

For the time being lets forget about the stored proc please. I was really after some more information about recovering from errors in a trigger. I want to make it robust so that if it crashes, I can report the error and continue, but also so that I do not loose the modifications that caused the trigger to fire in the first place.
I can’t see why this wouldn’t be possible; after all, the AFTER trigger is fired once the transaction has been successfully applied. I guess I want a trigger that fires after a commit. Is there such a thing?

If I force my trigger to crash for testing, I get the following message: “Transaction doomed in trigger. Batch has been aborted.”
That means I have lost the changes that originally caused my trigger to fire e.g. inserting a new record into the table.

So currently if your trigger crashes, you can’t catch the error and your transaction is always doomed. Is there no way around this?

---