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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
BrandonGalderisiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.