Create a trigger that inserts values ​​into a new table when updating a column

Question

I looked at some previous answers to triggers here, but cannot find what I need, but I'm sure my question has been asked / answered earlier.

I am trying to track any changes in column A and column B in a table.

If this value changes, I want to track the values ​​by inserting the existing value and the new value into another table with the date.

I was looking for using something similar for insertion, but not sure how to add get existing and new values ​​of the original table (table1):

CREATE TRIGGER NewTrigger ON table1 FOR INSERT AS INSERT INTO table2 (columnA , columnB, todaysDate) . . go 

I need to use (I think)

 Before update ON table1 FOR EACH ROW . . . BEGIN 

and review all the changes and insert them first, then do the same after the update?

Answer 1

Something like this should do what you need. You would have INSERT below the insertion values ​​indicating the operation performed on 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 want, you can also add columns from INSERTED and DELETED to your log table if you want to get the actual values ​​of the column that were inserted or updated.

Answer 2

This is for all changes and all columns, but you can change as you like:

 USE [DB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[trMyTrigger] ON [dbo].[MyTable] AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with caller queries select statements. -- if an update/insert/delete occurs on the main table, the number of records affected -- should only be based on that table and not what records the triggers may/may not -- select. SET NOCOUNT ON; -- Determine if this is an insert,update, or delete action DECLARE @action AS CHAR(1) DECLARE @count AS INT SET @action = 'I' -- SET action to 'I'NSERT by default. SELECT @count = count(*) FROM DELETED IF @count > 0 BEGIN SET @action= 'D' -- SET action to 'D'ELETED. SELECT @count = count(*) FROM INSERTED IF @count > 0 SET @action = 'U' -- SET action to 'U'PDATED. END IF @action = 'D' -- THIS IS A DELETE RECORD ACTION BEGIN INSERT INTO myBackupTable SELECT *,GETDATE() AS changeDate, 'DELETE' AS task FROM DELETED END ELSE BEGIN IF @action = 'I' -- this is an INSERT record action BEGIN INSERT INTO myBackupTable SELECT *,GETDATE() AS changeDate, 'INSERT' as task FROM INSERTED END ELSE -- this is an UPDATE record action BEGIN INSERT INTO myBackupTable SELECT *,GETDATE() AS changeDate, 'UPDATE' as task FROM INSERTED END END 

Answer 3

create trigger trigger on abs
instead of updating as

 declare @idd int , @pricee money select @idd= ProductID from inserted select @pricee = ListPrice from inserted insert into prod values ( @idd , @pricee) print ' cannot change'