Please support our MS SQL advertiser: Programming Forums
Views: 2606 | Replies: 5
 |
•
•
Join Date: Apr 2006
Posts: 62
Reputation: 
Rep Power: 3
Solved Threads: 0
Junior Poster in Training
HI,
I am trying to return automatic value into another column with using trigger in a table, when I enter new record into that table. It works if it is first data to be in that table. If I have more than one record already, it doesn’t work. I thing it says “more than one value has been returned….”. How can I prevent that? For example; if I am 5th row trigger only read that row instead of all rows. I can’t do that.
CREATE TRIGGER exam_note ON dbo.exam
FOR insert
AS
DECLARE @note decimal(10,2)
select @note = (SELECT e_score FROM exam)
IF ( @note >=0 and @note<=50 )
update exam set e_note='Half'
else IF( @note >=51 and @note<=100 )
update exam set e_note='Full'
Thanks,
I am trying to return automatic value into another column with using trigger in a table, when I enter new record into that table. It works if it is first data to be in that table. If I have more than one record already, it doesn’t work. I thing it says “more than one value has been returned….”. How can I prevent that? For example; if I am 5th row trigger only read that row instead of all rows. I can’t do that.
CREATE TRIGGER exam_note ON dbo.exam
FOR insert
AS
DECLARE @note decimal(10,2)
select @note = (SELECT e_score FROM exam)
IF ( @note >=0 and @note<=50 )
update exam set e_note='Half'
else IF( @note >=51 and @note<=100 )
update exam set e_note='Full'
Thanks,
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,166
Reputation:
Rep Power: 7
Solved Threads: 59
I don't think a trigger is what you want here, consider a user-defined function instead:
and the test case to show how it works
RESULTS:
========
create function getNote (@score int) returns nvarchar(4) as begin declare @note nvarchar(4) if @score between 0 and 50 set @note = 'Half' else set @note = 'Full' return @note end
and the test case to show how it works
--Test case create table #exam ( e_id int identity(1,1) primary key, e_score int, e_note nvarchar(4) ) insert into #exam(e_score, e_note) values (51, dbo.getNote(51)) insert into #exam(e_score, e_note) values (50, dbo.getNote(50)) select * from #exam drop table #exam
RESULTS:
========
e_id e_score e_note ----------- ----------- ------ 1 51 Full 2 50 Half
Last edited by hollystyles : Apr 3rd, 2007 at 8:46 am.
•
•
Join Date: Apr 2006
Posts: 62
Reputation:
Rep Power: 3
Solved Threads: 0
Junior Poster in Training
thank you very much. i have written a function for that before, but i didnt know how to call it in the insert into statements. now, i know. therefore i dont need a trigger for that. thanks to u
•
•
Join Date: Feb 2007
Location: London
Posts: 114
Reputation:
Rep Power: 2
Solved Threads: 8
Junior Poster
A trigger could still perform this task, and may well be more appropriate depending on what methodology you use with regards to placement of business logic
This should cover it
This should cover it
CREATE TRIGGER exam_note ON dbo.exam
FOR insert
AS
UPDATE exam
SET e_note =
(SELECT CASE WHEN e_score <= 50 THEN 'Half'
ELSE 'Full' END
FROM inserted i
WHERE i.keyval = exam.keyval)
•
•
Join Date: Feb 2005
Location: Braintree, UK
Posts: 1,166
Reputation:
Rep Power: 7
Solved Threads: 59
In fact why use a trigger OR a function. Consider this:
create table #exam ( e_id int identity(1,1) primary key, e_score int, e_note nvarchar(4) ) create table #score ( score_id int identity(1,1) primary key, score int ) declare @score int set @score = 10 while @score <= 100 begin insert into #score(score) values (@score) set @score = @score+10 end insert into #exam(e_score, e_note) values (50, case when 50 <= 50 then 'Half' else 'Full' end) insert into #exam(e_score, e_note) select score, case when score <= 50 then 'Half' else 'Full' end from #score select * from #exam drop table #exam drop table #score
•
•
Join Date: Apr 2006
Posts: 62
Reputation:
Rep Power: 3
Solved Threads: 0
Junior Poster in Training
Thanks for both of you. i just let u know that i have done it with function and trigger only. trigger is shown below. I am not professional thou. i am learning. i am very happy with you supports.
thanks again guys
CREATE TRIGGER exam_note ON dbo.exam
FOR insert
AS
DECLARE @note decimal(10,2)
DECLARE @e_id char(10)
select @note = (SELECT e_score FROM inserted)
select @e_id = (SELECT e_id FROM inserted)
IF(@note >=0 and @note<=39 )
update exam set e_note='FF', e_ok='Yes' where e_id=@e_id
else IF(@note >=40 and @note<=49 )
update exam set e_note='DD', e_ok='Yes' where e_id=@e_id
else IF(@note >=50 and @note<=69 )
update exam set e_note='CC', e_ok='Yes' where e_id=@e_id
else IF(@note >=70 and @note<=79 )
update exam set e_note='BB', e_ok='Yes' where e_id=@e_id
else IF(@note >=80 and @note<=100 )
update exam set e_note='AA', e_ok='Yes' where e_id=@e_id
thanks again guys
CREATE TRIGGER exam_note ON dbo.exam
FOR insert
AS
DECLARE @note decimal(10,2)
DECLARE @e_id char(10)
select @note = (SELECT e_score FROM inserted)
select @e_id = (SELECT e_id FROM inserted)
IF(@note >=0 and @note<=39 )
update exam set e_note='FF', e_ok='Yes' where e_id=@e_id
else IF(@note >=40 and @note<=49 )
update exam set e_note='DD', e_ok='Yes' where e_id=@e_id
else IF(@note >=50 and @note<=69 )
update exam set e_note='CC', e_ok='Yes' where e_id=@e_id
else IF(@note >=70 and @note<=79 )
update exam set e_note='BB', e_ok='Yes' where e_id=@e_id
else IF(@note >=80 and @note<=100 )
update exam set e_note='AA', e_ok='Yes' where e_id=@e_id
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
