Trigger help

[Fenerbahce]

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,

[hollystyles]

I don't think a trigger is what you want here, consider a user-defined function instead:

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

[Fenerbahce]

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

[davidcairns]

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

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)

[hollystyles]

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

[Fenerbahce]

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 [EMAIL="e_id=@e_id"]e_id=@e_id[/EMAIL]
else IF(@note >=40 and @note<=49 )
update exam set e_note='DD', e_ok='Yes' where [EMAIL="e_id=@e_id"]e_id=@e_id[/EMAIL]
else IF(@note >=50 and @note<=69 )
update exam set e_note='CC', e_ok='Yes' where [EMAIL="e_id=@e_id"]e_id=@e_id[/EMAIL]
else IF(@note >=70 and @note<=79 )
update exam set e_note='BB', e_ok='Yes' where [EMAIL="e_id=@e_id"]e_id=@e_id[/EMAIL]
else IF(@note >=80 and @note<=100 )
update exam set e_note='AA', e_ok='Yes' where [EMAIL="e_id=@e_id"]e_id=@e_id[/EMAIL]