Click here to Skip to main content
15,889,216 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:
SQL
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
------------------------------------

SQL
--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'
Posted
Updated 2-Jan-12 3:22am
v3

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:
SQL
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
 
Share this answer
 
Comments
Espen Harlinn 2-Jan-12 19:08pm    
I agree - if you can don't overwrite existing data, after all somebody made an effort to put the original data into the db.
Wendelius 3-Jan-12 0:25am    
That's true :)
I Think following will be helpful to you
SQL
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
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900