May 9, 2010 at 12:18 pm
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
May 9, 2010 at 12:51 pm
Surely the update statements need to include a where clause condition referencing the inserted table. You only want to update the rows that triggered the trigger.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2010 at 1:53 pm
I answered this over on SQL Team also, so check back there for that response. Basically, your trigger is going to fail for multiple rows on this:
IF (SELECT Age FROM inserted)
Also, you don't need the IF statement - this can be rewritten to use a CASE expression to determine what values to update, as in:
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 would not recommend doing this - as it is not needed. You can create a computed column on the table and get the same results using the above CASE expression for the computed column.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy