Create a trigger that inserts values into a new table when a column is updated

I’ve been looking at some previous answers on triggers on here but can’t find what I need exactly but I’m sure my question has been asked/answered before.

I’m trying to keep track of any changes to columnA and columnB in table1.

If this value changes I want to keep track of the values by inserting the existing value and the new Value into a different table with a date.

I’ve been looking at using something like this for the insert but not sure how to add get the existing and new values of the source table (table1):

CREATE TRIGGER NewTrigger ON table1
FOR INSERT
AS

INSERT INTO table2
        (columnA , columnB, todaysDate)
    .
    .

go

I need to use (I think) the

Before update ON table1 FOR EACH ROW
   .
   .
   .
BEGIN

and look through all the changes and insert these first then do the same after the Update?

Answer

Something like this should do what you need. You would have the INSERT statements below insert values indicating the operation performed into MyLogTable.

CREATE TRIGGER [dbo].[TRIG_MyTable]
ON [dbo].[MyTable]
AFTER INSERT, UPDATE

AS 

DECLARE @INS int, @DEL int

SELECT @INS = COUNT(*) FROM INSERTED
SELECT @DEL = COUNT(*) FROM DELETED

IF @INS > 0 AND @DEL > 0 
BEGIN

    -- a record got updated, so log accordingly.

    INSERT INTO MyLogTable
    SELECT 'New Values', getdate() FROM INSERTED

    INSERT INTO MyLogTable
    SELECT 'Old Values', getdate() FROM DELETED

END

ELSE 
BEGIN

    -- a new record was inserted.

    INSERT INTO MyLogTable
    SELECT 'Insert', getdate() FROM INSERTED

END

If you wanted to you could also add columns from INSERTED and DELETED to your log table as well if you wanted to capture the actual column values that got inserted or updated.