Am I correctly catching errors in this trigger

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

Transact-SQL (2000)

Am I correctly catching errors in this trigger

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-08-10 : 06:13:38

I'm trying to implement error catching in my triggers. Could someone please tell me whether I have placed the error-check in the correct place in this trigger?

Basically I am executing a proc to return a calculated value, then inserting that value into another table via a second proc. I need to ensure that the error check part is correct however, and that the transaction will correctly be rolled back if it should fail...


CREATE TRIGGER trg_fireCalculateAggregateCompetencyAfterInsert ON tbl_CompetencyHistory
AFTER INSERT, UPDATE
AS
	BEGIN TRAN

		DECLARE @levelJustInserted numeric(5,2)
		SELECT @levelJustInserted = (SELECT newSkillLevel from inserted)
	
		IF NOT @levelJustInserted IS NULL -- only do a calculation if an actual numeric value was entered
			BEGIN
				-- 1. // get values that have just been inserted into tbl_CompetencyHistory		
				DECLARE @skillID int
				DECLARE @skillGroupID int
				DECLARE @userID int
	
				SELECT @skillID = (SELECT skillID from inserted)
				SELECT @skillGroupID = (SELECT skillGroupID from inserted)
				SELECT @userID = (SELECT userID from inserted)
	
				-- launch sp to calculate NEW aggregate (stored in @newLevel)
				DECLARE @newLevel numeric(5,2)
				EXEC sp_getCalculateAggregateCompetency
					@skillID = @skillID,
					@skillGroupID = @skillGroupID,
					@userID = @userID,
					@aggregateLevel = @newLevel OUTPUT  -- NEW aggregate captured here
	
				-- 2. // Insert the new aggregate skillLevel into tbl_CompetencyAggregate
				EXEC sp_saveCompetencyAggregateLevel
					@skillID = @skillID,
					@skillGroupID = @skillGroupID,
					@userID = @userID,
					@newLevel = @newLevel
				IF @@ERROR <> 0
					BEGIN
						ROLLBACK TRAN
						RAISERROR('An error occurred while saving the new aggregate competency level', 10, 1)
					END
			END

	COMMIT TRAN

T-SQL seemed so easy in the training videos...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-10 : 06:28:21

You need to test @@error after each statement.
Also your trigger only copes wityh single row insert/updates.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-08-10 : 10:08:14

Hi nr

>>You need to test @@error after each statement.
My example wasn't the best looking back at it. Within that Proc, there is just one command that performs an edit/update to the database, and that's the SP fired directly before the @@ERROR check. Therefore what I mean is that if the SP before the error check encounters problems, will the current trigger's activity rollback as well? i.e.


CREATE TRIGGER trg_fireCalculateAggregateCompetencyAfterInsert ON tbl_CompetencyHistory
AFTER INSERT, UPDATE
AS
	BEGIN TRAN

		DECLARE @levelJustInserted numeric(5,2)
		SELECT @levelJustInserted = (SELECT newSkillLevel from inserted)
	
		IF NOT @levelJustInserted IS NULL -- only do a calculation if an actual numeric value was entered
			BEGIN
				-- 1. // get values that have just been inserted into tbl_CompetencyHistory		
				DECLARE @skillID int
				DECLARE @skillGroupID int
				DECLARE @userID int
	
				SELECT @skillID = (SELECT skillID from inserted)
				SELECT @skillGroupID = (SELECT skillGroupID from inserted)
				SELECT @userID = (SELECT userID from inserted)
	
				-- launch sp to calculate NEW aggregate (stored in @newLevel)
				DECLARE @newLevel numeric(5,2)
				EXEC sp_getCalculateAggregateCompetency
					@skillID = @skillID,
					@skillGroupID = @skillGroupID,
					@userID = @userID,
					@aggregateLevel = @newLevel OUTPUT  -- NEW aggregate captured here
	
				-- 2. // Insert the new aggregate skillLevel into tbl_CompetencyAggregate
				EXEC sp_saveCompetencyAggregateLevel <-- If this fails (i.e. if RAISERROR is called within it...
					@skillID = @skillID,
					@skillGroupID = @skillGroupID,
					@userID = @userID,
					@newLevel = @newLevel
				IF @@ERROR <> 0 <-- ...will this be TRUE and rollback
					BEGIN
						ROLLBACK TRAN
						RAISERROR('An error occurred while saving the new aggregate competency level', 10, 1)
					END
			END

	COMMIT TRAN

>>Also your trigger only copes wityh single row insert/updates.
That's fine, it'll only be dealing with single-row activity anyway.

T-SQL seemed so easy in the training videos...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-10 : 10:51:54

A trigger is always run within a transaction so there is no need for your begin tran end tran.
If a raiserror is called within an sp (and is the last statement before the return) then the raised error value will be available to the next statement in the calling procedure.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2006-08-10 : 11:14:40

Okay I understand, so that's why we continuously check for errors using a temp variable I guess. In this example, as nothing comes after the other SP is executed, would this RAISERROR command correctly pass the issue back up the chain?


CREATE TRIGGER trg_fireCalculateAggregateCompetencyAfterInsert ON tbl_CompetencyHistory
AFTER INSERT, UPDATE
AS
	DECLARE @levelJustInserted numeric(5,2)
	SELECT @levelJustInserted = (SELECT newSkillLevel from inserted)
	
	IF NOT @levelJustInserted IS NULL -- only do a calculation if an actual numeric value was entered
		BEGIN
			-- 1. // get values that have just been inserted into tbl_CompetencyHistory		
			DECLARE @skillID int
			DECLARE @skillGroupID int
			DECLARE @userID int

			SELECT @skillID = (SELECT skillID from inserted)
			SELECT @skillGroupID = (SELECT skillGroupID from inserted)
			SELECT @userID = (SELECT userID from inserted)

			-- launch sp to calculate NEW aggregate (stored in @newLevel)
			DECLARE @newLevel numeric(5,2)
			EXEC sp_getCalculateAggregateCompetency
				@skillID = @skillID,
				@skillGroupID = @skillGroupID,
				@userID = @userID,
				@aggregateLevel = @newLevel OUTPUT  -- NEW aggregate captured here

			-- 2. // Insert the new aggregate skillLevel into tbl_CompetencyAggregate
			EXEC sp_saveCompetencyAggregateLevel <-- BANG! 
				@skillID = @skillID,
				@skillGroupID = @skillGroupID,
				@userID = @userID,
				@newLevel = @newLevel
			IF @@ERROR <> 0 <-- Passed upwards to calling proc?
					RAISERROR('An error occurred while saving the new aggregate competency level', 10, 1)

		END

T-SQL seemed so easy in the training videos...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources