CREATE trigger [dbo].[TR_JOURNALENTRY_INSUPD] on [dbo].[JOURNALENTRY]
with execute as owner
for insert, update
as
set nocount on
-- declare @ElementsUpdated bit
-- declare @AccountIDUpdated bit
-- declare @JoinString nvarchar(2000)
-- declare @SQL nvarchar(2200)
-- declare @ErrorMessage nvarchar(4000)
-- declare @ErrorSeverity int
-- declare @ErrorState int
-- declare @IsBasicGL bit;
-- declare @GLACCOUNTID uniqueidentifier;
-- select @GLACCOUNTID = inserted.GLACCOUNTID from inserted
-- if @GLACCOUNTID is not null
-- select @IsBasicGL = t2.ISBASICGL from GLACCOUNT T1 join PDACCOUNTSYSTEM T2 on T1.PDACCOUNTSYSTEMID = T2.ID where T1.ID = @GLACCOUNTID
-- if update(GLACCOUNTID)
-- set @AccountIDUpdated = 1
-- if @IsBasicGL = 0
-- begin
-- if ((substring(columns_updated(),2,1) & 192 > 0) or (substring(columns_updated(),3,1) & 255 > 0) or (substring(columns_updated(),4,1) & 255 > 0) or (substring(columns_updated(),5,1) & 255 > 0) or (substring(columns_updated(),6,1) & 15 > 0))
-- set @ElementsUpdated = 1
-- --if update(GLACCOUNTID)
-- -- set @AccountIDUpdated = 1
-- if @AccountIDUpdated = 1 or @ElementsUpdated = 1 --Don't want this doing anything for the audit triggers
-- begin
-- create table #IDs (ID uniqueidentifier, GLACCOUNTID uniqueidentifier, NoDataElements bit, AllDataElements bit, ChangedByID uniqueidentifier)
-- insert into #IDs (ID, GLACCOUNTID, NoDataElements, AllDataElements, ChangedByID)
-- select t1.ID, t1.GLACCOUNTID, case isnull(V1.DataElementCount,0) when 0 then 1 else 0 end, case V1.DataElementCount when (select count(*) from dbo.PDAccountStructure where ElementTypeCode = 1 and PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B') then 1 else 0 end, CHANGEDBYID
-- from inserted t1
-- left join
-- (select unpvt.ID, count(DATAELEMENTID) as DataElementCount --case count(DATAELEMENTID) when 0 then 1 else 0 end, case count(DATAELEMENTID) when (select count(*) from dbo.PDACCOUNTSTRUCTURE where ELEMENTTYPECODE = 1 and PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B') then 1 else 0 end
-- from (select ID, DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
-- DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
-- DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID
-- from inserted) p
-- unpivot
-- (DATAELEMENTID for dColumn in
-- (DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID,
-- DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID,
-- DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)
-- ) as unPvt
-- inner join dbo.PDACCOUNTSEGMENTVALUE on unPvt.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
-- inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID and PDACCOUNTSTRUCTURE.ELEMENTTYPECODE = 1 and PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'
-- group by unpvt.ID) V1
-- on t1.ID = V1.ID
-- select @JoinString = (select ' t1.DATAELEMENT'+convert(varchar(2),SEGMENTCOLUMN)+'ID = t2.DATAELEMENT'+convert(varchar(2),SEGMENTCOLUMN)+'ID and'
-- from dbo.PDACCOUNTSTRUCTURE
-- where ELEMENTTYPECODE = 1 and PDACCOUNTSTRUCTURE.PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'
-- for xml path(''))
-- if @ElementsUpdated = 1 and @AccountIDUpdated = 1 --insert or updated dataelements and GLACCOUNTID
-- begin
-- --This is set based for a bulk update or insert. Since the audit triggers fire regardless of if rows are actually updated,
-- --put in a branch for a single row which is the way we normally do things.
-- if (select count(*) from #IDs) > 1 --bulk
-- begin
-- --GLACCOUNTID is not null and NoDataElements = 1
-- --Update DataElements based on GLACCOUNTID
-- select @SQL = 'update JOURNALENTRY set '+replace(replace(substring(@JoinString,1,len(@JoinString)-3),'and',','),'t1.','')+ ', CHANGEDBYID = #IDs.ChangedByID from dbo.JOURNALENTRY t1 inner join #IDs on t1.ID = #IDs.ID inner join dbo.GLACCOUNT t2 on t1.GLACCOUNTID = t2.ID where #IDs.GLACCOUNTID is not null and #IDs.NoDataElements = 1'
-- exec (@SQL)
-- --GLACCOUNTID is not null and AllDataElements = 0 and NoDataElements = 0
-- --set GLACCOUNTID to null to maintain data integrity
-- select @SQL = 'update JOURNALENTRY set GLACCOUNTID = null, CHANGEDBYID = #IDs.ChangedByID, DATECHANGED = getdate() from dbo.JOURNALENTRY inner join #IDs on JOURNALENTRY.ID = #IDs.ID where #IDs.GLACCOUNTID is not null and #IDs.NoDataElements = 0 and #IDs.AllDataElements = 0'
-- exec (@SQL)
-- --GLACCOUNTID is not null and AllDataElements = 1
-- --verify data integrity, raise an error if a row is found with DataElements that don't match the GLACCOUNTID
-- select @SQL = 'if exists(select t1.* from dbo.JOURNALENTRY t1 inner join #IDs on t1.ID = #IDs.ID inner join dbo.GLACCOUNT t2 on '+substring(@JoinString,1,len(@JoinSTring)-3)+' where #IDs.GLACCOUNTID is not null and #IDs.AllDataElements = 1 and t1.GLACCOUNTID != t2.ID) raiserror(''ERR_INVALID_ACCOUNTELEMENT_DATA'',13,1) '
-- begin try
-- exec (@SQL)
-- end try
-- begin catch
-- rollback
-- select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
-- raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
-- return
-- end catch
-- --GLACCOUNTID is null and AllDataElements = 1
-- --Update GLACCOUNTID based on DataElements
-- select @SQL = 'update JOURNALENTRY set GLACCOUNTID = t2.ID, CHANGEDBYID = #IDs.ChangedByID, DATECHANGED = getdate() from dbo.JOURNALENTRY t1 inner join #IDs on t1.ID = #IDs.ID inner join dbo.GLACCOUNT t2 on '+substring(@JoinString,1,len(@JoinString)-3)+' where #IDs.GLACCOUNTID is null and #IDs.AllDataElements = 1'
-- exec (@SQL)
-- end
-- else --single row
-- begin
-- declare @GLACCOUNTNull bit
-- declare @NoDataElements bit
-- declare @AllDataElements bit
-- select @GLACCOUNTNull = case when GLACCOUNTID is null then 1 else 0 end,
-- @NoDataElements = NoDataElements,
-- @AllDataElements = AllDataElements
-- from #IDs
-- if @GLACCOUNTNull = 0 and @NoDataElements = 1
-- begin
-- select @SQL = 'update JOURNALENTRY set '+replace(replace(substring(@JoinString,1,len(@JoinString)-3),'and',','),'t1.','')+ ', CHANGEDBYID = #IDs.ChangedByID from dbo.JOURNALENTRY t1 inner join #IDs on t1.ID = #IDs.ID inner join dbo.GLACCOUNT t2 on t1.GLACCOUNTID = t2.ID'
-- exec (@SQL)
-- end
-- else
-- begin
-- --GLACCOUNTID is not null and AllDataElements = 0 and NoDataElements = 0
-- if @GLACCOUNTNull = 0 and @AllDataElements = 0 and @NoDataElements = 0
-- begin
-- select @SQL = 'update JOURNALENTRY set GLACCOUNTID = null, CHANGEDBYID = #IDs.ChangedByID, DATECHANGED = getdate() from dbo.JOURNALENTRY inner join #IDs on JOURNALENTRY.ID = #IDs.ID'
-- exec (@SQL)
-- end
-- else
-- begin
-- --GLACCOUNTID is not null and AllDataElements = 1
-- if @GLACCOUNTNull = 0 and @AllDataElements = 1
-- begin
-- select @SQL = 'if exists(select t1.* from dbo.JOURNALENTRY t1 inner join #IDs on t1.ID = #IDs.ID inner join dbo.GLACCOUNT t2 on '+substring(@JoinString,1,len(@JoinSTring)-3)+' where t1.GLACCOUNTID != t2.ID) raiserror(''ERR_INVALID_ACCOUNTELEMENT_DATA'',13,1) '
-- begin try
-- exec (@SQL)
-- end try
-- begin catch
-- rollback
-- select @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
-- raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState)
-- return
-- end catch
-- end
-- else
-- begin
-- --GLACCOUNTID is null and AllDataElements = 1
-- if @GLACCOUNTNull = 1 and @AllDataElements = 1
-- begin
-- select @SQL = 'update JOURNALENTRY set GLACCOUNTID = t2.ID, CHANGEDBYID = #IDs.ChangedByID, DATECHANGED = getdate() from dbo.JOURNALENTRY t1 inner join #IDs on t1.ID = #IDs.ID inner join dbo.GLACCOUNT t2 on '+substring(@JoinString,1,len(@JoinString)-3)
-- exec (@SQL)
-- end
-- end
-- end
-- end
-- end
-- end
-- else
-- begin
-- if @AccountIDUpdated = 1
-- begin --Update elements based on AccountID
-- select @SQL = 'update JOURNALENTRY set '+replace(replace(substring(@JoinString,1,len(@JoinString)-3),'and',','),'t1.','')+ ', CHANGEDBYID = #IDs.ChangedByID from dbo.JOURNALENTRY t1 inner join #IDs on t1.ID = #IDs.ID left join dbo.GLACCOUNT t2 on t1.GLACCOUNTID = t2.ID '
-- exec (@SQL)
-- end
-- else
-- begin
-- if @ElementsUpdated = 1
-- begin --Update AccountID based on elements
-- select @SQL = 'update JOURNALENTRY set GLACCOUNTID = t2.ID, CHANGEDBYID = #IDs.ChangedByID, DATECHANGED = getdate() from dbo.JOURNALENTRY t1 inner join #IDs on t1.ID = #IDs.ID left join dbo.GLACCOUNT t2 on '+substring(@JoinString,1,len(@JoinString)-3)
-- exec (@SQL)
-- end
-- end
-- end
-- drop table #IDs
-- end
-- end
-- else
--begin
-- if @AccountIDUpdated = 1
-- begin --Update elements based on AccountID
-- declare @ChangeAgentID uniqueidentifier;
-- declare @CURRENTDATE datetime = getdate();
-- create table #AcctIDs (ID uniqueidentifier,GLACCOUNTID uniqueidentifier)
-- insert into #AcctIDs(ID ,GLACCOUNTID)
-- select ID,GLACCOUNTID from inserted
-- if @CHANGEAGENTID is null
-- exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
-- select top 1 @GLACCOUNTID = GLACCOUNTID from inserted order by dateadded desc;
-- select @SQL = (
-- select 'DATAELEMENT' + cast(T2.SEQUENCE as nvarchar(2))+ 'ID = T2.DATAELEMENT' + cast(T2.SEQUENCE as nvarchar(2))+ 'ID ,'
-- from dbo.PDACCOUNTSEGMENT T1
-- join dbo.PDACCOUNTSTRUCTURE T2 on T1.PDACCOUNTSTRUCTUREID = T2.ID
-- where T1.GLACCOUNTID = @GLACCOUNTID
-- order by T2.SEQUENCE for XML PATH(''))
-- set @SQL = @SQL + 'CHANGEDBYID = ' + '''' + convert(varchar(36), @CHANGEAGENTID) + '''' + ', DATECHANGED = ' + '''' + convert(varchar(36),@CURRENTDATE,110) + ''''
-- set @SQL = 'update JOURNALENTRY set ' + @SQL + ' from dbo.JOURNALENTRY t1 inner join #AcctIDs I on t1.ID = I.ID inner join dbo.GLACCOUNT t2 on t1.GLACCOUNTID = t2.ID '
-- exec (@SQL)
-- drop table #AcctIDs
-- end
--end
|
Trigger Definition
