Error in After Insert, Update Trigger

Question

Post reply

Error in After Insert, Update Trigger

mail2payan

SSCrazy

Points: 2286
More actions
September 19, 2012 at 7:17 am

#281912

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 TRIGGERdbo.CheckTLAssignedHours
ONdbo.ProjectHours
AFTERUPDATE,
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
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

vinuvt Old Hand Points: 386 More actions · Answer 1

Well.. isn't that what your trigger supposed to do ..???

Lowell SSC Guru Points: 323518 More actions · Answer 2

well i converted/scripted everything to consumable format, but i have no idea what you tried to insert into the table.

a simple INSERT INTO ProjectHours VALUES (10,0,0) works woithout an error, for example.

can you show us the command you rant that inserts, and why it should not raise the error int eh trigger?:

CREATE TABLE Project (ID int,Name varchar(30) ,ApprovedHours int)

INSERT INTO Project

SELECT 1,'ABC',100 UNION ALL

SELECT 2,'XYZ',500 UNION ALL

SELECT 3,'PQR',1000

CREATE TABLE ProjectHours (ID int,ProjectID int,HourAssigned int)

INSERT INTO ProjectHours

SELECT 1,1,20 UNION ALL

SELECT 2,1,30 UNION ALL

SELECT 3,1,60 UNION ALL

SELECT 4,2,250 UNION ALL

SELECT 5,2,250 UNION ALL

SELECT 6,3,100 UNION ALL

SELECT 7,3,300 UNION ALL

SELECT 8,3,400 UNION ALL

SELECT 9,3,250

GO

CREATE TRIGGERdbo.CheckTLAssignedHours

ONdbo.ProjectHours

AFTERUPDATE,

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

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!