SQL Server中trigger和变量的使用笔记

scs653298

--用trigger插入主键的值
--通过时间变量来决定主键的值
create trigger ss on test
instead of insert --在插入之前执行此trigger
as
begin
declare @index varchar(30)--声明变量
waitfor delay '00:00:00.010'--延时0.01秒执行下一条指令
set @index = replace(replace(replace(replace(convert(varchar(23),getdate(),121),'-',''),' ',''),':',''),'.','') --将当前时间转化为字符串
insert into test
select @index,i.SNAME,i.STATUS,i.CITY
from inserted i
end
insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');
insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');
insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');
insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');
insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');
insert into test(SNAME,STATUS,CITY) values ('11',30,'ss');
drop trigger ss;
delete from test
select * from test

--测试变量的用法
declare @a numeric
set @a = cast(replace(replace(replace(replace(convert(varchar(23),getdate(),121),'-',''),' ',''),':',''),'.','') as numeric)
print @a
print convert(varchar,(@a +1))
--另一种方法使用变量
declare @index varchar(30)--声明变量
set @index = replace(replace(replace(replace(convert(varchar(23),getdate(),121),'-',''),' ',''),':',''),'.','') --将当前时间转化为字符串
print @index