1 if exists(select * from sys.objects where name='test') 2 drop table test 3 go 4 create table test 5 ( 6 id varchar(20), 7 name varchar(20) 8 ) 9 if exists(select * from sys.objects where name='t_insert') 10 drop trigger t_insert 11 go 12 create trigger t_insert on test 13 instead of insert 14 as 15 declare @id varchar(20),@id1 int,@head varchar(20) 16 select * into #tb from inserted 17 set @head='uzi'+convert(varchar(20),getdate(),112) 18 select @id=max(id) from test 19 --if @id is null 20 --set @id1=0 21 --else 22 --set @id1=cast(substring(@id,12,4) as int) 23 if exists(select * from test) 24 set @id1=cast(substring(@id,12,4) as int) 25 else 26 set @id1=0 27 update #tb set @id1=@id1+1,id=@head+right('0000'+cast(@id1 as varchar),4) 28 insert into test select * from #tb 29 go 30 insert into test(name) values('小马') 31 insert into test(name) values('小明') 32 insert into test(name) values('小鱼') 33 34 select * from 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、一样的代码,就差一个范围,效果不相同
1 if exists(select * from sys.objects where name='test') 2 drop table test 3 go 4 create table test 5 ( 6 id varchar(20), 7 name varchar(20) 8 ) 9 if exists(select * from sys.objects where name='t_insert') 10 drop trigger t_insert 11 go 12 create trigger t_insert on test 13 instead of insert 14 as 15 declare @id varchar(20),@id1 int,@head varchar(20) 16 select * into #tb from inserted 17 set @head='uzi'+convert(varchar(20),getdate(),112) 18 select @id=max(id) from test 19 if @id is null 20 set @id1=0 21 else 22 set @id1=cast(substring(@id,12,4) as int) 23 update #tb set @id1=@id1+1,id=@head+right('0000'+cast(@id1 as varchar),4) 24 insert into test select * from #tb 25 go 26 insert into test(name) values('小马') 27 insert into test(name) values('小明') 28 insert into test(name) values('小鱼') 29 30 select * from test
1 if exists(select * from sys.objects where name='test') 2 drop table test 3 go 4 create table test 5 ( 6 id varchar(20), 7 name varchar(20) 8 ) 9 --触发器 10 if exists(select * from sys.objects where name='t_insert') 11 drop trigger t_insert 12 go 13 create trigger t_insert on test 14 instead of insert 15 as 16 declare @id varchar,@id1 int,@head varchar(20) 17 select * into #tb from inserted 18 set @head=convert(varchar(20),getdate(),112) 19 select @id=max(id) from test 20 if @id is null 21 set @id1=0 22 else 23 set @id1=cast(substring(@id,9,4) as int) 24 update #tb set @id1=@id1+1,id=@head+right('0000'+cast(@id1 as varchar),4) 25 insert into test select * from #tb 26 go 27 insert into test(name) values('小马') 28 insert into test(name) values('小白') 29 insert into test(name) values('小黑') 30 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 小黑