CODEWITHSUNDEEP
sql-servert-sqltriggers
mohamedelhawary
mohamedelhawary

Reputation: 113

After Update Trigger Insert old records to another table

I have MedicalAllowance Table i want to make vesrion from this table as history table "MedicalAllowanceHistory" i want to create trigger to do that after update MedicalAllowance Table copy data to "MedicalAllowanceHistory" table .which way is better to do that .

MedicalAllowance table 

ID | Employee_ID | Limit | Used | Balance

MedicalAllowanceHistory table 

ID | Employee_ID | Limit | Used | Balance

Upvotes: 2

Views: 3609

Answers (3)

Kesava Moorthi
Kesava Moorthi

Reputation: 59

You can use the following example to solve your problem:

CREATE TRIGGER tg_MedicalAllowance ON MedicalAllowance
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
  INSERT MedicalAllowanceHistory
  SELECT ID,Employee_ID,Limit,Used,balance
  FROM deleted
END

Upvotes: 0

Mohamad Bahmani
Mohamad Bahmani

Reputation: 98

I suggest you to use "Change Data Capture" instead of "Trigger". In this solution SQL tracks any changes. If you have need to learn more about it,click here.

Upvotes: 0

GuidoG
GuidoG

Reputation: 12059

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

Upvotes: 3

Related Questions