我创建了一个表,tblAddress
并且想使用触发器。当我INSERT/DELETE
或时UPDATE
,这些更改应保存在另一个表中-锁定表中。我是SQL Server的初学者,可以为您解决此问题吗?
我只是做了那么多。我要在其中存储触发器信息的另一个表名为tr_Lock
。
Create Trigger tr_tblAddress_ForInsertDeleteUpdate
On tblAddress
After Insert,Delete,Update
As
Print 'You Deleted, Inserted, Updated one row from tblAddress';
每个语句触发一次触发,而不是每行触发一次。触发者可以访问已插入,已删除的表,您可以根据需要使用它们。
插入后可以访问新插入的行,删除后可以访问删除的行,更新后可以访问删除的行以及新更新的行。
请看下面的简单例子
create table triggertest
(
id int,
name char(1)
)
create table log
(
id int,
name char(1),
operationdone char(2),
loggedtime datetime
)
Alter trigger trg_test1
on triggertest
after insert,update,delete
as
begin
set nocount on
--insert updated rows
if exists(select 1 from inserted) and exists(select 1 from deleted)
begin
insert into log
select *,'U',getdate() from inserted
return
end
----insert new rows
if exists(select 1 from inserted)
begin
insert into log
select *,'I',getdate() from inserted
return
end
--insertd deleted rows
if exists(select 1 from deleted)
begin
insert into log
select *,'D',getdate() from deleted
return
end
end
insert into triggertest
select 1,'z'
update triggertest
set
id=2
where name='z'
delete from triggertest
select * from log
select * from triggertest
id name operationdone loggedtime
1 z I 2016-03-08 19:54:23.617
2 z U 2016-03-08 19:54:30.373
2 z U 2016-03-08 19:54:30.373
2 z D 2016-03-08 19:54:39.063
2 z D 2016-03-08 19:54:39.063
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句