try:
------------------------------------------------------------------------
Create Trigger TR_GoodsBasicInfo
On BM_GoodsBasicinfo
For Insert,Update,Delete
As
Begin
If exists(select 1 from inserted) and exists(select 1 from deleted)
Begin
IF Update(GBI_Deleted) And (Select GBI_Deleted From inserted)=1
--删除(台站删除是设置GBI_Deleted=1的标志)
Begin
Insert Into sc_materiallog (pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicinfo','2','0' From inserted Where GBI_Deleted=1
End
Else
--修改
Begin
IF (Update(GBI_GCode) Or Update(GBI_GForName) Or Update(GBI_GName) Or Update(GBI_GRe)
Or Update(GBI_GType) Or Update(GBI_GStandard) Or Update(GBI_Gunit) Or Update(GBI_Deleted))
Begin
Insert Into sc_materiallog(pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicinfo','1','0' From inserted
End
End
End
ELSE
Begin
Insert Into sc_materiallog (pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicinfo','0','0' From inserted
Insert Into sc_materiallog (pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicInfo','2','0' From deleted
End
End;
------------------------------------------------------------------------
Create Trigger TR_GoodsBasicInfo
On BM_GoodsBasicinfo
For Insert,Update,Delete
As
Begin
If exists(select 1 from inserted) and exists(select 1 from deleted)
Begin
IF Update(GBI_Deleted) And (Select GBI_Deleted From inserted)=1
--删除(台站删除是设置GBI_Deleted=1的标志)
Begin
Insert Into sc_materiallog (pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicinfo','2','0' From inserted Where GBI_Deleted=1
End
Else
--修改
Begin
IF (Update(GBI_GCode) Or Update(GBI_GForName) Or Update(GBI_GName) Or Update(GBI_GRe)
Or Update(GBI_GType) Or Update(GBI_GStandard) Or Update(GBI_Gunit) Or Update(GBI_Deleted))
Begin
Insert Into sc_materiallog(pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicinfo','1','0' From inserted
End
End
End
ELSE
Begin
Insert Into sc_materiallog (pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicinfo','0','0' From inserted
Insert Into sc_materiallog (pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicInfo','2','0' From deleted
End
End;
Create Trigger TR_GoodsBasicInfo_Upd On BM_GoodsBasicinfo
For insert,Update,delete
As
if exists(select 1 from inserted) and exists(select 1 from deleted)
begin /*这里放更新触发器代码*/
.....
end
else
if exists(select 1 from inserted)
begin /*这里放插入触发器代码*/
......
end
else
begin /*这里放删除触发器代码*/
.......
endgo
For Update,insert,delete
If exists(select * from inserted) and exists(select * from deleted)-----update
begin
IF Update(GBI_Deleted) And (Select GBI_Deleted From inserted)=1
--删除(台站删除是设置GBI_Deleted=1的标志)
Begin
Insert Into sc_materiallog (pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicinfo','2','0' From inserted Where GBI_Deleted=1
End
Else
--修改
IF (Update(GBI_GCode) Or Update(GBI_GForName) Or Update(GBI_GName) Or Update(GBI_GRe)
Or Update(GBI_GType) Or Update(GBI_GStandard) Or Update(GBI_Gunit) Or Update(GBI_Deleted))
Begin
Insert Into sc_materiallog(pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicinfo','1','0' From inserted
End
end
If exists(select * from inserted) and not exists(select * from deleted)-----insert
begin
Insert Into sc_materiallog (pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicinfo','0','0' From inserted
end
If not exists(select * from inserted) and exists(select * from deleted) -----delete
begin
Insert Into sc_materiallog (pk,tablename,flag,status)
Select GBI_ID,'BM_GoodsBasicInfo','2','0' From deleted
end