如何在SQL Server 2005中使用触发器进行更新 [英] How can use Trigger for Update in SQL Server 2005

问题描述

亲爱的所有人，

如何在SQL Server 2005中使用更新触发器来维护跟踪?

我创建了两个表&触发更新.如果更新tbl_master表，我不想保持与tbl_audit_master表中存在的值相同.

例如，如果我用相同的值两次更新表tbl_master.

Qry:

Dear All,

How can I use Update Trigger in SQL Server 2005 to maintain the tracing?

I created two tables & trigger for Update. I don''t want to maintain same values that existing in the tbl_audit_master table if I update tbl_master table.

For Example, If I update the Table tbl_master two times with the same values.

Qry:

update tbl_Master set Emp_Name='Test', Designation='Electrical Engineer' where Emp_ID='1'

------------------------------------
Emp_Id  Emp_Name      Designation 
------------------------------------
1	    Test	      Electrical Engineer
1	    Test	      Electrical Engineer
------------------------------------

--Master Table---
create table tbl_Master (Emp_ID numeric(12,0), Emp_Name varchar(50), Designation varchar(25));
--Audit Table---
create table tbl_Audit_Master (Emp_ID numeric(12,0), Emp_Name varchar(50), Designation varchar(25));
---------------------------------------------------

--Trigger---
CREATE TRIGGER trg_UpdateTbl

ON DBO.tbl_Master
After update
As 
begin
set nocount on;
insert dbo.tbl_Audit_Master
select Emp_ID, Emp_Name, Designation from inserted
end
Go

----------------
insert into tbl_Master values('1','XYZ','SE');
insert into tbl_Master values('2','TEST','SSE');
update tbl_Master set Emp_Name='Test', Designation='Electrical Engineer' where Emp_ID='1'

推荐答案

首先，如果正在审核，我个人会在每次修改(包括必要的时间戳)时添加一行.

但是对于您的问题，您也可以使用MERGE作为补充.例如:

First, personally if this is auditing I would add a row whenever a modification is done including necessary timestamps.

But to your question, you can also use MERGE for the upsert. For example something like:

MERGE tbl_Audit_Master AS target
USING (SELECT i.Emp_ID, i.Emp_Name, i.Designation 
       from inserted i, tbl_Audit_Master tam
       where 
       ) AS source (Emp_ID, Emp_Name, Designation)
ON (target.emp_id = source.emp_id 
    and target.emp_name = source.emp_name
    and target.designation = source.designation)
WHEN NOT MATCHED BY TARGET THEN 
         INSERT (Emp_ID, Emp_Name, Designation) 
         VALUES (Emp_ID, Emp_Name, Designation);
GO

我认为以下内容对您有帮助

I Think following will be helpful to you

CREATE TRIGGER trg_UpdateTbl
 
ON DBO.tbl_Master
After update
As 
begin
 Declare @Emp_ID int
 Declare @Emp_Name varchar(50)
 Declare @Designation varchar(50)
 set @Emp_ID=(select Emp_ID from inserted)
 set @Emp_Name=(select Emp_Name from inserted)
 set @Designation=(select Designation from inserted)
 if Not Exists(Select * from tbl_Audit_Master where Emp_Name<>@Emp_Name And      Designation<>@Designation And Emp_ID<>Emp_ID)
 Begin
  set nocount on;
  insert dbo.tbl_Audit_Master
  select Emp_ID, Emp_Name, Designation from inserted
 end
end
Go

这篇关于如何在SQL Server 2005中使用触发器进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！