Trigger Query! - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Trigger Query!

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-06 : 17:27:49

I have a INSERT query:

INSERT INTO mark_absent (attendence_ID, student_ID)
SELECT     dbo.student_details.student_ID, dbo.attendence_date.attendence_ID
FROM         dbo.[term in batch] INNER JOIN
                      dbo.student_details ON dbo.[term in batch].batch = dbo.student_details.batch INNER JOIN
                      dbo.attendence_date INNER JOIN
                      dbo.course_details ON dbo.attendence_date.course_code = dbo.course_details.course_code ON 
                      dbo.[term in batch].term = dbo.course_details.course_level

This part I have to insert in DOT REGION of the following trigger:

CREATE TRIGGER StudentsUpdate
ON attendence_date
AFTER INSERT
AS
BEGIN
......
END

How can this be done??

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:07:03

for that what you need to make sure is to get only records that were affected by insert operation in attendance_date table. This detail you can get from internal temporary table INSERTED


CREATE TRIGGER StudentsUpdate
ON attendence_date
AFTER INSERT
AS
BEGIN
INSERT INTO mark_absent (attendence_ID, student_ID)
SELECT     dbo.student_details.student_ID, i.attendence_ID
FROM         dbo.[term in batch] INNER JOIN
                      dbo.student_details ON dbo.[term in batch].batch = dbo.student_details.batch INNER JOIN
                      INSERTED i INNER JOIN
                      dbo.course_details ON i.course_code = dbo.course_details.course_code ON 
                      dbo.[term in batch].term = dbo.course_details.course_level
END

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-07 : 16:55:58

Thank You...THANK YOU...it's working!

Sir, What should be the alteration in TRIGGER for UPDATE and DELETE??

Daipayan

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-07 : 18:27:33

By taking reference of your TRIGGER, I created same another TRIGGER:

CREATE TRIGGER Insert_Student_Course_MarksType
ON marks_assign
After INSERT
AS
BEGIN
INSERT INTO student_marks (student_ID, course_code, marks_type)
SELECT     dbo.[student specialisation].student_ID, dbo.course_details.course_code, i.marks_type
FROM         dbo.specialisation_combination INNER JOIN
                      dbo.[student specialisation] ON dbo.specialisation_combination.specialisation_ID = dbo.[student specialisation].specialisation_ID INNER JOIN
                      dbo.course_details INNER JOIN
                      INSERTED i ON dbo.course_details.course_code = i.course_code ON 
                      dbo.specialisation_combination.specialisation_1 = dbo.course_details.course_specialisation OR 
                      dbo.specialisation_combination.specialisation_2 = dbo.course_details.course_specialisation
END

Data inserting in marks_assign table TWICE and giving following error message:

Key column information is insufficient or incorrect. Too many rows were affected by update.

What the error in my TRIGGER?
This error is coming after attaching the TRIGGER with marks_assign table!!

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 12:41:26

try like this. learn to use table aliases instead of repeat name everywhere



CREATE TRIGGER Insert_Student_Course_MarksType
ON marks_assign
After INSERT
AS
BEGIN
INSERT INTO student_marks (student_ID, course_code, marks_type)
SELECT     dbo.[student specialisation].student_ID, dbo.course_details.course_code, i.marks_type
FROM         dbo.specialisation_combination  sc
INNER JOIN   dbo.[student specialisation]  ss
ON sc.specialisation_ID = ss.specialisation_ID 
INNER JOIN   dbo.course_details cd
ON sc.specialisation_1 = cd.course_specialisation 
OR sc.specialisation_2 = cd.course_specialisation
INNER JOIN  INSERTED i 
ON cd.course_code = i.course_code 
END

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-08 : 14:56:32

Sir,
Same ERROR coming, no change!!

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 02:37:36

can i ask what is your actual reqmnt?

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-10 : 05:37:31

For the last TRIGGER. my requirement as follows:
I have table called marks_assign, whenever I assiged the marks and marks type for a particular course, say

COURSE: MIS
----------------
MARKS_TYPE MARKS
----------------
Mid-Term    30
End-Term    40
Project     30
----------------

then automatically, in table called student_marks, student ID, course and marks_type, leaving actual_marks column NULL will be inserted, say

------------------------------------------
STUDENT_ID COURSE MARKS_TYPE ACTUAL_MARKS
------------------------------------------
  3015      MIS    Mid-Term     <NULL>
  3015      MIS    End-Term     <NULL>
  3015      MIS    Project      <NULL>
  3016      MIS    Mid-Term     <NULL>
  3016      MIS    End-Term     <NULL>
  3016      MIS    Project      <NULL>
------------------------------------------

Now, Course and marks_type will be fetched from marks_assign table, but student_ID will be fetched student_specialisation table checking that batch in student_specialisation equal to batch in course details and course in course details equal to course in marks assign.
I changed the TRIGGER like this:

CREATE TRIGGER Insert_Student
ON marks_assign
FOR INSERT
AS
BEGIN
INSERT INTO student_marks (student_ID, course_code, marks_type)
SELECT     ss.student_ID, i.course_code, i.marks_type
FROM         INSERTED i INNER JOIN
                      dbo.course_details cd ON i.course_code = cd.course_code INNER JOIN
                      dbo.[student specialisation] ss ON cd.batch = ss.batchcode
END

quote:
Originally posted by visakh16

can i ask what is your actual reqmnt?

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 05:52:05

do you have another trigger on student_marks table?

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-10 : 06:00:19

Yes, another trigger is checking 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 float)) > (CAST(m.marks_assign AS float))		)
	BEGIN
		RAISERROR('Marks Entered is greater than Assigned Marks', 16, 1)
		ROLLBACK TRAN
	END

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 06:26:32

nope, anything that inserts to dbo.marks_assign table?

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-10 : 06:45:42

No, Sir
There is no other INSERT TRIGGER!

quote:
Originally posted by visakh16

nope, anything that inserts to dbo.marks_assign table?

Daipayan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 14:15:59

not insert trigger on dbo.marks_assign table but any other trigger on any other table that inserts to dbo.marks_assign table

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-14 : 06:15:03

Sir,
I resolved the query....

I did this changes in the following triggers:

CREATE TRIGGER StudentsUpdate
ON attendence_date
AFTER INSERT
AS
SET NOCOUNT ON
INSERT INTO mark_absent (attendence_ID, student_ID)
SELECT     dbo.student_details.student_ID, i.attendence_ID
FROM         dbo.[term in batch] INNER JOIN
                      dbo.student_details ON dbo.[term in batch].batch = dbo.student_details.batch INNER JOIN
                      INSERTED i INNER JOIN
                      dbo.course_details ON i.course_code = dbo.course_details.course_code ON 
                      dbo.[term in batch].term = dbo.course_details.course_level

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources