首页 > 数据库 > SQL Server >

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

2012-05-17

SQL Server中trigger和变量的使用笔记[sql]--用trigger插入主键的值 --通过时间变量来决定主键的值 create trigger ss on test instead of insert --在插入之前执行此trigger as begin declar...

SQL Server中trigger和变量的使用笔记
[sql]
--用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 www.2cto.com
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
www.2cto.com
--测试变量的用法
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
作者 diyoosjtu
相关文章
最新文章
热点推荐