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

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.atcpu.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.atcpu.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