Back to OIM Explorer

dbo.QER_TIPersonInBaseTree

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on PersonInBaseTree. Bulk DBQueue insert -> QER-K-AllForPersonInBaseTree / QER_ZAllForPersonInBasetree at line 23; References QBM_PDBQueueInsert_Bulk; Trigger parent table: PersonInBaseTree

Source: sandbox-db sys.sql_modules

Source size: 4.099 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 -> QER-K-AllForPersonInBaseTree / QER_ZAllForPersonInBasetree at line 23
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: PersonInBaseTree

Typed Edges

  • queues DBQueue task QER_ZAllForPersonInBasetree at line 23 Bulk DBQueue insert -> QER-K-AllForPersonInBaseTree / QER_ZAllForPersonInBasetree at line 23
  • trigger on table PersonInBaseTree Trigger parent table: PersonInBaseTree
  • 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.QER_PAssignmentCheckValid source text reference
  • references source dbo.QER_TIPerson source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL166 lines
1CREATE trigger QER_TIPersonInBaseTree2  ON PersonInBaseTree FOR3INSERT NOT FOR Replication4AS5BEGIN6  DECLARE @QBM_BitPatternXOrigin_Direct int = dbo.QBM_FGIBitPatternXOrigin('|Direct|',7  0)8  BEGIN TRY9    IF EXISTS(10      SELECT TOP 1 111      FROM inserted)12    GOTO start13    RETURN start:14    DECLARE @GenProcID varchar(38)15    SELECT @GenProcID = dbo.QBM_FGISessionContext('')16    DECLARE @AssignCheckValues QBM_YparameterList17    INSERT INTO @AssignCheckValues(Parameter1,18    Parameter2)19    SELECT20      i.UID_Org,21      i.XOrigin22    FROM inserted i23    EXEC QER_PAssignmentCheckValid 'QER-AsgnBT-Person',24      @AssignCheckValues,25      @GenProcID26    IF EXISTS(27      SELECT TOP 1 128      FROM inserted i29      WHERE30        isnull(i.uid_org, '') = '')31    BEGIN32      RAISERROR('#LDS#Empty values not permitted.|',33      18,34      1)35        WITH nowait36    END37    IF EXISTS(38      SELECT TOP 1 139      FROM BaseTreeExcludesBasetree)40    BEGIN41      IF EXISTS(42        SELECT TOP 1 143        FROM inserted i44        JOIN PersonInBaseTree pio45          ON i.uid_person = pio.uid_person AND pio.XOrigin > 046        JOIN BaseTreeExcludesBaseTree beb47          ON pio.uid_org = beb.uid_org AND beb.uid_OrgExcluded = i.uid_org48        JOIN BaseTree bex49          ON beb.UID_OrgExcluded = bex.UID_Org50      LEFT51      OUTER52        JOIN DynamicGroup g53          ON g.ObjectKeyBaseTree = bex.XObjectKey54        WHERE55          g.UID_DynamicGroup IS NULL) OR EXISTS(56      SELECT TOP 1 157      FROM inserted i58      JOIN PersonInBaseTree pio59        ON i.uid_person = pio.uid_person AND pio.XOrigin > 060      JOIN BaseTreeExcludesBaseTree beb61        ON pio.uid_org = beb.uid_orgExcluded AND beb.uid_Org = i.uid_org62      JOIN BaseTree bex63        ON beb.UID_OrgExcluded = bex.UID_Org64      LEFT65      OUTER66      JOIN DynamicGroup g67        ON g.ObjectKeyBaseTree = bex.XObjectKey68      WHERE69        g.UID_DynamicGroup IS NULL)70      BEGIN71        RAISERROR('#LDS#Cannot make assignment because there are already identity assignments to roles that exclude the role(s) to be added.|',72        18,73        2)74          WITH nowait75      END76    END77    IF EXISTS(78      SELECT TOP 1 179      FROM inserted i80      JOIN BaseTree n81        ON i.UID_Org = n.UID_Org AND i.XOrigin & @QBM_BitPatternXOrigin_Direct = 182      JOIN OrgRoot r83        ON n.UID_OrgRoot = r.UID_OrgRoot AND r.IsPersonAssignOnce = 184      JOIN PersonInBaseTree pib85        ON i.UID_Person = pib.UID_Person86      JOIN BaseTree o87        ON pib.UID_Org = o.UID_Org AND o.UID_OrgRoot = r.UID_OrgRoot88      GROUP BY pib.uid_person89      HAVING count(*) > 1)90    BEGIN91      RAISERROR('#LDS#Cannot make direct assignment because there are already assignments of this person to roles in this role class with IsPersonAssignOnce.|'92      ,93      18,94      2)95        WITH nowait96    END97    IF EXISTS(98      SELECT TOP 1 199      FROM inserted i100      JOIN BaseTree n101        ON i.UID_Org = n.UID_Org AND i.XOrigin & @QBM_BitPatternXOrigin_Direct = 1102      JOIN OrgRoot rn103        ON n.UID_OrgRoot = rn.UID_OrgRoot104      JOIN Orgtype t105        ON rn.UID_OrgType = t.UID_OrgType AND t.IsPersonAssignOnce = 1106      JOIN PersonInBaseTree pib107        ON i.UID_Person = pib.UID_Person108      JOIN BaseTree o109        ON pib.UID_Org = o.UID_Org110      JOIN OrgRoot ro111        ON o.UID_OrgRoot = ro.UID_OrgRoot AND ro.UID_OrgType = t.UID_OrgType112      GROUP BY pib.uid_person113      HAVING count(*) > 1)114    BEGIN115      RAISERROR('#LDS#Cannot make direct assignment because there are already assignments of this person to roles in a role class in this role type with IsPersonAssignOnce.|'116      ,117      18,118      2)119        WITH nowait120    END121    IF EXISTS(122      SELECT TOP 1 1123      FROM inserted i124      JOIN BaseTree n125        ON i.UID_Org = n.UID_Org AND i.XOrigin & @QBM_BitPatternXOrigin_Direct = 1126      JOIN Orgtype t127        ON n.UID_OrgType = t.UID_OrgType AND t.IsPersonAssignOnce = 1128      JOIN PersonInBaseTree pib129        ON i.UID_Person = pib.UID_Person130      JOIN BaseTree o131        ON pib.UID_Org = o.UID_Org AND o.UID_OrgType = t.UID_OrgType132      GROUP BY pib.uid_person133      HAVING count(*) > 1)134    BEGIN135      RAISERROR('#LDS#Cannot make direct assignment because there are already assignments of this person to roles in this role type with IsPersonAssignOnce.|'136      ,137      18,138      2)139        WITH nowait140    END141    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw142    INSERT INTO @DBQueueElements_01(object,143    subobject,144    genprocid)145    SELECT146      x.uid_parameter,147      x.uid_SubParameter,148      @GenProcID149    FROM(150    SELECT151      m.uid_person AS uid_parameter,152      m.uid_org AS uid_subparameter153    FROM inserted m) AS x154    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForPersonInBaseTree',155      @DBQueueElements_01156  END TRY157  BEGIN CATCH158    EXEC QBM_PSessionErrorAdd DEFAULT159    RAISERROR('',160    18,161    1)162      WITH NOWAIT163  END CATCH164  ende:165  RETURN166END
Open raw exported source
SQL ยท Raw26 lines
1     create   trigger QER_TIPersonInBaseTree on PersonInBaseTree  for Insert not for Replication as begin  declare @QBM_BitPatternXOrigin_Direct2 int = dbo.QBM_FGIBitPatternXOrigin('|Direct|', 0)  BEGIN TRY if exists (select top 1 1 from inserted) goto start return start: declare @GenProcID varchar3(38) select @GenProcID = dbo.QBM_FGISessionContext('')   declare @AssignCheckValues QBM_YparameterList insert into @AssignCheckValues(Parameter1, Parameter24) select i.UID_Org, i.XOrigin from inserted i exec QER_PAssignmentCheckValid 'QER-AsgnBT-Person', @AssignCheckValues, @GenProcID if exists (select top 51 1 from inserted i where isnull(i.uid_org, '') = '' ) begin  raiserror( '#LDS#Empty values not permitted.|', 18, 1) with nowait end if exists (select 6top 1 1 from BaseTreeExcludesBasetree ) begin  if exists (select top 1 1 from inserted i join PersonInBaseTree pio  on i.uid_person = pio.uid_person and7 pio.XOrigin > 0  join BaseTreeExcludesBaseTree beb on pio.uid_org = beb.uid_org and beb.uid_OrgExcluded = i.uid_org  join BaseTree bex on beb.UID_OrgExcluded8 = bex.UID_Org left outer join DynamicGroup g on g.ObjectKeyBaseTree = bex.XObjectKey where g.UID_DynamicGroup is null )  or exists (select top 1 1 from9 inserted i join PersonInBaseTree pio  on i.uid_person = pio.uid_person and pio.XOrigin > 0  join BaseTreeExcludesBaseTree beb on pio.uid_org = beb.uid_orgExcluded10 and beb.uid_Org = i.uid_org  join BaseTree bex on beb.UID_OrgExcluded = bex.UID_Org left outer join DynamicGroup g on g.ObjectKeyBaseTree = bex.XObjectKey11 where g.UID_DynamicGroup is null ) begin  raiserror( '#LDS#Cannot make assignment because there are already identity assignments to roles that exclude the role(s) to be added.|'12, 18, 2) with nowait end end   if exists (select top 1 1 from inserted i join BaseTree n on i.UID_Org = n.UID_Org and i.XOrigin & @QBM_BitPatternXOrigin_Direct13 = 1 join OrgRoot r on n.UID_OrgRoot = r.UID_OrgRoot and r.IsPersonAssignOnce = 1 join PersonInBaseTree pib on i.UID_Person = pib.UID_Person join BaseTree14 o on pib.UID_Org = o.UID_Org and o.UID_OrgRoot = r.UID_OrgRoot group by pib.uid_person having count(*) > 1 ) begin raiserror( '#LDS#Cannot make direct assignment because there are already assignments of this person to roles in this role class with IsPersonAssignOnce.|'15, 18, 2) with nowait end if exists (select top 1 1 from inserted i join BaseTree n on i.UID_Org = n.UID_Org and i.XOrigin & @QBM_BitPatternXOrigin_Direct16 = 1 join OrgRoot rn on n.UID_OrgRoot = rn.UID_OrgRoot join Orgtype t on rn.UID_OrgType = t.UID_OrgType and t.IsPersonAssignOnce = 1 join PersonInBaseTree17 pib on i.UID_Person = pib.UID_Person join BaseTree o on pib.UID_Org = o.UID_Org join OrgRoot ro on o.UID_OrgRoot = ro.UID_OrgRoot and ro.UID_OrgType =18 t.UID_OrgType group by pib.uid_person having count(*) > 1 ) begin raiserror( '#LDS#Cannot make direct assignment because there are already assignments of this person to roles in a role class in this role type with IsPersonAssignOnce.|'19, 18, 2) with nowait end if exists (select top 1 1 from inserted i join BaseTree n on i.UID_Org = n.UID_Org and i.XOrigin & @QBM_BitPatternXOrigin_Direct20 = 1 join Orgtype t on n.UID_OrgType = t.UID_OrgType and t.IsPersonAssignOnce = 1 join PersonInBaseTree pib on i.UID_Person = pib.UID_Person join BaseTree21 o on pib.UID_Org = o.UID_Org and o.UID_OrgType = t.UID_OrgType group by pib.uid_person having count(*) > 1 ) begin raiserror( '#LDS#Cannot make direct assignment because there are already assignments of this person to roles in this role type with IsPersonAssignOnce.|'22, 18, 2) with nowait end  declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid_parameter23, x.uid_SubParameter, @GenProcID from ( select m.uid_person as uid_parameter, m.uid_org as uid_subparameter from inserted m ) as x exec QBM_PDBQueueInsert_Bulk24 'QER-K-AllForPersonInBaseTree', @DBQueueElements_01 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH 25ende: return end 26