Multi Row Update! - 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

Multi Row Update!

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-07-28 : 04:22:02

Hello Sir,

I have two table:
Table 1: GRADERATE

if object_id('dbo.GradeRate') is not null drop table dbo.GradeRate;
CREATE TABLE dbo.GradeRate(      
    GradeRateID     tinyint identity not null,      
    Course          varchar(10) not null,  
    LowGradeNo      tinyint not null,      
    HighGradeNo     tinyint not null,      
    Grade           varchar(2) not null,      
    Points          tinyint not null,      
    constraint PK_GradeRate primary key(GradeRateID));

Table 2: COURSE_DETAILS

if object_id('dbo.course_details') is not null drop table dbo.course_details;
CREATE TABLE dbo.course_details(      
    Course     tinyint identity not null,      
    Course_name          varchar(10) not null
    constraint PK_course_details primary key(Course));

Now I declared a TRIGGER:

CREATE TRIGGER Insert_GRADERANGE
ON dbo.course_details
After INSERT
AS
SET NOCOUNT ON
insert into dbo.GradeRate
(Course, LowGradeNo, HighGradeNo, Grade, Points)
select dbo.course_details.Course, 0, 34.99, 'F', 0 from dbo.course_details UNION
select dbo.course_details.Course, 35, 42.99, 'D', 1 from dbo.course_details UNION
select dbo.course_details.Course, 43, 50.99, 'D+', 2 from dbo.course_details UNION
select dbo.course_details.Course, 51, 58.99, 'C', 3 from dbo.course_details UNION
select dbo.course_details.Course, 59, 66.99, 'C+', 4 from dbo.course_details UNION
select dbo.course_details.Course, 67, 74.99, 'B', 5 from dbo.course_details UNION
select dbo.course_details.Course, 75, 82.99, 'B+', 6 from dbo.course_details UNION
select dbo.course_details.Course, 83, 90.99, 'A', 7 from dbo.course_details UNION
select dbo.course_details.Course, 91, 100, 'A+', 8 from dbo.course_details

Whenever a course name is being declared, the trigger will be fired and automatically, grade range will be inserted in the graderate table according the that particular course. The trigger is running well but, I am facing a problem, whenever I am inserting course details, whatever the courses are already exists in the course_details table, according to that trigger inserting the data in the graderange table means for one course am getting same data for multiple times.

What modification should I have to make in trigger?
Please Help!

Daipayan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-07-28 : 10:39:29

You need to INNER JOIN each of your unioned statements with the INSERTED table on (course). That way you will only be inserting the ranges for the course you just inserted.

EDIT
Like this:
select c.Course, 0, 34.99, 'F', 0 from dbo.course_details as c inner join inserted i on i.course = c.course UNION ALL

Be One with the Optimizer
TG

daipayan
Posting Yak Master

181 Posts

Posted - 2009-07-28 : 12:20:01

THANK YOU SIR, IT's working...silly me, I didn't apply this simple logic.

quote:
Originally posted by TG

You need to INNER JOIN each of your unioned statements with the INSERTED table on (course). That way you will only be inserting the ranges for the course you just inserted.

EDIT
Like this:
select c.Course, 0, 34.99, 'F', 0 from dbo.course_details as c inner join inserted i on i.course = c.course UNION ALL

Be One with the Optimizer
TG

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources