表Test
Id int 主键
Item1 varchar(50)
Item2 varchar(50)
Item3 varchar(50)
我想在这个表上建立触发器,当操作表的的时候,记录下被操作的ID
请问能否将三个触发器建立在一起??,还是必须分开操作,才能得到表的Id??
CREATE TRIGGER triggerOper ON Test
FOR INSERT, UPDATE, DELETE
AS
begin
....................//记录下 被操作记录的ID
end
Id int 主键
Item1 varchar(50)
Item2 varchar(50)
Item3 varchar(50)
我想在这个表上建立触发器,当操作表的的时候,记录下被操作的ID
请问能否将三个触发器建立在一起??,还是必须分开操作,才能得到表的Id??
CREATE TRIGGER triggerOper ON Test
FOR INSERT, UPDATE, DELETE
AS
begin
....................//记录下 被操作记录的ID
end
CREATE TRIGGER triggerOper ON Test
FOR INSERT, UPDATE, DELETE
AS
begin
insert into aa(id)
select id from deleted insert into aa(id)
select id from inserted
end
/*
update時會將舊的和新的都記錄下來,如果只需記錄舊的就用
if exists(select 1 from deleted)
begin
insert into aa(id)
select id from deleted
end
else
begin
insert into aa(id)
select id from inserted
end
*/
上面两个 select 语句 不同 判断条件分开???
go
create trigger getid
on tb
for insert,update,delete
as
if exists(select 1 from inserted)
select id from inserted
else
select id from deleted
go
insert into tb select 'aa'
insert into tb select 'bb'
update tb set col='cc' where id=2
delete tb where id=1
/*
id
-----------
1(1 行受影响)
(1 行受影响)
id
-----------
2(1 行受影响)
(1 行受影响)
id
-----------
2(1 行受影响)
(1 行受影响)
id
-----------
1(1 行受影响)
*/
go
drop table tb
on tb
for insert,update,delete
as
if exists(select 1 from inserted) --优先查找是否为插入或更新操作
select id from inserted
else --剩下删除操作
select id from deleted
--如果要把id插入到另一个表中,则用:
--insert into tb1 select id from inserted 或 deleted
go
delete deleted有记录 inserted没有
update deleted有 inserted有
insert deleted没有 inserted有
也可以分开了弄