Error in After Insert, Update 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 2008 Forums

Transact-SQL (2008)

Error in After Insert, Update Trigger

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2012-09-19 : 07:23:44

Dear All,

Please help, I have 2 tables: Project & ProjectHours
Columns in Project

--------------------------
ID | Name | ApprovedHours
--------------------------
 1 | ABC  |      100
 2 | XYZ  |      500
 3 | PQR  |     1000
--------------------------

Columns in ProjectHours

------------------------------
ID | ProjectID | HourAssigned
------------------------------
 1 |     1     |    20
 2 |     1     |    30
 3 |     1     |    60
 4 |     2     |    250
 5 |     2     |    250
 6 |     3     |    100
 7 |     3     |    300
 8 |     3     |    400
 9 |     3     |    250
------------------------------

Here, you can see in ProjectHours table, ProjectID = 1 & 3 total HourAssigned exceed the ApprovedHours of Project tables.

So I declared a Trigger which will restrict the exceed of Total HourAssigned as per ApprovedHours, following is the Trigger:

CREATE TRIGGER	dbo.CheckTLAssignedHours
ON		dbo.ProjectHours
AFTER		UPDATE,
		INSERT
AS
IF EXISTS
(
Select TOP 1 p.[id], i.[Totals], p.[ApprovedHours]
FROM [dbo].[Project] p  
INNER JOIN
(
SELECT [ProjectID], sum([HourAssigned]) as [Totals] FROM INSERTED group by [ProjectID]
) AS i ON i.[ProjectID] = p.[id]
GROUP BY p.[id], i.[Totals], p.[ApprovedHours]
HAVING (CAST(i.[Totals] AS FLOAT)) > (CAST(p.[ApprovedHours] AS FLOAT))
)
	BEGIN
		RAISERROR('Assigned Hours Entered is greater than Approved Hours, Please Re-Estimate the Hours', 16, 1)
		ROLLBACK TRAN
	END

But now the problem is after declaring the Trigger, I am unable to update/Insert in the ProjectHours table.

Please Help

Regards,
Daipayan
Software Analyst

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-19 : 09:51:26

If the insert or update statement is within a try/catch block, the call stack would unwind when it encounters the RAISERROR statement. So the transaction may still be open unless there is a roll back in the catch block of the try/catch block.

I would do at least two things:

a) Check if there are any open transactions because of what I described above and roll them back if you see any.

b) swap the order of the RAISERROR and ROLLBACK statements - i.e., ROLLBACK first, and then RAISERROR.

Also, the logic of calculating the SUM would work only if all inserts/updates for a give id are done in a single insert/update because you are summing only the INSERTED virtual table. You may want to sum from the ProjectHours table rather than the INSERTED table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 12:05:51

i would have done it by means of check constraint or instead of trigger to check this logic before I do insert/update action

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

daipayan
Posting Yak Master

181 Posts

Posted - 2012-09-20 : 06:00:55

Ok..I applied the check constraint and it's working now!

Regards,
Daipayan
Software Analyst

Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources