create trigger tri_for_insert on 表a
for insert
as
declare @code varchar(50)
declare @code1 varchar(50)
declare @realcode varchar(50)
declare @strlen int
declare @PerChar varchar(50)declare insert_cur cursor for select code,code1,realcode from inserted
open insert_cur
fetch next from insert_cur into @code,@code1,@realcode
while @@fetch_status = 0
begin
if @code=@realcode or @code1=@realcode
begin
set @strlen=len(@realcode)
set @i=1
set @ID = 1
while (@ID <= @Length)
begin
set @PerChar = substring(@realcode,@ID,1)
insert dbo.(id,perchar,type) values(@ID,@PerChar,1)
set @ID =@ID+1
end
fetch next from insert_cur into @code,@code1,@realcode
end
close insert_curdeallocate insert_cur
for insert
as
declare @code varchar(50)
declare @code1 varchar(50)
declare @realcode varchar(50)
declare @strlen int
declare @PerChar varchar(50)declare insert_cur cursor for select code,code1,realcode from inserted
open insert_cur
fetch next from insert_cur into @code,@code1,@realcode
while @@fetch_status = 0
begin
if @code=@realcode or @code1=@realcode
begin
set @strlen=len(@realcode)
set @i=1
set @ID = 1
while (@ID <= @Length)
begin
set @PerChar = substring(@realcode,@ID,1)
insert dbo.(id,perchar,type) values(@ID,@PerChar,1)
set @ID =@ID+1
end
fetch next from insert_cur into @code,@code1,@realcode
end
close insert_curdeallocate insert_cur
for insert
as
declare @code varchar(50)
declare @code1 varchar(50)
declare @realcode varchar(50)
declare @strlen int
declare @PerChar varchar(50)declare insert_cur cursor for select code,code1,realcode from inserted
----游标解决批量插入时有符合条件有不符合条件的问题
open insert_cur
fetch next from insert_cur into @code,@code1,@realcode
while @@fetch_status = 0
begin
if @code=@realcode or @code1=@realcode
begin
set @strlen=len(@realcode)
set @i=1
set @ID = 1
while (@ID <= @Length)
begin
set @PerChar = substring(@realcode,@ID,1)
insert 表b(id,perchar,type) values(@ID,@PerChar,1)
set @ID =@ID+1
end
end
fetch next from insert_cur into @code,@code1,@realcode
end
close insert_curdeallocate insert_cur
set nocount on
go
create table a
(
id int identity,
code varchar(20),
code1 varchar(20),
realcode varchar(20)
)
create table b
(
id int identity,perchar varchar(10),type int default 1
)
go
if exists(select 1 from sysobjects where id=object_id('t') and xtype='TR')
drop trigger t
go
create trigger t
on a
for insert
as
if exists(select 1 from inserted where realcode=code or realcode=code1)
begin
declare @sql varchar(2000),@str varchar(1000)
select @sql='',@str=''
select @str=@str+code from inserted where realcode=code or realcode=code1
while(len(@str)>=1)
begin
if len(@str)>1
set @sql=@sql+' select '''+substring(@str,1,1)+''' union all'
else
set @sql=@sql+' select '''+substring(@str,1,1)+''''
set @str=stuff(@str,1,1,'')
end
set @sql='insert b(perchar) '+@sql
--print @sql
exec(@sql)
endgo--测试一行数据
insert a(code,code1,realcode) select '37011273816524X','37011273816524*','37011273816524*'
--测试多行行数据
insert a(code,code1,realcode)
select '37011273816524X','37011273816524*','37011273816524*' union
select '123412738165243','37011273816524*','37011273816524*'--查看
select * from b--删除测试环境
drop table a
drop table b--结果
/*id perchar type
----------- ---------- -----------
1 3 1
2 7 1
3 0 1
4 1 1
5 1 1
6 2 1
7 7 1
8 3 1
9 8 1
10 1 1
11 6 1
12 5 1
13 2 1
14 4 1
15 X 1
16 1 1
17 2 1
18 3 1
19 4 1
20 1 1
21 2 1
22 7 1
23 3 1
24 8 1
25 1 1
26 6 1
27 5 1
28 2 1
29 4 1
30 3 1
31 3 1
32 7 1
33 0 1
34 1 1
35 1 1
36 2 1
37 7 1
38 3 1
39 8 1
40 1 1
41 6 1
42 5 1
43 2 1
44 4 1
45 X 1
*/