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
|
Trigger Definition
