＋设为首页 ＋添加到收藏夹
在mo_receive表结构：
CREATE TABLE [dbo].[MO_RECEIVE] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Mobile] [char] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Content] [varchar] (140) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ReceiveTime] [datetime] NOT NULL
) ON [PRIMARY]
当插入的新记录到mo_receive，并且Content字段是‘0000’时，根据mo_receive.Mobile
选择表sercice_user的ServiceName字段，结构如下:
CREATE TABLE [dbo].[Service_USER] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[YD_LT] [bit] NOT NULL ,
[ServiceName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ServiceID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Mobile] [char] (11) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Corp] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Dept] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[OrderTime] [datetime] NOT NULL ,
[Status] [bit] NOT NULL
) ON [PRIMARY]
当有记录时，如有3条符合的，则构造一条insert 语句插如到send表。
小弟不知怎样写： 0 ServiceName，1 ServiceName，3 ServiceName 怎样写这个循环语句？？？
--不太明白,是这个意思吗?
create trigger t_insert on MO_RECEIVE
for insert
as
if exists(select 1 from inserted where Content=0000)
insert into send(ServiceName)
select a.ServiceName
from Service_USER a join inserted b on a.Mobile=b.Mobile
where b.Content=0000
go
create trigger trig_insert_MO_RECEIVE on MO_RECEIVE
for insert
as
set nocount on
declare @count int
declare @uname varchar(100)
set @count=0
if (select count(b.servicename) from inserted a,Service_USER b where a.mobile=b.mobile and a.content=0000)=3
begin
declare cur1 cursor for select b.servicename from inserted a,Service_USER b where a.mobile=b.mobile and a.content=0000
open cur1
fetch next from cur1 into @uname
while @@fetch_status=0
begin
insert send(col1,col2) select @count,@uname
set @count=@count+1
fetch next from cur1 into @uname
end
close cur1
deallocate cur1
end
CREATE trigger insert_QX_0000 on mo_receive for insert as
if exists (select * from inserted a where upper(left(a.content,4)) = 0000 and left(a.mobile,3) in (134,135,136,137,138,139))
begin
DECLARE @num int,@scontent varchar(140),@name varchar(50)
set @num=0
DECLARE service_Cursor CURSOR FOR
select servicename from service_user where mobile=(select mobile from inserted)
OPEN service_Cursor
FETCH NEXT FROM service_Cursor into @name
while @num<@@cursor_rows
begin
set @scontent=@scontent + CAST(@num as varchar(10) )+@name
set @num=@num+1
FETCH NEXT FROM service_Cursor into @name
end
close service_Cursor
deallocate service_Cursor
insert into send_quick(mobile,content,feeusertype,feetype,feevalue,yd_lt,servicename,serviceid) select mobile,@scontent,0,02,000010,0,退定,0000 from inserted
end
