--自已做标识列的例子--流水号:日期+当日编号: --创建视图,得到当前日期(因为函数中不能使用getdate()) 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 tb
SQL学习----日期流水号生成
最新推荐文章于 2022-10-06 08:33:34 发布