TR_SMARTFIELD_UPDATE

CREATE trigger TR_SMARTFIELD_UPDATE on dbo.SMARTFIELD after update not for replication
as begin
    if update(TABLECATALOGID) begin
        if exists(select DELETED.ID from DELETED inner join INSERTED on INSERTED.ID = DELETED.ID where DELETED.TABLECATALOGID <> INSERTED.TABLECATALOGID and DELETED.TABLECATALOGID is not null)                            
            raiserror('The TABLECATALOGID column cannot be updated.', 13, 1)
    end

    if update(SMARTFIELDDATAFORMITEM) begin
        update dbo.SMARTFIELD set LASTRUNON = null, CHANGEDBYID = (select distinct INSERTED.CHANGEDBYID from INSERTED where INSERTED.ID = SMARTFIELD.ID) where SMARTFIELD.ID in (select DELETED.ID from DELETED)                        
    end

    if update(USEVALUEGROUP) or update(NAME) or update(CURRENCYID) or update(SOURCEQUERYVIEWCATALOGID)
        begin
            declare @ID uniqueidentifier
            declare @CHANGEDBYID uniqueidentifier
            declare @NAMECHANGED bit
            declare @OLDNAME nvarchar(100);
            declare @SOURCEQUERYVIEWCHANGED bit;

            if (select count(INSERTED.ID) from INSERTED inner join DELETED on DELETED.ID = INSERTED.ID where INSERTED.USEVALUEGROUP <> DELETED.USEVALUEGROUP or INSERTED.NAME <> DELETED.NAME or INSERTED.CURRENCYID <> DELETED.CURRENCYID or INSERTED.SOURCEQUERYVIEWCATALOGID <> DELETED.SOURCEQUERYVIEWCATALOGID) > 1 
                begin 
                    declare update_cursor cursor local fast_forward for
                        SELECT INSERTED.ID, case when INSERTED.NAME <> DELETED.NAME then 1 else 0 end, INSERTED.CHANGEDBYID, DELETED.NAME, case when INSERTED.SOURCEQUERYVIEWCATALOGID <> DELETED.SOURCEQUERYVIEWCATALOGID then 1 else 0 end from INSERTED inner join DELETED on DELETED.ID = INSERTED.ID where INSERTED.USEVALUEGROUP <> DELETED.USEVALUEGROUP or INSERTED.NAME <> DELETED.NAME or INSERTED.CURRENCYID <> DELETED.CURRENCYID or INSERTED.SOURCEQUERYVIEWCATALOGID <> DELETED.SOURCEQUERYVIEWCATALOGID

                    open update_cursor;

                    fetch next from update_cursor into @ID, @NAMECHANGED, @CHANGEDBYID, @OLDNAME, @SOURCEQUERYVIEWCHANGED;

                    while @@FETCH_STATUS = 0 begin
                        if @ID is not null
                            exec dbo.USP_SMARTFIELD_UPDATESMARTFIELDQUERIES @ID, @NAMECHANGED, @CHANGEDBYID, @OLDNAME, @SOURCEQUERYVIEWCHANGED;

                        fetch next from update_cursor into @ID, @NAMECHANGED, @CHANGEDBYID, @OLDNAME, @SOURCEQUERYVIEWCHANGED;
                    end

                    close update_cursor;

                    deallocate update_cursor;
                end 
            else 
                begin
                    SELECT @ID = INSERTED.ID, @NAMECHANGED = case when INSERTED.NAME <> DELETED.NAME then 1 else 0 end, @CHANGEDBYID = INSERTED.CHANGEDBYID, @OLDNAME = DELETED.NAME, @SOURCEQUERYVIEWCHANGED = case when INSERTED.SOURCEQUERYVIEWCATALOGID <> DELETED.SOURCEQUERYVIEWCATALOGID then 1 else 0 end from INSERTED inner join DELETED on DELETED.ID = INSERTED.ID where INSERTED.USEVALUEGROUP <> DELETED.USEVALUEGROUP or INSERTED.NAME <> DELETED.NAME or INSERTED.CURRENCYID <> DELETED.CURRENCYID or INSERTED.SOURCEQUERYVIEWCATALOGID <> DELETED.SOURCEQUERYVIEWCATALOGID;
                    if @ID is not null
                        exec dbo.USP_SMARTFIELD_UPDATESMARTFIELDQUERIES @ID, @NAMECHANGED, @CHANGEDBYID, @OLDNAME, @SOURCEQUERYVIEWCHANGED;
                end
        end

        if update(CURRENCYID)
        begin
            declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY](); 
            update
                dbo.[SMARTFIELD]
            set
                [SMARTFIELD].[CURRENCYID] = @ORGANIZATIONCURRENCYID,
                [SMARTFIELD].[CHANGEDBYID] = [SMARTFIELD].[CHANGEDBYID],
                [SMARTFIELD].[DATECHANGED] = [SMARTFIELD].[DATECHANGED]
            from
                inserted
                inner join dbo.[SMARTFIELD] on inserted.ID = [SMARTFIELD].[ID]
                inner join dbo.[SMARTFIELDCATALOG] on [SMARTFIELD].[SMARTFIELDCATALOGID] = [SMARTFIELDCATALOG].[ID]
                where
                    [SMARTFIELD].[CURRENCYID] is null
                    and [SMARTFIELDCATALOG].[DATATYPECODE] = 3;
        end

        if update([USEVALUEGROUP])
        begin
            if (select count([ODS].[ID]) from dbo.[OLAPDATASOURCE] [ODS] where [ODS].[MARTKEY] = 'BBDW') > 0
            begin
                if exists (
                    select
                        [INS].[ID]
                    from
                        [inserted] [INS]
                        join [deleted] [DEL] on [INS].[ID] = [DEL].[ID]
                        left join dbo.[OLAPDATASOURCESMARTFIELD] [ODSSF] on [INS].[ID] = [ODSSF].[SMARTFIELDID]
                    where 
                        [INS].[USEVALUEGROUP] = 0 and [DEL].[USEVALUEGROUP] = 1)
                begin

                    declare @CONTEXTCACHE varbinary(128) = CONTEXT_INFO()
            set CONTEXT_INFO @CHANGEDBYID;

                    delete from
                        [OLAPDATASOURCESMARTFIELD]
                    where
                        [SMARTFIELDID] in (
                            select
                                [INS].[ID]
                            from
                                [inserted] [INS]
                                join [deleted] [DEL] on [INS].[ID] = [DEL].[ID]
                                left join dbo.[OLAPDATASOURCESMARTFIELD] [ODSSF] on [INS].[ID] = [ODSSF].[SMARTFIELDID]
                            where
                                [INS].[USEVALUEGROUP] = 0 and [DEL].[USEVALUEGROUP] = 1);

                        if @CONTEXTCACHE is not null begin
                                set CONTEXT_INFO @CONTEXTCACHE
            end;
                end
            end
        end
    end