TR_SMARTFIELDVALUEGROUP_SYNCSMARTFIELDVIEW (Trigger)

                
create trigger TR_SMARTFIELDVALUEGROUP_SYNCSMARTFIELDVIEW on dbo.SMARTFIELDVALUEGROUP after update, insert, delete not for replication
as begin

    declare @CHANGEAGENTID uniqueidentifier;
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
    
    --Loop through and update all smart fields:

    -- If the trigger is hit on the initial configuration of the smart field, VALUECOLUMNNAME will be blank.

    declare @SMARTFIELDID uniqueidentifier;
    declare SMARTFIELDCURSOR cursor local fast_forward for
        select distinct SMARTFIELDID
        from (
            select SMARTFIELDID
            from inserted
            
            union all
            
            select SMARTFIELDID
            from deleted
        ) SF
            inner join dbo.SMARTFIELD on SF.SMARTFIELDID = SMARTFIELD.ID
        where VALUECOLUMNNAME <> '';
    
    open SMARTFIELDCURSOR;
    fetch next from SMARTFIELDCURSOR into @SMARTFIELDID;
    while @@fetch_status = 0
    begin
        exec dbo.USP_SMARTFIELD_UPDATESMARTFIELDVIEW @SMARTFIELDID, @CHANGEAGENTID;
        fetch next from SMARTFIELDCURSOR into @SMARTFIELDID;
    end
    
    close SMARTFIELDCURSOR;
    deallocate SMARTFIELDCURSOR;
end