asked on

Update, INsert Trigger with fieldname etc

I got some help here getting my first trigger together only to discover it is not what is required. I now need a trigger that will insert into one table only the EMpID, fieldname of the field that is changed (one row for each field name updated or added), ChangedFrom, ChangedTO, User who made the change. How do I modify the code I had below to make that happen.
I am a newby here.
CREATE TRIGGER trgEdit ON tblEmpInfo
FOR UPDATE
AS
if @@rowcount=0
return

-- Audit OLD record.
INSERT INTO tblEMSHistory
( EmpID, empStatus ,Supervisor, Department ,Location)
SELECT
del.empId,
case when del.empStatus <> ins.empStatus then del.empstatus else null end,
case when del.Supervisor <> ins.Supervisor then del.Supervisor else null end,
case when del.Department <> ins.Department then del.Department else null end,
case when del.Location <> ins.Location then del.Location else null end
FROM deleted del
join inserted ins
on ins.empID = del.empID -- asusming empID is the PK and that the PK will NEVER be updated.

where ins.empstatus<>del.empstatus
or ins.supervisor <> del.supervisor
or ins.department <> del.department
or ins.location <> del.location
--
-- list all other columns here and add them to the insert above
--

BrandonGalderisi

This won't be very efficient

CREATE TRIGGER trgEdit ON tblEmpInfo
FOR  UPDATE
AS
if @@rowcount=0
  return
 
declare @empID int
 
declare @i_empStatus  varchar(32),
@i_Supervisor varchar(32), 
@i_Department  varchar(32),
@i_Location varchar(32),
@d_empStatus  varchar(32),
@d_Supervisor varchar(32), 
@d_Department  varchar(32),
@d_Location varchar(32)
 
declare ptCUR cursor for
select i.empID 
i.empStatus ,i.Supervisor, i.Department ,i.Location
d.empStatus ,d.Supervisor, d.Department ,d.Location
from inserted i
join deleted d
on i.empID = d.empID
where i.empstatus<>d.empstatus
or i.supervisor <> d.supervisor
or i.department <> d.department
or i.location <> d.location
 
open ptCUR
fetch next from ptCUR into @empID,@i_empStatus  ,@i_Supervisor ,@i_Department ,@i_Location,@d_empStatus,@d_Supervisor,@d_Department ,@d_Location
while @@Fetch_Status=0
begin
 
if @i_empStatus  <> @d_empStatus
 insert tblEMSHistory (EmpId, Field,'EmpStatus', @i_EmpStatus
 
if @i_Supervisor <> @d_Supervisor 
 insert tblEMSHistory (EmpId, Field,'Supervisor', @i_Supervisor 
 
if @i_Department<> @d_Department
 insert tblEMSHistory (EmpId, Field,'Department', @i_Department
 
if @i_Location<> @d_Location
 insert tblEMSHistory (EmpId, Field,'Location', @i_Location
 
 
fetch next from ptCUR into @empID,@i_empStatus  ,@i_Supervisor ,@i_Department ,@i_Location,@d_empStatus,@d_Supervisor,@d_Department 
end
close ptCUR
deallocate ptCUR
-- Audit OLD record.
INSERT INTO tblEMSHistory
( EmpID, empStatus ,Supervisor, Department ,Location)
  SELECT  
del.empId,
case when del.empStatus <> ins.empStatus then del.empstatus else null end,
case when del.Supervisor <> ins.Supervisor then del.Supervisor else null end,
case when del.Department <> ins.Department then del.Department else null end,
case when del.Location <> ins.Location then del.Location else null end
   FROM deleted del
   join inserted ins
     on ins.empID = del.empID -- asusming empID is the PK and that the PK will NEVER be updated.

Open in new window

krbnldy

ASKER

I am trying to process the code above and getting several syntax errors, example. line36, 39, 42, 45 and near the keyword in. Trying to see what is missing

BrandonGalderisi

Sorry....

forgot to remove the extra stuff I copied in. Try this now. It should be much closer

CREATE TRIGGER trgEdit ON tblEmpInfo
FOR  UPDATE
AS
if @@rowcount=0
  return
 
declare @empID int
 
declare @i_empStatus  varchar(32),
@i_Supervisor varchar(32), 
@i_Department  varchar(32),
@i_Location varchar(32),
@d_empStatus  varchar(32),
@d_Supervisor varchar(32), 
@d_Department  varchar(32),
@d_Location varchar(32)
 
declare ptCUR cursor for
select i.empID 
i.empStatus ,i.Supervisor, i.Department ,i.Location
d.empStatus ,d.Supervisor, d.Department ,d.Location
from inserted i
join deleted d
on i.empID = d.empID
where i.empstatus<>d.empstatus
or i.supervisor <> d.supervisor
or i.department <> d.department
or i.location <> d.location
 
open ptCUR
fetch next from ptCUR into @empID,@i_empStatus  ,@i_Supervisor ,@i_Department ,@i_Location,@d_empStatus,@d_Supervisor,@d_Department ,@d_Location
while @@Fetch_Status=0
begin
 
if @i_empStatus  <> @d_empStatus
 insert tblEMSHistory (EmpId, Field,NewValue)
select @empId'EmpStatus', @i_EmpStatus
 
if @i_Supervisor <> @d_Supervisor 
 insert tblEMSHistory (EmpId, Field,NewValue)
select @empId,'Supervisor', @i_Supervisor 
 
if @i_Department<> @d_Department
 insert tblEMSHistory (EmpId, Field,NewValue)
select @empId,'Department', @i_Department
 
if @i_Location<> @d_Location
 insert tblEMSHistory EmpId, Field,NewValue)
select @empId,'Location', @i_Location
 
 
fetch next from ptCUR into @empID,@i_empStatus  ,@i_Supervisor ,@i_Department ,@i_Location,@d_empStatus,@d_Supervisor,@d_Department 
end
close ptCUR
deallocate ptCUR

Open in new window

krbnldy

ASKER

I added all the fields I needed to modify however 2 things I'm missing. How do I pass the username to a trigger and at what point do I loop through so that more than one field cahnge can be updated each time?
I am leaving for the day, but will check back tomorrow. You have been a great help so far.

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

Note that if you are using the default SQL Case INSENSITIVE mode that you will not get an audit trail for a change in case. So to this above query, "hello" = "HELLO" so it would not audit that. If you need to audit change in case you must cast the inserted and deleted values as a datatype that will view the difference. Like varbinary(16)

ex. (and cast(i.location as varbinary(16)) <> cast(d.location as varbinary(16)))

dportas

Example follows. Your design implies that all data values (possibly of different types?) are combined in one column. Auditing like this will not only be resource intensive but also much harder to query and make use of than the data in its original form. A better alternative in my opinion would be to create table(s) that preserve the history of the changed rows in a structure that mirrors your original table.

CREATE TRIGGER trg ON tbl FOR UPDATE
AS
BEGIN

      INSERT INTO Audit (KeyCol, dt, ColName, ColValue)
      SELECT i.KeyCol, CURRENT_TIMESTAMP, 'col1', i.col1
      FROM inserted i
      JOIN deleted d
      ON i.KeyCol = d.KeyCol AND i.col1<>d.col1
      UNION ALL
      SELECT i.KeyCol, CURRENT_TIMESTAMP, 'col2', i.col2
      FROM inserted i
      JOIN deleted d
      ON i.KeyCol = d.KeyCol AND i.col2<>d.col2
      UNION ALL
      SELECT i.KeyCol, CURRENT_TIMESTAMP, 'col3', i.col3
      FROM inserted i
      JOIN deleted d
      ON i.KeyCol = d.KeyCol AND i.col3<>d.col3;

END

BrandonGalderisi

dportas:

I worked with him/(her) earlier to create the auditing trigger to do just that. But (s)he is being told that this is how it has to be done.