MS SQL Server Trigger help
-
Hi, I have a sql server table with fields EmployeeID, Date, TimeIN, TimeOut, Remarks. Now there is a unique constraint on both EmployeeID and Date such that only one record exists for a particular Employee on a particular date. Now the main application can insert and update records. I need a trigger to track changes such that on insert or update, the values are copied onto a data_log table. Here is the problem, I need the trigger to check if the row exists in data_log for that EmployeeID and Date, then update, else insert. So there should be only one record of each employee per date in the data_log table as well. I just want the most current change and need to overwrite previous log entry. thanks
-
Hi, I have a sql server table with fields EmployeeID, Date, TimeIN, TimeOut, Remarks. Now there is a unique constraint on both EmployeeID and Date such that only one record exists for a particular Employee on a particular date. Now the main application can insert and update records. I need a trigger to track changes such that on insert or update, the values are copied onto a data_log table. Here is the problem, I need the trigger to check if the row exists in data_log for that EmployeeID and Date, then update, else insert. So there should be only one record of each employee per date in the data_log table as well. I just want the most current change and need to overwrite previous log entry. thanks
Just a thought... why are you bothering if all you are doing is copying the values into another table? An audit or log table should contain complete history, I've always thought and no unique keys from the parent table - duplicates are fine - create a compound index or a surrogate key if you need one. Prepared to be called out on this - just seems a bit pointless. In the meantime you could check with:
if exists(select top 1 1 from [table] where [key] = [parameter])
begin
-- insert
end
else
begin
-- update
endor similar: adjust to suit.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
Just a thought... why are you bothering if all you are doing is copying the values into another table? An audit or log table should contain complete history, I've always thought and no unique keys from the parent table - duplicates are fine - create a compound index or a surrogate key if you need one. Prepared to be called out on this - just seems a bit pointless. In the meantime you could check with:
if exists(select top 1 1 from [table] where [key] = [parameter])
begin
-- insert
end
else
begin
-- update
endor similar: adjust to suit.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
I have to run extended operations on bulk records in the destination table. I thought Delete if exists, then Insert would be a possible solution, but the trigger is working some times and failing other times, and when it does fail, it cancels out the transaction on the original table resulting in data loss.
-
Hi, I have a sql server table with fields EmployeeID, Date, TimeIN, TimeOut, Remarks. Now there is a unique constraint on both EmployeeID and Date such that only one record exists for a particular Employee on a particular date. Now the main application can insert and update records. I need a trigger to track changes such that on insert or update, the values are copied onto a data_log table. Here is the problem, I need the trigger to check if the row exists in data_log for that EmployeeID and Date, then update, else insert. So there should be only one record of each employee per date in the data_log table as well. I just want the most current change and need to overwrite previous log entry. thanks
I do not know which version of SQL server you have but if 2008 try MERGE in your trigger.