dbo.ATT_TUAttestationCase

1CREATE trigger ATT_TUAttestationCase2  ON AttestationCase FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @uid_AttestationCase varchar(38)7  DECLARE @DecisionLevelNew int8  DECLARE @attBuffer QBM_YCursorBuffer9  DECLARE @ElementCount int10  DECLARE @ElementIndex int11  DECLARE @ElementLast int12  BEGIN TRY13    IF EXISTS(14      SELECT TOP 1 115      FROM inserted)16    GOTO start17    IF EXISTS(18      SELECT TOP 1 119      FROM deleted)20    GOTO start21    RETURN start:22    DECLARE @GenProcID varchar(38)23    SELECT @GenProcID = dbo.QBM_FGISessionContext('')24    IF25    UPDATE(DecisionLevel)26    BEGIN27      IF EXISTS(28        SELECT TOP 1 129        FROM AttestationCase a30        JOIN deleted d31          ON a.UID_AttestationCase = d.UID_AttestationCase AND a.DecisionLevel <> d.DecisionLevel32        JOIN AttestationHelper h33          ON h.UID_AttestationCase = a.UID_AttestationCase AND h.LevelNumber = a.DecisionLevel AND h.UID_PWODecisionRule = 'ATT-PWODecisionRule-EX' AND34      h.ReasonHead > ' ')35      BEGIN36        UPDATE AttestationHelper37        SET ReasonHead = ''38        FROM AttestationHelper h39        JOIN deleted d40          ON h.UID_AttestationCase = d.UID_AttestationCase41        JOIN AttestationCase a42          ON d.UID_AttestationCase = a.UID_AttestationCase AND a.DecisionLevel = h.LevelNumber AND a.DecisionLevel <> d.DecisionLevel AND h.UID_PWODecisionRule43        = 'ATT-PWODecisionRule-EX' AND h.ReasonHead > ' '44      END45    END46    IF47    UPDATE(IsClosed) OR48    UPDATE(UID_QERWorkingMethod)49    BEGIN50      IF EXISTS(51        SELECT TOP 1 152        FROM inserted i53        WHERE54          sign(len(isnull(i.UID_QERWorkingMethod, ''))) ^ i.IsClosed = 0)55      BEGIN56        RAISERROR('#LDS#Invalid combination of IsClosed and QERWorkingMethod.|',57        18,58        1)59          WITH nowait60      END61    END62    IF63    UPDATE(IsUnderConstruction)64    BEGIN65      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw66      INSERT INTO @DBQueueElements_01(object,67      subobject,68      genprocid)69      SELECT70        x.uid,71        NULL,72        @GenProcID73      FROM(74      SELECT c.UID_AttestationCase AS uid75      FROM deleted d76      JOIN AttestationCase c77        ON d.UID_AttestationCase = c.UID_AttestationCase78      WHERE79        d.IsUnderConstruction = 1 AND c.IsUnderConstruction = 0) AS x80      EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper',81        @DBQueueElements_0182    END83    IF84    UPDATE(IsClosed) OR85    UPDATE(IsGranted) OR86    UPDATE(DecisionLevel)87    BEGIN88      DELETE AttestationHelper89      WHERE90        UID_PWODecisionRule = 'ATT-PWODecisionRule-QP' AND EXISTS(91      SELECT TOP 1 192      FROM inserted i93      WHERE94        i.uid_AttestationCase = AttestationHelper.uid_AttestationCase)95    END96    IF97    UPDATE(isReserved)98    BEGIN99      DELETE AttestationHelper100      WHERE101        UID_PWOdecisionRule = 'ATT-PWODecisionRule-QP' AND EXISTS(102      SELECT TOP 1 1103      FROM deleted d104      JOIN AttestationCase pwo105        ON d.uid_AttestationCase = pwo.uid_AttestationCase AND d.isreserved = 1 AND pwo.isReserved = 0106      WHERE107        pwo.uid_AttestationCase = AttestationHelper.uid_AttestationCase)108    END109    IF110    UPDATE(isclosed)111    BEGIN112      DECLARE @DBQueueElements_02 QBM_YDBQueueRaw113      INSERT INTO @DBQueueElements_02(object,114      subobject,115      genprocid)116      SELECT117        x.uid,118        NULL,119        @GenProcID120      FROM(121      SELECT i.UID_AttestationCase AS uid122      FROM inserted i) AS x123      EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper',124        @DBQueueElements_02125    END126    IF dbo.QBM_FGIColumnUpdatedOthers('AttestationCase',127    'isclosed',128    columns_updated()) = 1 AND dbo.QBM_FGIColumnUpdatedOthers('AttestationCase',129    'DecisionLevel',130    columns_updated()) = 1 AND NOT131    UPDATE(IsUnderConstruction) AND(132    UPDATE(UID_PersonHead) OR133    UPDATE(ReasonHead) OR134    UPDATE(DateHead))135    BEGIN136      DECLARE @DBQueueElements_03 QBM_YDBQueueRaw137      INSERT INTO @DBQueueElements_03(object,138      subobject,139      genprocid)140      SELECT141        x.uid,142        NULL,143        @GenProcID144      FROM(145      SELECT146        DISTINCT pwo.uid_AttestationCase AS uid147      FROM deleted d148      JOIN AttestationCase pwo149        ON d.uid_AttestationCase = pwo.uid_AttestationCase150      WHERE151        (isnull(d.DateHead, '1899-12-30') <> isnull(pwo.DateHead, '1899-12-30') OR isnull(d.ReasonHead, N '') <> isnull(pwo.ReasonHead,152      N '')) AND(isnull(d.UID_PersonHead, '') <> isnull(pwo.UID_PersonHead, '') OR EXISTS(153      SELECT TOP 1 1154      FROM AttestationHelper h155      WHERE156        h.uid_AttestationCase = pwo.uid_AttestationCase AND h.LevelNumber = pwo.decisionlevel AND(h.UID_PersonAdditional > ' ' OR h.UID_PersonInsteadOf157      > ' ' OR h.Decision = '-')))) AS x158      EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper',159        @DBQueueElements_03160      EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationReminder',161        @DBQueueElements_03162    END163    IF164    UPDATE(DecisionLevel)165    BEGIN166      DELETE @attBuffer167      INSERT INTO @attBuffer(UID1,168      Int1)169      SELECT170        pwo.uid_Attestationcase,171        pwo.DecisionLevel172      FROM deleted d173      JOIN Attestationcase pwo174        ON d.uid_Attestationcase = pwo.uid_Attestationcase175      WHERE176        d.DecisionLevel <> pwo.DecisionLevel177      SELECT @ElementCount = @@ROWCOUNT178      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1179      SELECT @ElementLast = @@IDENTITY180      WHILE @ElementIndex <= @ElementLast181      BEGIN182        SELECT183          TOP 1 @uid_AttestationCase = bu.UID1,184          @DecisionLevelNew = bu.Int1185        FROM @attBuffer bu186        WHERE187          bu.ElementIndex = @ElementIndex188        EXEC ATT_PAttestationResetOneLevel @uid_Attestationcase,189          @DecisionLevelNew,190          @GenProcID191        SELECT @ElementIndex += 1192      END193      DECLARE @DBQueueElements_04 QBM_YDBQueueRaw194      INSERT INTO @DBQueueElements_04(object,195      subobject,196      genprocid)197      SELECT198        x.uid,199        NULL,200        @GenProcID201      FROM(202      SELECT bu.UID1 AS uid203      FROM @attBuffer bu) AS x204      EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelperNewLevel',205        @DBQueueElements_04206      EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationCheckValid',207        @DBQueueElements_04208      DECLARE @DBQueueElements_05 QBM_YDBQueueRaw209      INSERT INTO @DBQueueElements_05(object,210      subobject,211      genprocid)212      SELECT213        x.uid,214        NULL,215        @GenProcID216      FROM(217      SELECT pwo.uid_Attestationcase AS uid218      FROM deleted d219      JOIN Attestationcase pwo220        ON d.uid_Attestationcase = pwo.uid_Attestationcase221      WHERE222        d.DecisionLevel = pwo.DecisionLevel) AS x223      EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper',224        @DBQueueElements_05225      EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationCheckValid',226        @DBQueueElements_05227    END228  END TRY229  BEGIN CATCH230    EXEC QBM_PSessionErrorAdd DEFAULT231    RAISERROR('',232    18,233    1)234      WITH NOWAIT235  END CATCH236  ende:237  RETURN238END