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

--用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