1、
if exists(select * from sys.objects where name='test')
drop table test
go
create table test
(
id varchar(),
name varchar()
)
if exists(select * from sys.objects where name='t_insert')
drop trigger t_insert
go
create trigger t_insert on test
instead of insert
as
declare @id varchar(),@id1 int,@head varchar()
select * into #tb from inserted
set @head='uzi'+convert(varchar(),getdate(),)
select @id=max(id) from test
--if @id is null
--set @id1=
--else
--set @id1=cast(substring(@id,,) as int)
if exists(select * from test)
set @id1=cast(substring(@id,,) as int)
else
set @id1=
update #tb set @id1=@id1+,id=@head+right(''+cast(@id1 as varchar),)
insert into test select * from #tb
go
insert into test(name) values('小马')
insert into test(name) values('小明')
insert into test(name) values('小鱼') select * from test
表test
if exists(select * from test)
select ‘0’ ————————————————-
else — 表中有数据时为: 0 | 表中无数据时为: 1 —
select ‘1’ ————————————————-
if exists(select count(*) from test)
select ‘0’ ————————————————–
else — 表中有数据时为: 1 | 表中无数据时为: 0 —
select ‘1’ ————————————————–
丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄丄
【判断表中是否有数据】
丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅丅
–使用局部变量
–select @id=max(id) from test
–if @id is null
–set @id1=0
–else
–set @id1=cast(substring(@id,12,4) as int)
2、一样的代码，就差一个范围，效果不相同
if exists(select * from sys.objects where name='test')
drop table test
go
create table test
(
id varchar(),
name varchar()
)
if exists(select * from sys.objects where name='t_insert')
drop trigger t_insert
go
create trigger t_insert on test
instead of insert
as
declare @id varchar(),@id1 int,@head varchar()
select * into #tb from inserted
set @head='uzi'+convert(varchar(),getdate(),)
select @id=max(id) from test
if @id is null
set @id1=
else
set @id1=cast(substring(@id,,) as int)
update #tb set @id1=@id1+,id=@head+right(''+cast(@id1 as varchar),)
insert into test select * from #tb
go
insert into test(name) values('小马')
insert into test(name) values('小明')
insert into test(name) values('小鱼') select * from test
if exists(select * from sys.objects where name='test')
drop table test
go
create table test
(
id varchar(),
name varchar()
)
--触发器
if exists(select * from sys.objects where name='t_insert')
drop trigger t_insert
go
create trigger t_insert on test
instead of insert
as
declare @id varchar,@id1 int,@head varchar()
select * into #tb from inserted
set @head=convert(varchar(),getdate(),)
select @id=max(id) from test
if @id is null
set @id1=
else
set @id1=cast(substring(@id,,) as int)
update #tb set @id1=@id1+,id=@head+right(''+cast(@id1 as varchar),)
insert into test select * from #tb
go
insert into test(name) values('小马')
insert into test(name) values('小白')
insert into test(name) values('小黑')
select * from test
给变量添加类型后规定范围(第2个16行代码处)
(a)declare @id varchar,@x int,@head varchar(20)
效果为：
201606140001 小黄
201606140001 小白
201606140001 小黑
（b）declare @id varchar(20),@x int,@head varchar(20)
效果为：
201606140001 小黄
201606140002 小白
201606140003 小黑
（c）declare @id varchar(20),@x int,@head varchar
效果为：
20001 小黄
20001 小白
20001 小黑
