1.拿到插入的记录的id,当表有触发器时候就不可以带output
insert into userinfo(name, gender, address, delflag) output inserted.id values ('hhh',1,'jjj',0)
2.创建插入触发器
--当表发生插入操作之后,触发下面的触发器执行
create trigger TR_UserInfo_Insert on userinfo
for insert
as
begin
declare @id int
select @id=id from inserted--inserted是一个表,只有在插入时候才能用inserted.id写法
update userinfo set name=name+CAST(getdate() as nvarchar(32)) where id=@id
end
insert into userinfo(name, gender, address, delflag) values ('556',1,'jjj',0)
3 创建删除触发器
--当表发生删除操作之后,触发下面的触发器执行
create trigger TR_UserInfo_Delete on userinfo
instead of delete
as
begin
declare @id int
select @id=id from deleted
update userinfo set delflag=1 where id=@id
end
delete from userinfo where id=13