TR_CONSTITUENCYCODE_UPDATECONSTITUENCYDEFINITION

--Add update trigger to the CONSTITUENCYCODE table to keep it in sync with the associated CONSTITUENCYDEFINITION record
create trigger TR_CONSTITUENCYCODE_UPDATECONSTITUENCYDEFINITION on dbo.CONSTITUENCYCODE after update not for replication
as
begin
    set nocount on;

    if update(ACTIVE) or update(DESCRIPTION)
    begin
        update
            dbo.CONSTITUENCYDEFINITION
        set
            CONSTITUENCYDEFINITION.ISACTIVE = INSERTED.ACTIVE,
            CONSTITUENCYDEFINITION.DESCRIPTION = INSERTED.DESCRIPTION,
            CONSTITUENCYDEFINITION.DATECHANGED = INSERTED.DATECHANGED,
            CONSTITUENCYDEFINITION.CHANGEDBYID = INSERTED.CHANGEDBYID
        from
            CONSTITUENCYDEFINITION
            inner join INSERTED on CONSTITUENCYDEFINITION.ID = INSERTED.ID;

        if update(ACTIVE)
        begin
            --if a constituency code is marked inactive, delete the appropriate rows from CONSTITUENCYDATERANGE
            delete 
            from 
                dbo.CONSTITUENCYDATERANGE
            where ID in
                (
                    select CDR.ID 
                    from 
                        dbo.CONSTITUENCYDATERANGE CDR
                        inner join dbo.CONSTITUENCYDEFINITION on CDR.CONSTITUENCYDEFINITIONID = CONSTITUENCYDEFINITION.ID
                        inner join INSERTED on CONSTITUENCYDEFINITION.ID = INSERTED.ID
                    where
                        INSERTED.ACTIVE = 0
                )
        end

    end
end