CONDITIONAL TRIGGER FOR INSERT

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

SQL Server 2000 Forums

SQL Server Development (2000)

CONDITIONAL TRIGGER FOR INSERT

Author

Topic

capooti
Starting Member

4 Posts

Posted - 2002-04-11 : 06:16:10

Hi All

Let's suppose I have the following table:
Fields:
PersonID
Name
Money
Description

I want a trigger that automatically insert a description when you insert a new record or more new records at a time.
The appropriate description depends on the money amount.

It should be something like that:

create trigger triInsertDescription on tablename for insert
as
update tablename
if money < 20 then
set tablename.description = 'poor'
if money >= 20 and money < 50 then
set tablename.description = 'medium'
if money >= 50 then
set tablename.description = 'medium'
from tablename
inner join inserted
on tablename.PersonID = inserted.PersonID

It can be inserted one record at a time, but also n records can be inserted from other tables.
Someone would be so kind to tell me how to do this?

Thanks in advance!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-11 : 07:50:39

This will work:

CREATE TRIGGER triInsertDescription ON tablename FOR INSERT
AS
UPDATE T
SET description = CASE WHEN T.money < 20 then 'poor'
WHEN T.money BETWEEN 20 and 50 then 'medium'
WHEN T.money > 50 THEN 'medium' --should this be changed???
FROM tablename T
INNER JOIN inserted I
ON T.PersonID = I.PersonID