Trigger insert old values- values that was updated
Here's an example update trigger:
create table Employees (id int identity, Name varchar(50), Password varchar(50))
create table Log (id int identity, EmployeeId int, LogDate datetime,
OldName varchar(50))
go
create trigger Employees_Trigger_Update on Employees
after update
as
insert into Log (EmployeeId, LogDate, OldName)
select id, getdate(), name
from deleted
go
insert into Employees (Name, Password) values ('Zaphoid', '6')
insert into Employees (Name, Password) values ('Beeblebox', '7')
update Employees set Name = 'Ford' where id = 1
select * from Log
This will print:
id EmployeeId LogDate OldName
1 1 2010-07-05 20:11:54.127 Zaphoid
After Update Trigger Insert old records to another table
you can use a trigger for that
But I would also store the date of the event, and the kind of event (insert, update or delete)
Here is an example to get you started
CREATE trigger tr_UID_MedicalAllowance on dbo.MedicalAllowance
after update, insert, delete
as
begin
set nocount on
declare @Insert bit = 0
declare @Update bit = 0
declare @Delete bit = 0
--find out why we where triggered
if (exists(select 1 from inserted)) and (exists(select 1 from deleted))
set @Update = 1
else if (exists(select 1 from inserted))
set @Insert = 1
else if (exists (select 1 from deleted))
set @Delete = 1
if @Update = 1
begin
insert into MedicalAllowanceHistory (
MedicalAllowanceID,
HistoryDate,
HistoryEvent,
other columns...)
select i.MedicalAllowanceID,
getdate(),
'UPDATED',
i.other columns...
from inserted i
end
if @Insert = 1
begin
insert into MedicalAllowanceHistory (
MedicalAllowanceID,
HistoryDate,
HistoryEvent,
other columns...)
select i.MedicalAllowanceID,
getdate(),
'INSERTED',
i.other columns...
from inserted i
end
if @Delete = 1
begin
insert into MedicalAllowanceHistory (
MedicalAllowanceID,
HistoryDate,
HistoryEvent,
other columns...)
select d.MedicalAllowanceID,
getdate(),
'DELETED',
d.other columns...
from deleted d
end
end
It is also possible to store old and new values in case of update, the new values are in the inserted table and the old are in the deleted table.
In that case the update part could look something like this
if @Update = 1
begin
insert into MedicalAllowanceHistory (
MedicalAllowanceID,
HistoryDate,
HistoryEvent,
NewLimit,
OldLimit,
other columns...)
select i.MedicalAllowanceID,
getdate(),
'UPDATED',
i.Limit,
d.Limit,
other columns...
from inserted i
inner join deleted d on i.MedicalAllowanceID = d.MedicalAllowanceID
end
How to compare old value and new value in triggers and then mark as insert/update/delete in audit table
Why you didn't listen to my previous suggestion? You should really add new pair of new/old columns into the audit table, not put everything into value_old/value_new because - as you don't store information what it represents - you'll have to guess (and that's the way to problems).
BTW, what will you do if you - in the same statement - modify both REF_ID and SYS_OTHER? You'll have to check what you did first, and then insert (or not) a row. Too much code for a little benefit.
if :new.ref_id is not null then
INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.ref_id);
elsif :new.sys_other is not null then
INSERT INTO audit_tab (a_id,l_transaction,e_id, sys_name,value_old,value_new)
VALUES (audit_tab_sq.NEXTVAL,'INSERT',:new.e_id,:new.sys_name,NULL,:new.sys_other);
end if;
Track and store old and new value using trigger in MSSQL
Your trigger makes a classic mistake: not taking into account multiple (or zero) rows in the inserted table.
Generally, you should combine INSERT UPDATE and DELETE triggers in one. But here it's a pretty simple trigger, so you can combine them by doing a full join on the inserted and deleted tables. You should join by primary key, I've assumed that is SL_NO
CREATE OR ALTER TRIGGER ToolHistory on [Tool Management]
AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
INSERT PINQC.dbo.Tool_Management_History
(Old_Table_ID, Old_Type, New_Type, Old_Material, New_Material,
Old_Material_Description, New_Material_Description, Old_Tool_Code, New_Tool_Code,
Old_Tool_Description, New_Tool_Description, Old_Tool_Life, New_Tool_Life,
Changed_By, Changed_At, Change_Type)
SELECT
ISNULL(i.SL_NO, d.SL_NO),
d.[Type],
i.[Type],
d.Material,
i.Material,
d.Material_Description,
i.Material_Description,
d.Tool_Code,
i.Tool_Code,
d.Tool_Description,
i.Tool_Description,
d.Tool_Life,
i.Tool_Life,
CURRENT_USER,
GETDATE(),
CASE WHEN d.SL_NO IS NULL THEN 'Inserted' WHEN d.SL_NO IS NULL THEN 'Deleted' ELSE 'Update' END
FROM inserted i
FULL JOIN deleted d ON d.SL_NO = i.SL_NO;
GO
Trigger to insert old values
Something in your description is missing. If you do what you say you're doing, your history table would have only one row and it would have the old data. If you also have a history row that has the new data, either your trigger is doing something other than what you posted or you have something else (i.e. another trigger) that is writing to the history table.
Create the two tables
SQL> ed
Wrote file afiedt.buf
1 create table my_tab (
2 id number,
3 start_date date,
4 end_date date,
5 no number,
6 updated varchar2(1)
7* )
SQL> /
Table created.
SQL> ed
Wrote file afiedt.buf
1 create table my_hist (
2 id number,
3 start_date date,
4 end_date date,
5 no number,
6 updated varchar2(1),
7 update_date date
8* )
SQL> /
Populate the initial data
SQL> ed
Wrote file afiedt.buf
1 insert into my_tab
2* values( 3, date '2014-01-01', date '2014-01-31', 1, 'Y' )
SQL> /
1 row created.
Create the trigger
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE TRIGGER mytrig
2 AFTER UPDATE
3 ON my_tab
4 FOR EACH ROW
5 WHEN (
6 new.updated = 'Y'
7 )
8 BEGIN
9 INSERT INTO my_hist( id,
10 no,
11 start_date,
12 end_date,
13 update_date )
14 VALUES (
15 :old.id,
16 :old.no,
17 :old.start_date,
18 :old.end_date,
19 SYSDATE
20 );
21* END mytrig;
SQL> /
Trigger created.
Now, when I update the row
SQL> update my_tab
2 set end_date = date '2014-03-31'
3 where id = 3;
1 row updated.
there will be only one row in the MY_HIST table and that row will have the old values from the row in MY_TAB
SQL> select * from my_hist;
ID START_DAT END_DATE NO U UPDATE_DA
---------- --------- --------- ---------- - ---------
3 01-JAN-14 31-JAN-14 1 27-MAY-12
If you see two rows, something else is writing the second row. My guess is that you have another trigger defined.
Trigger for rows where specific column was changed
You need to compare the inserted and deleted tables, joining by primary key, and verify the results are different.
Note that the UPDATE() function only tells you if the column was present in the UPDATE, not if the value actually changed.
CREATE TRIGGER testTrigger
ON table
AFTER UPDATE
AS
SET NOCOUNT ON;
IF(UPDATE(KeyProperty))
BEGIN
UPDATE t
SET Flag = True
FROM (
SELECT i.Id, i.KeyProperty
FROM inserted i
EXCEPT
SELECT d.Id, d.KeyProperty
FROM deleted d
) i
JOIN table t ON t.Id = i.Id
END
GO
Related Topics
Why Even Use *Db.Exec() or Prepared Statements in Golang
SQL Server Bitwise Processing Like C# Enum Flags
Get the Nearest Longitude and Latitude from Mssql Database Table
Why Isn't Row Level Security Enabled for Postgres Views
How to Take Sum of Column with Same Id in SQL
Using Multiple Joins. Sum() Producing Wrong Value
Postgresql Query to Detect Overlapping Time Ranges
Select Distinct Values from 1 Column
Want to Display 12 Months Name from SQL Server
Pyspark: Filter Dataframe Based on Multiple Conditions
How to Sort a Linked List in SQL
How to Correctly Do Upsert in Postgres 9.5
How to Find the Average Time Difference Between Rows in a Table