0

I have a table Person:

CREATE TABLE Person
(
    ID INT PRIMARY KEY,
    FirstName varchar(50),
    LastName varchar(50),
    Phone varchar(50),
    Address varchar(50),
    City varchar(50),
    PinCode varchar(50),
    DateOfBirth DATETIME,
    UpdatedOn DATETIME,
    UpdatedBy varchar(50)
)

Whenever I insert or update the multiple fields from above table then I want previous value and current value of all updated fields and store that in another table using Trigger. How we can get values of all updated fields.

For example

INSERT INTO Person 
VALUES (1, 'first', 'last', '11111', 'add', 'city', 'pin', GETDATE(), GETDATE(), 'ABC')

UPDATE Person
SET FirstName = 'First11', 
    LastName = 'Last22', 
    Phone = '1010101010'
WHERE id = 1

When I will hit above commands in both cases I want old and current value and store it in another table. How we can achieve this using triggers?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tejas
  • 107
  • 4
  • 13
  • Simple - write on `AFTER INSERT` trigger, and another `AFTER UPDATE` - see [the official MS docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15) on how to do this – marc_s May 20 '20 at 15:23

1 Answers1

0

For SQL Server when you updated ( he delete the old values and then insert the new values ) 

   ALTER TRIGGER [dbo].[YOUR_TRIGGER_NAME_INSERT]
   ON  [dbo].[YOUR_TABLE] 
   AFTER INSERT --Here when you insered rows
   AS 
   BEGIN
        select * from inserted --get all inserted rows  
        --Your code
   END

And Update

   ALTER TRIGGER [dbo].[YOUR_TRIGGER_NAME_UPDATE] 
   ON  [dbo].[YOUR_TABLE] 
   AFTER UPDATE--Here when you updated rows
   AS 
   BEGIN
        select * from inserted --get all inserted (new values) rows 
        select * from deleted--get all deleted (old values) rows    
        --Your code
   END
rachidait
  • 19
  • 3