dbo.ATT_TUAttestationCase

1create   trigger ATT_TUAttestationCase on AttestationCase  for UPDATE not for Replication as begin declare @uid_AttestationCase varchar(38) declare
2 @DecisionLevelNew int declare @attBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @ElementLast int  BEGIN TRY if 
3exists (select top 1 1 from inserted) goto start if exists (select top 1 1 from deleted) goto start return start: declare @GenProcID varchar(38) select
4 @GenProcID = dbo.QBM_FGISessionContext('') if UPDATE (DecisionLevel) begin if exists (select top 1 1 from AttestationCase a join deleted d on a.UID_AttestationCase
5 = d.UID_AttestationCase and a.DecisionLevel <> d.DecisionLevel join AttestationHelper h on h.UID_AttestationCase = a.UID_AttestationCase and h.LevelNumber
6 = a.DecisionLevel and h.UID_PWODecisionRule = 'ATT-PWODecisionRule-EX' and h.ReasonHead > ' ' ) begin update AttestationHelper set ReasonHead = '' from
7 AttestationHelper h join deleted d on h.UID_AttestationCase = d.UID_AttestationCase join AttestationCase a on d.UID_AttestationCase = a.UID_AttestationCase
8 and a.DecisionLevel = h.LevelNumber and a.DecisionLevel <> d.DecisionLevel and h.UID_PWODecisionRule = 'ATT-PWODecisionRule-EX' and h.ReasonHead > ' '
9 end end  if update(IsClosed) or update(UID_QERWorkingMethod) begin if exists (select top 1 1  from inserted i where  sign(len(isnull(i.UID_QERWorkingMethod
10, ''))) ^ i.IsClosed = 0 ) begin raiserror('#LDS#Invalid combination of IsClosed and QERWorkingMethod.|', 18, 1) with nowait end end if UPDATE(IsUnderConstruction
11) begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from 
12( select c.UID_AttestationCase as uid from deleted d join AttestationCase c on d.UID_AttestationCase = c.UID_AttestationCase where d.IsUnderConstruction
13 = 1 and c.IsUnderConstruction = 0 ) as x exec QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper', @DBQueueElements_01   end if update(IsClosed) or update
14(IsGranted) or update(DecisionLevel) begin delete AttestationHelper where UID_PWODecisionRule = 'ATT-PWODecisionRule-QP' and exists (select top 1 1 from
15 inserted i where i.uid_AttestationCase = AttestationHelper.uid_AttestationCase) end if update(isReserved) begin  delete AttestationHelper where UID_PWOdecisionRule
16 = 'ATT-PWODecisionRule-QP' and exists (select top 1 1 from deleted d join AttestationCase pwo on d.uid_AttestationCase = pwo.uid_AttestationCase and d.isreserved
17 = 1 and pwo.isReserved = 0 where pwo.uid_AttestationCase = AttestationHelper.uid_AttestationCase ) end if update(isclosed)  begin declare @DBQueueElements_02
18 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select i.UID_AttestationCase as uid
19 from inserted i ) as x  exec QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper', @DBQueueElements_02 end  if dbo.QBM_FGIColumnUpdatedOthers ('AttestationCase'
20, 'isclosed', columns_updated()) = 1 and dbo.QBM_FGIColumnUpdatedOthers ('AttestationCase', 'DecisionLevel', columns_updated()) = 1 and not update(IsUnderConstruction
21)  and (update(UID_PersonHead) or update(ReasonHead) or update(DateHead) ) begin declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03
22 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select distinct pwo.uid_AttestationCase as uid from deleted d join AttestationCase
23 pwo on d.uid_AttestationCase = pwo.uid_AttestationCase where (isnull(d.DateHead, '1899-12-30') <> isnull(pwo.DateHead, '1899-12-30') or isnull(d.ReasonHead
24, N'') <> isnull(pwo.ReasonHead, N'') ) and ( isnull(d.UID_PersonHead, '') <> isnull(pwo.UID_PersonHead, '')   or exists (select top 1 1 from AttestationHelper
25 h where h.uid_AttestationCase = pwo.uid_AttestationCase and h.LevelNumber = pwo.decisionlevel  and (h.UID_PersonAdditional > ' ' or h.UID_PersonInsteadOf
26 > ' '  or h.Decision = '-' ) ) ) ) as x  exec QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper', @DBQueueElements_03  exec QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationReminder'
27, @DBQueueElements_03 end  if update(DecisionLevel) begin delete  @attBuffer  insert into @attBuffer(UID1 , Int1 ) select pwo.uid_Attestationcase, pwo.DecisionLevel
28 from deleted d join Attestationcase pwo on d.uid_Attestationcase = pwo.uid_Attestationcase where d.DecisionLevel <> pwo.DecisionLevel select @ElementCount
29 = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top
30 1 @uid_AttestationCase = bu.UID1  , @DecisionLevelNew = bu.Int1  from @attBuffer bu where bu.ElementIndex = @ElementIndex   exec ATT_PAttestationResetOneLevel
31 @uid_Attestationcase, @DecisionLevelNew, @GenProcID    select @ElementIndex += 1 end  declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04
32 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select bu.UID1  as uid from @attBuffer bu ) as x exec QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelperNewLevel'
33, @DBQueueElements_04 exec QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationCheckValid', @DBQueueElements_04  declare @DBQueueElements_05 QBM_YDBQueueRaw insert
34 into @DBQueueElements_05 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select pwo.uid_Attestationcase as uid from deleted d join
35 Attestationcase pwo on d.uid_Attestationcase = pwo.uid_Attestationcase where d.DecisionLevel = pwo.DecisionLevel ) as x exec QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper'
36, @DBQueueElements_05 exec QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationCheckValid', @DBQueueElements_05 end      END TRY BEGIN CATCH exec QBM_PSessionErrorAdd
37 default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 
38