Trigger problem - 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 problem

Author

Topic

mcbtiger
Starting Member

4 Posts

Posted - 2010-05-09 : 14:14:07

I have littel problem in creating trigger
i create table Employees which include four filed

EmpName Sex Age Remark
Amylee Female 45 Over Age Emp
Jack Male 19 Young Age Emp

i create trigger which automaticlly give Remark

create trigger emp on employees
for insert
as
if (select age from inserted)>20
begin
update employees
set status=Over Age Emp’
where age >=20
end
Else
if (select age from inserted)<20
begin
update employees
set status='Young Age Emp'
where age <=20
end
This trigger not Work it update all data filed it give Remark
"Over age Emp" for all emp which have under age 20 and over 20 it give same remark

Muhammadali

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-05-09 : 15:43:03

The problem is your IF statement. If you have more than one row in the inserted table, the sub-query is going to fail. You do not need the check - you can update everything you need to with the following:

UPDATE emp
SET Status = CASE WHEN Age > 20 THEN 'Over Age Emp'
WHEN Age <= 20 THEN 'Young Age Emp'
END
FROM dbo.Employees emp
INNER JOIN inserted i ON i.{pk column} = emp.{pk column};

However, I wouldn't use a trigger to update this column. I would create the column as a computed column on the table and use a CASE expression to return the appropriate value based upon the Age. No reason to have the trigger in the first place.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 13:22:47

why should you need a trigger for this? isnt a computed column enough? just make status column as computed with below definition

status AS CASE WHEN age <=20 THEN 'Young Age Emp' ELSE 'Over Age Emp' END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources