Search code examples
sqlsql-servertriggerscdc

MS SQL Server Trigger based on data change from a specific value to another

I would like to create an after update trigger on a table when a specific field, ERDS (I did not name these fields), gets changed from a value of '07' to any other value. Is this something I can do with a trigger or will I have to use the change data capture feature in SQL server?

Ideally, when the ERDS field gets changed from '07' to another value the trigger will take that record and put it into another table.

Solution

  • Try something like this....

    CREATE TRIGGER tr_Checking_ERDS_Column
ON Your_This_Table
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  INSERT INTO dbo.Some_Table(Column1, COlumn2, Column3)
  SELECT i.Column1, i.Column2, i.ERDS
  FROM inserted I INNER JOIN deleted d 
  ON i.PK = d.PK
  WHERE i.ERDS = '07' AND d.ERDS <> '07'

END

    Details

    i.PK = Primary Key Column in your table
Column1, COlumn2, Column3  some random column names just for demo purpose.