触发器中如何判断那些记录是Insert or Update or Delete 所影响的记录？

[xpilot]

我想做一个通用触发器，在当前biao01表执行Insert/update/delete时，都判断执行，并向另一个表biao01_tmp中插入一条记录，
1.如biao01插入一条记录，则在biao01_tmp中插入一条记录，同时OptType=1
2.如biao01修改一条记录，则在biao01_tmp中插入一条记录，同时OptType=2
3.如biao01删除一条记录，则在biao01_tmp中插入一条记录，同时OptType=3
我写的触发器如下，但是执行时只有insert有效，update和delete时都有问题。
(由于这个触发器使用T-SQL,在Sqlanywhere中来写,多少还是和Sqlserver有点微小的差异,写起来实在有点别扭)

Create trigger dba.CommonToTmp on dba.biao01 for insert,delete,update
as
begin
declare @errorcode integer
declare @tab_name varchar(50)
select @errorcode=@@error
begin transaction
-- ***
if(@errorcode = 0)
begin
-- 获得触发器宿主源名(表名)
select @tab_name = table_name from systable where table_id = (select table_id from
systrigger where
trigger_name = 'CommonToTmp')
select @errorcode=@@error
end
-- ***
-- 判断是否存在,只在Inserted有记录而deleted中没有的记录,即，判断是否是Insert操作
if exists(select* from inserted where not UnitCoding = any(select UnitCoding from deleted))
begin
if(@errorcode = 0)
begin
insert into dba.biao01_tmp(UnitCoding,OptType,ObjName)
select UnitCoding,1,@tab_name from
inserted where not UnitCoding = any(select UnitCoding from deleted)
select @errorcode=@@error
end
end
-- ***
-- 判断是否存在,既在Inserted有记录又在deleted中有的记录,即，判断是否是update操作
if exists(select* from inserted as i join deleted as d on i.UnitCoding = d.UnitCoding)
begin
if(@errorcode = 0)
begin
insert into dba.biao01_tmp(UnitCoding,OptType,ObjName)
select i.UnitCoding,2,@tab_name from
deleted as d join inserted as i on d.UnitCoding = i.UnitCoding
select @errorcode=@@error
end
end
-- ***
-- 判断是否存在,只在deleted有记录而inserted中没有的记录,即，判断是否是deleted操作
if exists(select* from deleted where not UnitCoding = any(select UnitCoding from inserted))
begin
if(@errorcode = 0)
begin
insert into dba.biao01_tmp(UnitCoding,OptType,ObjName)
select UnitCoding,3,@tab_name from
deleted where not UnitCoding = any(select UnitCoding from inserted)
select @errorcode=@@error
end
end
-- ***
if(@errorcode = 0)
begin
commit transaction
end
else
begin
rollback transaction
end
end

[xpilot]

to weixiao51(三土)
老兄你没看清我的问题啊!
我要一次for insert,update,delete
然后在触发器中用sql语句来判断是那种触发行为。

[yelook]

不要一下子就for insert,update,delete
写成三个触发器
Create trigger dba.biao01_insert on dba.biao01 for insert
Create trigger dba.biao01_update on dba.biao01 for update
Create trigger dba.biao01_delete on dba.biao01 for delete

[xpilot]

分别写3个触发器我已经写好了，可是我这个触发器有10多个表要用，如果每个表都写3个，以后管理起来太多了，所以我才想要合并起来写一个。那么我10个表也只要这样的10个触发器就可以了，对以后维护工作量应该减小不少。

[weixiao51]

Create trigger dba.biao01_insert on dba.biao01 for []
在for后面跟的是什么单词，就触发什么事件
insert 当插入时触发事件
update 当更新时触发事件
delete 当删除时触发事件

[pacificlh]

关注，我只知道在Oracle中可以进行判断，IF INSERTING THEN //是插入操作
UPDATING //是更新
deleting是//删除
找到，给我一个[e-mail]

[HawaiiLeo]

CREATE TRIGGER TABLECHANGED ON dbo.TableName
FOR INSERT,UPDATE,DELETE
AS
BEGIN
DECLATE @i int,@j int
SELECT @i = COUNT(*) FROM INSERTED
SELECT @i = COUNT(*) FROM DELETED
IF @i = 0 --delete
.................
IF @j = 0 --insert
.................
IF @i<>0 AND @j<>0 --update
................
END

[linghuchonglzq2000]

收藏ing...