触发器 插入更新删除三种操作能放在一起处理吗?

[ponydph]

表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

[ponydph]

多谢，我用的是2000

[cd731107]

一般来说都是分开的，互不相扰，一旦需要维护也比较简便

[oO寒枫Oo]

能 放在一起的话通过deleted inserted这2个表来进行判断
delete deleted有记录 inserted没有
update deleted有 inserted有
insert deleted没有 inserted有


也可以分开了弄

[ponydph]

多谢。。。。。。

[X_0]

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
*/

[ponydph]

多谢， 是否是建立一个临时表的意思？

[X_0]

[Quote=引用 1 楼 x_0 的回复:]
SQL code

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
/*……
[/Quote]
aa是你要存放操作記錄的表

[ponydph]

[Quote=引用 3 楼 x_0 的回复:]
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
……
[/Quote]

上面两个 select 语句 不同 判断条件分开？？？

[-晴天]

create table tb(id int identity(1,1),col varchar(10))
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

[-晴天]

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
--如果要把id插入到另一个表中,则用:
--insert into tb1 select id from inserted 或 deleted
go

[--小F--]

2008可以用merge 自己去查下资料