Back to OIM Explorer

dbo.CPL_TUComplianceRule

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on ComplianceRule. Bulk DBQueue insert -> CPL-K-ComplianceSubRuleFillPerson / CPL_ZSubRuleFillPerson at line 17; Bulk DBQueue insert -> CPL-K-ComplianceSubRuleFillObject / CPL_ZSubRuleFillObject at line 17; Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 17; Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 17

Source: sandbox-db sys.sql_modules

Source size: 4.700 characters

Interpretation

  • Database trigger. Treat parent table and enqueue/object-layer calls as the main relation points.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> CPL-K-ComplianceSubRuleFillPerson / CPL_ZSubRuleFillPerson at line 17
  • Bulk DBQueue insert -> CPL-K-ComplianceSubRuleFillObject / CPL_ZSubRuleFillObject at line 17
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 17
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 17
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 17
  • Bulk DBQueue insert -> CPL-K-ComplianceSubRuleFillObject / CPL_ZSubRuleFillObject at line 20
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 20
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 20
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 20
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 24
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 24
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 24
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 28
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 28
  • Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 31
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: ComplianceRule

Typed Edges

  • queues DBQueue task CPL_ZSubRuleFillPerson at line 17 Bulk DBQueue insert -> CPL-K-ComplianceSubRuleFillPerson / CPL_ZSubRuleFillPerson at line 17
  • queues DBQueue task CPL_ZSubRuleFillObject at line 17 Bulk DBQueue insert -> CPL-K-ComplianceSubRuleFillObject / CPL_ZSubRuleFillObject at line 17
  • queues DBQueue task CPL_ZComplianceCheckMakeProc at line 17 Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 17
  • queues DBQueue task CPL_ZComplianceCheckForRule at line 17 Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 17
  • queues DBQueue task CPL_ZSubRuleFillObject at line 20 Bulk DBQueue insert -> CPL-K-ComplianceSubRuleFillObject / CPL_ZSubRuleFillObject at line 20
  • queues DBQueue task CPL_ZComplianceCheckMakeProc at line 20 Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 20
  • queues DBQueue task CPL_ZComplianceCheckForRule at line 20 Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 20
  • queues DBQueue task CPL_ZComplianceCheckMakeProc at line 24 Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 24
  • queues DBQueue task CPL_ZComplianceCheckForRule at line 24 Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 24
  • queues DBQueue task CPL_ZComplianceCheckMakeProc at line 28 Bulk DBQueue insert -> CPL-K-ComplianceCheckMakeProc / CPL_ZComplianceCheckMakeProc at line 28
  • queues DBQueue task CPL_ZComplianceCheckForRule at line 28 Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 28
  • queues DBQueue task CPL_ZComplianceCheckForRule at line 31 Bulk DBQueue insert -> CPL-K-ComplianceCheckForRule / CPL_ZComplianceCheckForRule at line 31
  • trigger on table ComplianceRule Trigger parent table: ComplianceRule
  • references source dbo.QBM_FGIBitPatternXOrigin source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QBM_PSQLCheckExecutable source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL206 lines
1CREATE trigger CPL_TUComplianceRule2  ON ComplianceRule FOR3UPDATE NOT FOR Replication4AS5BEGIN6  DECLARE @SQLToCheck QBM_YParameterList7  DECLARE @CursorBuffer QBM_YParameterList8  DECLARE @XUser nvarchar(64) = object_name(@@procid)9  DECLARE @Xdate datetime = getutcdate()10  BEGIN TRY11    IF EXISTS(12      SELECT TOP 1 113      FROM inserted)14    GOTO start15    IF EXISTS(16      SELECT TOP 1 117      FROM deleted)18    GOTO start19    RETURN start:20    DECLARE @GenProcID varchar(38)21    SELECT @GenProcID = dbo.QBM_FGISessionContext('')22    DECLARE @errno int23    DECLARE @errmsg nvarchar(255)24    DECLARE @UID_NonCompliance varchar(38)25    DECLARE @UID_ComplianceRule varchar(38)26    DECLARE @WhereClause nvarchar(1024)27    IF28    UPDATE(WhereClause) OR29    UPDATE(WhereClausePerson)30    BEGIN31      INSERT INTO @SQLToCheck(Parameter1,32      ContentFull)33      SELECT34        c.XObjectKey,35        'select top 1 1 as columnvalue from ' + 'Person' + '36		 where (37		 ' + c.WhereClause + '38		 )'39      FROM inserted c40      WHERE41        c.WhereClause > ' ' AND c.IsSimpleMode = 042      INSERT INTO @SQLToCheck(Parameter1,43      ContentFull)44      SELECT45        c.XObjectKey,46        'select top 1 1 as columnvalue from ' + 'Person' + '47		 where (48		 ' + c.WhereClausePerson + '49		 )'50      FROM inserted c51      WHERE52        c.WhereClausePerson > ' '53      EXEC QBM_PSQLCheckExecutable @SQLToCheck,54        @RaiseError = 155    END56    IF57    UPDATE(WhereClausePerson) OR58    UPDATE(IsInactive) OR59    UPDATE(IsWorkingCopy) OR60    UPDATE(IsPersonStoreInverted)61    BEGIN62      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw63      INSERT INTO @DBQueueElements_01(object,64      subobject,65      genprocid)66      SELECT67        x.uid,68        NULL,69        @GenProcID70      FROM(71      SELECT i.uid_ComplianceRule AS uid72      FROM inserted i) AS x73      EXEC QBM_PDBQueueInsert_Bulk 'CPL-K-ComplianceSubRuleFillPerson',74        @DBQueueElements_0175    END76    IF77    UPDATE(IsSimpleMode) OR78    UPDATE(IsInactive) OR79    UPDATE(IsWorkingCopy)80    BEGIN81      DECLARE @DBQueueElements_02 QBM_YDBQueueRaw82      INSERT INTO @DBQueueElements_02(object,83      subobject,84      genprocid)85      SELECT86        x.uid,87        NULL,88        @GenProcID89      FROM(90      SELECT s.uid_ComplianceSubRule AS uid91      FROM inserted i92      JOIN ComplianceSubrule s93        ON i.uid_complianceRule = s.uid_complianceRule) AS x94      EXEC QBM_PDBQueueInsert_Bulk 'CPL-K-ComplianceSubRuleFillObject',95        @DBQueueElements_0296    END97    IF(98      UPDATE(WhereClause) OR99    UPDATE(UID_NonCompliance) OR100    UPDATE(UID_OrgRuler) OR101    UPDATE(IsExceptionAllowed) OR102    UPDATE(RuleSeverity) OR103    UPDATE(DetectRuleTypeForPWO) OR104    UPDATE(isToGrantEver) OR105    UPDATE(IsCrossPersonCheck) OR106    UPDATE(IsSimpleMode)) AND(107    UPDATE(IsInactive) OR EXISTS(108    SELECT TOP 1 1109    FROM inserted110    WHERE111      isnull(IsInactive, 0) = 0 AND isnull(IsWorkingCopy, 0) = 0))112    BEGIN113      DECLARE @DBQueueElements_03 QBM_YDBQueueRaw114      INSERT INTO @DBQueueElements_03(object,115      subobject,116      genprocid)117      SELECT118        x.uid,119        NULL,120        @GenProcID121      FROM(122      SELECT t.UID_ComplianceRule AS uid123      FROM ComplianceRule t124      JOIN deleted d125        ON t.UID_ComplianceRule = d.UID_ComplianceRule) AS x126      EXEC QBM_PDBQueueInsert_Bulk 'CPL-K-ComplianceCheckMakeProc',127        @DBQueueElements_03128    END129    IF(130      UPDATE(WhereClause) OR131    UPDATE(IsInactive) OR132    UPDATE(UID_NonCompliance) OR133    UPDATE(IsExceptionAllowed) OR134    UPDATE(DetectRuleTypeForPWO) OR135    UPDATE(isToGrantEver) OR136    UPDATE(IsCrossPersonCheck) OR137    UPDATE(IsWorkingCopy) OR138    UPDATE(IsCrossPersonCheck) OR139    UPDATE(IsSimpleMode))140    BEGIN141      DECLARE @DBQueueElements_04 QBM_YDBQueueRaw142      INSERT INTO @DBQueueElements_04(object,143      subobject,144      genprocid)145      SELECT146        x.uid,147        NULL,148        @GenProcID149      FROM(150      SELECT t.UID_ComplianceRule AS uid151      FROM ComplianceRule t152      JOIN deleted d153        ON t.UID_ComplianceRule = d.UID_ComplianceRule) AS x154      EXEC QBM_PDBQueueInsert_Bulk 'CPL-K-ComplianceCheckMakeProc',155        @DBQueueElements_04156      DECLARE @DBQueueElements_05 QBM_YDBQueueRaw157      INSERT INTO @DBQueueElements_05(object,158      subobject,159      genprocid)160      SELECT161        x.uid,162        NULL,163        @GenProcID164      FROM(165      SELECT t.UID_ComplianceRule AS uid166      FROM ComplianceRule t167      JOIN deleted d168        ON t.UID_ComplianceRule = d.UID_ComplianceRule169      WHERE170        isnull(t.IsInactive, 0) = 0 AND isnull(t.IsWorkingCopy, 0) = 0) AS x171      EXEC QBM_PDBQueueInsert_Bulk 'CPL-K-ComplianceCheckForRule',172        @DBQueueElements_05173    END174    IF175    UPDATE(UID_NonCompliance) OR176    UPDATE(IsInActive) OR177    UPDATE(IsWorkingCopy)178    BEGIN179      UPDATE PersonInBaseTree180      SET XOrigin = pio.XOrigin & dbo.QBM_FGIBitPatternXOrigin('|Dynamic|',181      1),182      XDateUpdated = @Xdate,183      XUserUpdated = @XUser184      FROM PersonInBaseTree pio185      JOIN(186      SELECT187        DISTINCT d.UID_NonCompliance188      FROM deleted d189      JOIN ComplianceRule r190        ON d.uid_ComplianceRule = r.uid_ComplianceRule191      WHERE192        isnull(d.UID_NonCompliance, '') <> isnull(r.UID_NonCompliance, '') OR(isnull(d.IsInActive, 0) = 0 AND isnull(r.IsInActive,193      0) = 1) OR(isnull(d.IsWorkingCopy, 0) = 0 AND isnull(r.IsWorkingCopy, 0) = 1)) AS x194        ON pio.UID_Org = x.UID_NonCompliance195    END196  END TRY197  BEGIN CATCH198    EXEC QBM_PSessionErrorAdd DEFAULT199    RAISERROR('',200    18,201    1)202      WITH NOWAIT203  END CATCH204  ende:205  RETURN206END
Open raw exported source
SQL ยท Raw37 lines
1create   trigger CPL_TUComplianceRule on ComplianceRule  for Update not for Replication as begin  declare @SQLToCheck QBM_YParameterList declare2 @CursorBuffer QBM_YParameterList declare @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() BEGIN TRY if exists (select3 top 1 1 from inserted) goto start if exists (select top 1 1 from deleted) goto start return start: declare @GenProcID varchar(38) select @GenProcID = 4dbo.QBM_FGISessionContext('') declare @errno int declare @errmsg nvarchar(255) declare @UID_NonCompliance varchar(38) declare @UID_ComplianceRule varchar5(38) declare @WhereClause nvarchar(1024)  if update(WhereClause) or update(WhereClausePerson) begin insert into @SQLToCheck(Parameter1, ContentFull) select6 c.XObjectKey, 'select top 1 1 as columnvalue from ' + 'Person' + '7		 where (8		 ' + c.WhereClause + '9		 )' from inserted c  where c.WhereClause >10 ' ' and c.IsSimpleMode = 0   insert into @SQLToCheck(Parameter1, ContentFull) select c.XObjectKey, 'select top 1 1 as columnvalue from ' + 'Person' + 11'12		 where (13		 ' + c.WhereClausePerson + '14		 )' from inserted c  where c.WhereClausePerson > ' ' exec QBM_PSQLCheckExecutable @SQLToCheck, @RaiseError15 = 1 end if update(WhereClausePerson)  or update(IsInactive) or update(IsWorkingCopy) or update(IsPersonStoreInverted)  begin declare @DBQueueElements_0116 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select i.uid_ComplianceRule as uid17 from inserted i ) as x exec QBM_PDBQueueInsert_Bulk 'CPL-K-ComplianceSubRuleFillPerson', @DBQueueElements_01 end if update(IsSimpleMode) or update(IsInactive18) or update(IsWorkingCopy) begin declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid19, null, @GenProcID from ( select s.uid_ComplianceSubRule as uid from inserted i join ComplianceSubrule s on i.uid_complianceRule = s.uid_complianceRule20 ) as x exec QBM_PDBQueueInsert_Bulk 'CPL-K-ComplianceSubRuleFillObject', @DBQueueElements_02 end  if (update(WhereClause) or update(UID_NonCompliance)21 or update(UID_OrgRuler) or update(IsExceptionAllowed) or update(RuleSeverity)  or update(DetectRuleTypeForPWO) or update(isToGrantEver) or update(IsCrossPersonCheck22)  or update(IsSimpleMode) ) and  ( update(IsInactive) or exists (select top 1 1 from inserted where isnull(IsInactive, 0) = 0 and isnull(IsWorkingCopy23, 0) = 0 ) ) begin declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, subobject, genprocid) select x.uid, null, @GenProcID24 from ( select t.UID_ComplianceRule as uid from ComplianceRule t join deleted d on t.UID_ComplianceRule = d.UID_ComplianceRule ) as x exec QBM_PDBQueueInsert_Bulk25 'CPL-K-ComplianceCheckMakeProc', @DBQueueElements_03 end   if (update(WhereClause) or update(IsInactive) or update(UID_NonCompliance) or update(IsExceptionAllowed26)  or update(DetectRuleTypeForPWO) or update(isToGrantEver) or update(IsCrossPersonCheck)  or update(IsWorkingCopy)  or update(IsCrossPersonCheck) or update27(IsSimpleMode) ) begin declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object, subobject, genprocid) select x.uid, null, @GenProcID28 from ( select t.UID_ComplianceRule as uid from ComplianceRule t join deleted d on t.UID_ComplianceRule = d.UID_ComplianceRule ) as x exec QBM_PDBQueueInsert_Bulk29 'CPL-K-ComplianceCheckMakeProc', @DBQueueElements_04  declare @DBQueueElements_05 QBM_YDBQueueRaw insert into @DBQueueElements_05 (object, subobject, 30genprocid) select x.uid, null, @GenProcID from ( select t.UID_ComplianceRule as uid from ComplianceRule t join deleted d on t.UID_ComplianceRule = d.UID_ComplianceRule31 where isnull(t.IsInactive, 0) = 0 and isnull(t.IsWorkingCopy, 0) = 0 ) as x exec QBM_PDBQueueInsert_Bulk 'CPL-K-ComplianceCheckForRule', @DBQueueElements_0532 end    if update(UID_NonCompliance) or update(IsInActive) or update(IsWorkingCopy) begin update PersonInBaseTree set XOrigin = pio.XOrigin & dbo.QBM_FGIBitPatternXOrigin33('|Dynamic|', 1)    , XDateUpdated = @Xdate , XUserUpdated = @XUser from PersonInBaseTree pio join ( select distinct d.UID_NonCompliance from deleted d34 join ComplianceRule r on d.uid_ComplianceRule = r.uid_ComplianceRule where isnull(d.UID_NonCompliance,'') <> isnull(r.UID_NonCompliance,'') or (isnull35(d.IsInActive, 0) = 0 and isnull(r.IsInActive, 0) = 1) or (isnull(d.IsWorkingCopy, 0) = 0 and isnull(r.IsWorkingCopy, 0) = 1) ) as x on pio.UID_Org = x.UID_NonCompliance36 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH ende: return end 37