FIM - SQL Triggers for updating records in Delta table

Question

I'm writing a DML trigger when change (update or Insert) happens in one table (Master table), I want to write the whole row into another table (Delta table). The Master and Delta tables contains the same column with same datatype, except that Delta table contains an additional column called 'change_type', which should say either 'INSERT' OR 'MODIFY', depending on which trigger is updating the delta table.

The difficulty I'm having is I want to use the inserted table to update the Delta table row but its giving me errors.

CREATE TRIGGER [dbo].[TR_Update] 
   ON  [dbo].[People_Master]
   AFTER Update
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @RowCount int
Declare @ID int
Declare @Email nvarchar(50)
Declare @ct nvarchar(10)

select @ID = ID from inserted


Select @RowCount=COUNT(*) from People_Delta where People_Delta.ID = @ID and People_Delta.change_type = 'Modify';

if(@RowCount = 0)
Begin
    Insert into People_Delta (ID,Email,uac,Department,FirstName,change_type)
    values (iserted.ID,inserted.Email,inserted.uac,inserted.Department,inserted.Firstname'Modify');
END

END
GO

My table has 5 columns.

ID (primary key)
Email
Firstname
uac
Department

Answer 1

You are missing a , in your INSERT statement.

And because the number of columns you have specified does not match with the number of values you are inserting, you get an error.

inserted.Firstname , 'Modify'

Insert into People_Delta (ID,Email,uac,Department,FirstName,change_type)
    values (iserted.ID,inserted.Email,inserted.uac,inserted.Department,inserted.Firstname,'Modify');