Creating an INSERT, UPDATE Trigger

I have the following trigger but the table the Insert is not being performed on the tblEMSHistory.  The field I changed in tblEmpInfo was changed.  ANy suggestions on what I need to modify

CREATE TRIGGER trgEdit ON tblEmpInfo
FOR  UPDATE
AS
     IF (COLUMNS_UPDATED() & 14) > 0
    BEGIN
    -- Audit OLD record.
    INSERT INTO tblEMSHistory
        ( EmpID, empStatus ,Supervisor, Department ,Location)
        SELECT  
               del.EMpID, del.empStatus, del.Supervisor, del.Department, del.Location
            FROM deleted del
       END
krbnldyAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
Well if you only want to insert the changed values, you have to ensure that your destination table allows nulls.  Then wrap each field insert in a case statement.
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
--

Open in new window

0
 
BrandonGalderisiCommented:
Your bitwise operator will only update if colums 2, 3 and 4 are ALL updated.  If only one is updated, the insert won't happen.
0
 
BrandonGalderisiCommented:
This is directly from BOL...


To test for updates or inserts to specific columns, follow the syntax with a bitwise operator and an integer bitmask of the columns being tested. For example, table t1 contains columns C1, C2, C3, C4, and C5. To verify that columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), follow the syntax with & 14. To test whether only column C2 is updated, specify & 2.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
krbnldyAuthor Commented:
I am trying to do the insert if any column is updated.  I have never done this before. Can you help me find modify the trigger to make this happen.
0
 
BrandonGalderisiCommented:
if updated(ColumnName) or updated(ColumnName).......
0
 
BrandonGalderisiCommented:
I personally am in favor of tracking new inserts instead of old updates.  So instead of logging when the item in the tblEmpInfo table gets updated, I would log as the new values get inserted.
CREATE TRIGGER trgEdit ON tblEmpInfo
FOR  UPDATE
AS
     IF updated(EmpID) or updated(empStatus) or updated(Supervisor) or updated(Department) or updated(Location)
    BEGIN
    -- Audit OLD record.
    INSERT INTO tblEMSHistory
        ( EmpID, empStatus ,Supervisor, Department ,Location)
        SELECT  
               del.EMpID, del.empStatus, del.Supervisor, del.Department, del.Location
            FROM deleted del
       END

Open in new window

0
 
krbnldyAuthor Commented:
I have numerous columns and  anyone of them may be changed by the user.  So I'd like the insert to be done whenever any changes is done to the table.  I have no idea which one they can change but would like to create a table that does an audit of any changes made to that table. Because I have never written a trigger before could you modify my original code to help me understand what I need to do.  

I am lost on this one
0
 
BrandonGalderisiCommented:
I assume you only care about updates to the EmpId, empStatus,Supervisor and/or location column since that is all you are tracking changes to.  The trigger that I wrote will log if any of those columns change.
0
 
krbnldyAuthor Commented:
not really,  I was only using those 3 as a test to see if it would work,  I will include all the other columns once I get it working
0
 
krbnldyAuthor Commented:
Using your code I get the error " updated is not a recognized function name"   I am using SQL 2000
0
 
BrandonGalderisiCommented:
Well.... I would do something like this....

The check on ins.COLUMN <> del.COLUMN is to prevent logging of the following statement as updated.


update tblEmpInfo
set empStatus=empStatus

Without the ins. <> del. a change like above, where no values change, will result in logging.
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,del.empStatus,del.Supervisor,del.Department,del.Location
   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
--

Open in new window

0
 
krbnldyAuthor Commented:
What if I only want the field(s) that was changed to be inserted into the table along with the empID?
0
 
krbnldyAuthor Commented:
one last thing,  IF i wanted to get the name of the field that was changed so that I can add a field that named "fldName"  and collect the field name that was changed how do I do that considering there can be more than one field name that was changed.
0
 
BrandonGalderisiCommented:
If you intend to track changed fields in a format where each field changed records a row in the table, I would try my best to deter you from doing that.  The presence of a value in the auditing table indicates that the value for that field changed.
0
 
krbnldyAuthor Commented:
that is what i thought but I was asked to store that field name which i thought was redundant,
0
 
BrandonGalderisiCommented:
The field name would be required if you were logging in a (fieldName, oldValue, newValue) format.  That way would be EXTREMELY inefficient and cause a lot of overhead because you would have to cursor through each field in the inserted/deleted tables and then write inserts for each column.
0
 
krbnldyAuthor Commented:
ok, thank you, I'll suggest that i omit that.  YOu have been a great help

THank you
0
All Courses

From novice to tech pro — start learning today.