想写一个触发器，把一个表里符合不同条件的记录写入其他不同的表里面，看看能否用case when ，我把问题简化叙述如下：
表a,b,c,d结构相同，其中表a中已经有多条记录如下：
id name age
1 令狐冲 22
2 乔峰 33
3 张无忌 19
4 段誉 18
5 虚竹 21
6 郭靖 42
如下写成三个语句
create trigger xxx on a for insert as
begin
insert into b select * from a where a.id = inserted.id and age <20
insert into c select * from a where a.id = inserted.id and age >= 20 and age <30
insert into d select * from a where a.id = inserted.id and age >= 30
end
这样固然能够完成目标，但是我想简化成一个语句，简化如下：
create trigger xxx on a for insert as
declare @age int
select @age = age from a
begin
insert into case when @age < 20 then b
when @age >= 20 and @age < 30 then c
else d end
select * from a
where a.id = inserted.id
end
执行，提示错误【关键字 'case' 附近有语法错误。】
我的问题是：这个错误如何纠正？能简化成一个语句吗？
菜鸟第一次发问题，浅陋勿笑
------解决方案--------------------
用if....else...
另外,触发器没有考虑到多行的情况,还需修改
- SQL code
create trigger xxx on a for insert as declare @age intselect @age = age from insertd abeginif @age < 20begin insert into b select * from a where a.id = inserted.id endelsebegin if @age >= 20 and @age < 30 begin insert into c select * from a where a.id = inserted.id end else begin insert into d select * from a where a.id = inserted.id endendend
------解决方案--------------------
- SQL code
create trigger xxx on t1 for insert as begin declare @age int select @age = age from inserted if @age<20 insert into b select * from inserted else if (@age>20 and @age<30) insert into c select * from inserted else if @age>30 insert into d select * from insertedend
------解决方案--------------------
- SQL code
create trigger xxx on a for insert as begininsert into b select * from a where a.id = inserted.id and age <20 insert into c select * from a where a.id = inserted.id and age >= 20 and age <30insert into d select * from a where a.id = inserted.id and age >= 30end
------解决方案--------------------
2楼的是有问题的，如果一次插入多条，age不同段，就会出现插错表的现象