CREATE TRIGGER	dbo.Check_Marks
ON		dbo.student_marks
AFTER		UPDATE,
		INSERT
AS
IF EXISTS	(
			SELECT TOP 1	m.course_code, m.marks_type
			FROM		dbo.marks_assign AS m
			INNER JOIN	(
						SELECT		course_code, marks_type
						FROM		inserted
					) AS i ON i.course_code = m.course_code
						AND i.marks_type = m.marks_type
			Group By m.course_code, m.marks_type
			HAVING		 (CAST(i.marks AS SMALLINT)) > (CAST(m.marks_assign AS SMALLINT))
		)
	BEGIN
		RAISERROR('Marks Entered is greater than Assigned Marks', 16, 1)
		ROLLBACK TRAN
	END

-------------    ------------
student_marks    marks_assign
-------------    ------------
studentMarks_ID  marksAssign_ID
student_ID
course_code----->course_code
marks_type------>marks_type
marks            marks_assign
-------------  ------------

Server: Msg 207, Level 16, State 3, Procedure Check_Marks, Line 6
Invalid column name 'marks'.

INNER JOIN	(
						SELECT	       course_code
                                                              ,marks_type
                                                              ,marks -- THIS LINE IS ADDED
						FROM		inserted
					) AS i ON i.course_code = m.course_code

Server: Msg 8121, Level 16, State 1, Procedure Check_Marks, Line 6
Column 'i.marks' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8121, Level 16, State 1, Procedure Check_Marks, Line 6
Column 'm.marks_assign' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

quote:

---

Originally posted by daipayan

I have a Triiger, that checking that marks_assign in table dbo.marks_assign should br less than the marks of table student_marks:

CREATE TRIGGER	dbo.Check_Marks
ON		dbo.student_marks
AFTER		UPDATE,
		INSERT
AS
IF EXISTS	(
			SELECT TOP 1	m.course_code, m.marks_type,i.marks,m.marks_assign 
			FROM		dbo.marks_assign AS m
			INNER JOIN	(
						SELECT		course_code, marks_type,marks
						FROM		inserted
					) AS i ON i.course_code = m.course_code
						AND i.marks_type = m.marks_type
			Group By m.course_code, m.marks_type,i.marks,m.marks_assign 
			HAVING		 (CAST(i.marks AS SMALLINT)) > (CAST(m.marks_assign AS SMALLINT))		)
	BEGIN
		RAISERROR('Marks Entered is greater than Assigned Marks', 16, 1)
		ROLLBACK TRAN
	END

My two tables are:

-------------    ------------
student_marks    marks_assign
-------------    ------------
studentMarks_ID  marksAssign_ID
student_ID
course_code----->course_code
marks_type------>marks_type
marks            marks_assign
-------------  ------------

Am getting following error, while excuting the trigger:

Server: Msg 207, Level 16, State 3, Procedure Check_Marks, Line 6
Invalid column name 'marks'.

Daipayan

---