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