After Update Trigger Insert old records to another table

Question

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

please explaine what is your point to do that? Do you wanna do that to log "MedicalAllowanceHistory" or any usage else? — Mohamad Bahmani, Sep 27 '18 at 12:57
I would tell you to implement using bests pratices to audit and trail tables but I don't know your requirements nor your SQL Server version. However you should look this link: https://stackoverflow.com/questions/17546601/best-way-to-implement-an-audit-trail-in-sql-server — Andrew Paes, Sep 27 '18 at 12:58
I would suggest having more details than just the old values. Otherwise you won't ever be able to work out the change of values over time. — Thom A, Sep 27 '18 at 12:59
At a minimum it probably makes sense to also log the date/time of the change and the user, to the extent it can be determined, responsible. Having a bag of history rows with no order isn't very useful for most applications. — HABO, Sep 27 '18 at 14:31

GuidoG · Accepted Answer · 2018-09-27T13:18:01.980

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

score 0 · Answer 2 · edited Sep 27 '18 at 22:49

0

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

edited Sep 27 '18 at 22:49

Grant Miller

27,532
16
147
165

answered Sep 27 '18 at 12:57

Kesava Moorthi

59
7

1

How will that work on Updates? That's an `INSERT` trigger. – Thom A Sep 27 '18 at 12:57
1

That doesn't fix the issue. `inserted` is the **new** data, so you just have 2 copies of the latest version (this the initial version would be lost). It also doesn't help for traceability just copying the columns and not adding additional metadata. – Thom A Sep 27 '18 at 13:06
I changed `inserted` to `deleted`, and added `AFTER INSERT, UPDATE, DELETE`, as I believe this was the intent Kesava was going for. – digital.aaron Sep 27 '18 at 19:36

score 0 · Answer 3 · answered Sep 27 '18 at 13:27

0

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.

answered Sep 27 '18 at 13:27

Mohamad Bahmani

88
1
7

After Update Trigger Insert old records to another table

3 Answers3