如何在触发器中将满足条件的记录插入到另一表?有一字段要链接到其它表
我需要在触发器中插入SEND表的时候插入一条记录到SMS表,如果ALARMID是空的话,就直接插入,如果不是空的话,插入SMS表destPhonenumber字段的值就要查找ALARMER表中的手机号和小灵通号,如果两个字段都不为空就用 "# "连接,如果只有某一个,就插入这个字段

表结构如下:
send表
alarmID   警报编号
mobile     手机号
sendmsg   信息

Sms表
destPhoneNumber   手机号
SMSbody                   信息

alarmer警报表
alarmid       警报编号
mobile         手机号
phone           小灵通    



------解决方案--------------------
Create Trigger Insert_Sms On send
For Insert
As
Begin
Insert Sms Select mobile, sendmsg From Inserted Where IsNull(alarmID , ' ') = ' '
Union All Select (Case When IsNull(B.mobile, ' ') != ' ' Then Rtrim(B.mobile) + '# ' Else ' ' End) + Rtrim(B.phone), A.sendmsg From Inserted A Inner Join alarmer B On A.alarmID = B.alarmID Where IsNull(alarmID , ' ') != ' '
End

------解决方案--------------------
--看着太乱，定义变量了
create trigger tritest
on send
for insert
as
declare @number varchar(20),@sendmsg varchar(50)
if inserted.alarmID is null
insert into Sms(destPhoneNumber,SMSbody) select mobile,sendmsg from inserted
else
begin--赋值给变量
select @number =isnull(mobile, ' ')+ '# '+isnull(phone, ' '),@sendmsg=a.sendmsg from inserted a inner join alarmer b on a.alarmID=b.alarmid
--对电话号码相加结果进行处理
if left(@number,1)= '# '
set @number=stuff(@number,1,1, ' ')
if right(@number,1)= '# '
set @number=left(@number,len(@number)-1)
--插入处理后的结果
insert into Sms(destPhoneNumber,SMSbody) values (@number,@sendmsg)
end
------解决方案--------------------
--改一下测试数据好看一些
--测试数据
create table send(alarmid varchar(100),mobile varchar(100),sendmsg varchar(100))

create table Sms(destPhoneNumber varchar(100),SMSbody varchar(100))

create table alarmer(alarmid varchar(100),mobile varchar(100),phone varchar(100))
insert into alarmer
select '001 ', '13512345678 ', '27755123 ' union all
select '002 ', '13612345678 ', '27755456 ' union all
select '004 ', '13812345678 ', '27755789 '
go
--建立触发器
create trigger tr_test on send
for insert
as
insert Sms(destPhoneNumber,SMSbody)
select mobile,sendmsg from inserted
where isnull(alarmid, ' ')= ' '

insert Sms(destPhoneNumber,SMSbody)
select mobile=rtrim(b.mobile)+ '# '+rtrim(b.phone),a.sendmsg
from inserted a inner join alarmer b on a.alarmid=b.alarmid
where isnull(a.alarmid, ' ')!= ' '
go

--测试插入
insert into send
select '001 ', '13512345678 ', '今天上午去图书馆 ' union all
select '002 ', '13612345678 ', '今天下午打球 ' union all
select ' ', '13712345678 ', '今晚吃饭 ' union all
select '004 ', '13812345678 ', '早点休息 '

select * from Sms order by destPhoneNumber

--删除测试数据
drop table send,Sms,alarmer

--查看结果
/*
destPhoneNumber SMSbody
----------------------------------------------------
13512345678#27755123 今天上午去图书馆
13612345678#27755456 今天下午打球
13712345678 今晚吃饭
13812345678#27755789 早点休息
*/