TR_DEMOGRAPHIC_ETHNICITY_INSERT

create trigger TR_DEMOGRAPHIC_ETHNICITY_INSERT on dbo.DEMOGRAPHIC after insert, update not for replication
as begin

    set nocount on;    

    declare @DATECHANGED date = getdate();

    if update(ETHNICITYCODEID)
        insert into dbo.DEMOGRAPHICETHNICITY 
            (DEMOGRAPHICID, ETHNICITYCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        select 
            i.ID, 
            i.ETHNICITYCODEID,
            i.CHANGEDBYID,
            i.CHANGEDBYID,
            @DATECHANGED,
            @DATECHANGED
        from INSERTED i
            left join dbo.DEMOGRAPHICETHNICITY  on DEMOGRAPHICETHNICITY.DEMOGRAPHICID = i.ID 
                                                  and DEMOGRAPHICETHNICITY.ETHNICITYCODEID = i.ETHNICITYCODEID
            left join DELETED d                 on d.ID = i.ID
        where   
            i.ETHNICITYCODEID is not null           -- ethnicity is being set to some value

            and DEMOGRAPHICETHNICITY.ID is null     -- ethnicity does not yet exist in DEMOGRAPHICETHNICITY

            and d.ETHNICITYCODEID is null           -- value was not already set


end