Is SQL Begin /Commit / Rollback Tran Always Good To Use?

Studying for exams are we?

>Q. Can the  SQL Begin /Commit / Rollback Tran be used in ALL Store Procedure and Triggers?
Yes.

>Q. Is this too much coding over-kill?
Define the terms 'too much' and 'coding'.  Mind readers we ain't, unless you would like me to summon the Magic 8-ball here.

I suppose the therm 'coding over-kill' is a bit too broad. I find many SP's and Trigs written very simple without the extra Trans tags or Try Catch's.

Good advice!

I think XACT_STATE is cool, just never used it, found more info here.

Based on original post:
Q. Is there any reason to use Trans in a sp with just a simple Select...?

Back to the XACT_STATE, I currently have this code which inserts any error in a log table and calls Rollback.

Q. How would I use XACT_STATE in the Try Catch using my code example?

ALTER TRIGGER [dbo].[UpdateInventory] ON [dbo].[Inventory]
AFTER INSERT 
AS

DECLARE @T TABLE(Id INT);
SET NOCOUNT ON;
SET XACT_ABORT ON

BEGIN
   BEGIN TRY
         BEGIN TRAN
               UPDATE ...
               UPDATE...
         COMMIT TRAN
   END TRY
   BEGIN CATCH
        DECLARE @ErrorMsg VARCHAR(MAX), 
                         @ErrorNumber INT, 
                         @ErrorProc sysname, 
                         @ErrorLine INT 

	SELECT @ErrorMsg = ERROR_MESSAGE(), 
                     @ErrorNumber =ERROR_NUMBER(),                   
                     @ErrorProc = ERROR_PROCEDURE(), 
                     @ErrorLine = ERROR_LINE();

	ROLLBACK TRAN;

	INSERT INTO Trigger_ErrorLog (ErrorMsg,  ErrorNumber,  ErrorProc,  ErrorLine)
		VALUES (@ErrorMsg, @ErrorNumber, @ErrorProc, @ErrorLine)

   END CATCH 
END

Select allOpen in new window

I agree, thx...