以下触发器,不能"insert into t1 (v1) select v1 from t2)"批量插入CREATE trigger t1
on dbo.t1
for insert
as
begin
update t1
set id=(select isnull(max(id),0)
from t1
)+1)
where id is null
end
on dbo.t1
for insert
as
begin
update t1
set id=(select isnull(max(id),0)
from t1
)+1)
where id is null
end
不会,
on dbo.t1
for insert
as
begin
select *,IDENTITY(int,1,1) as Id0001 into # from inserted
update t1
set id=(select isnull(max(id),0)
from t1
)+t.id0001
from t1,# t
where t1.id is null
and t1.唯一字段=t.唯一字段
end
上面的ID我是给它设的一个自定义格式流水号,为了把问题简单化就没贴其它相关信息
create table test(id varchar(18), --流水号,日期(8位)+时间(4位)+流水号(4位)
name varchar(10) --其他字段
)go
--创建生成流水号的触发器
create trigger t_insert on test
INSTEAD OF insert
as
declare @id varchar(18),@id1 int,@head varchar(12)
select * into #tb from inserted
set @head=convert(varchar,getdate(),112)+replace(convert(varchar(5),getdate(),108),':','')
select @id=max(id) from test where id like @head+'%'
if @id is null
set @id1=0
else
set @id1=cast(substring(@id,13,4) as int)
update #tb set @id1=@id1+1
,id=@head+right('0000'+cast(@id1 as varchar),4)
insert into test select * from #tb
go
--插入数据,进行测试
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'--修改系统时间,再插入数据测试一次
insert into test(name)
select 'aa'
union all select 'bb'
union all select 'cc'--显示测试结果
select * from test
--删除测试环境
drop table test/*--测试结果
id name
------------------ ----------
2004022720430001 aa
2004022720430002 bb
2004022720430003 cc
2004022720430004 aa
2004022720430005 bb
2004022720430006 cc(所影响的行数为 6 行)
--*/
create view v_getdate as select dt=convert(varchar,getdate(),112)
go--创建得到最大id的函数
create function f_getid()
returns varchar(12)
as
begin
declare @id varchar(12),@dt varchar(8)
select @dt=dt from v_getdate
select @id=@dt+'-'+right(1001+isnull(right(max(id),3),0),3)
from tb where id like @dt+'-%'
return(@id)
end
go--创建表
create table tb(id varchar(20) default dbo.f_getid() primary key,name varchar(10))
go
/*--创建触发器,在删除表中的记录时,自动更新记录的id
create trigger t_delete on tb
AFTER delete
as
declare @id int,@mid int
select @mid=min(id),@id=@mid-1 from deleted
update tb set id=@id,@id=@id+1 where id>@mid
--*/
go--插入记录测试
insert into tb(name) values('张三')
insert into tb(name) values('张四')
insert into tb(name) values('张五')
insert into tb(name) values('张六')
insert into tb(name) values('张七')
insert into tb(name) values('张八')
insert into tb(name) values('张九')
insert into tb(name) values('张十')--显示插入的结果
select * from tb--删除部分记录
delete from tb where name in('张五','张七','张八','张十')--显示删除后的结果
select * from tbgo
--删除环境
drop table tb
drop view v_getdate
drop function f_getid
create function f_getid(
@类别编号 varchar(3))
returns int
as
begin
declare @re int
select @re=right(id,4) from(
select id=max(材料编号) from tb where 类别编号=@类别编号
) a
set @re=isnull(@re,0)+1
return(@re)
end
go--创建测试表
create table tb(材料编号 varchar(7) primary key default '',类别编号 varchar(3),材料名称 varchar(10))
go--创建触发器,自动生成材料编号
create trigger t_insert on tb
instead of insert
as
select * into #t from inserted order by 类别编号
declare @类别编号 varchar(3),@id int
update #t set @id=case when @类别编号=类别编号 then @id+1 else dbo.f_getid(类别编号) end
,材料编号=类别编号+right('0000'+cast(@id as varchar),4)
,@类别编号=类别编号
insert into tb select * from #t
go--插入数据测试
insert into tb(类别编号,材料名称)
select '101','A材料'
union all select '101','B材料'
union all select '302','C材料'--显示结果
select * from tb order by 材料编号go
--删除测试环境
drop table tb
drop function f_getid
on dbo.t1
for insert
as
begin
declare @i int
set @i = 0
update t1
set id=(select isnull(max(id),0)
from t1
-- )+1)
)+@i),@i = @i + 1 where id is null
end
--换成
declare @i int
set @i = 0
select @i = max(id) from t1
select identity(int,1,1)+@i as id,v1 into # from t2
insert t1(id,v) select id,v1 from #
insert t1(id,v) select id,v1 from #
--换成
select identity(int,1,1) as id,v1 into # from t2
insert t1(id,v) select id+@i,v1 from #