Back to OIM Explorer

dbo.ADS_TUADSAccount

Database TriggerSQL_TRIGGERSandbox DB

Database Trigger on ADSAccount. Bulk DBQueue insert -> ADS-K-PersonHasObject / ADS_ZPersonHasObject at line 25; Bulk DBQueue insert -> ADS-K-ADSAccountInADSGroup / ADS_ZAccountInADSGroup at line 25; Bulk DBQueue insert -> ADS-K-ADSAccountInADSGroup / ADS_ZAccountInADSGroup at line 32; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 5.785 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 -> ADS-K-PersonHasObject / ADS_ZPersonHasObject at line 25
  • Bulk DBQueue insert -> ADS-K-ADSAccountInADSGroup / ADS_ZAccountInADSGroup at line 25
  • Bulk DBQueue insert -> ADS-K-ADSAccountInADSGroup / ADS_ZAccountInADSGroup at line 32
  • References QBM_PDBQueueInsert_Bulk
  • Trigger parent table: ADSAccount

Typed Edges

  • queues DBQueue task ADS_ZPersonHasObject at line 25 Bulk DBQueue insert -> ADS-K-PersonHasObject / ADS_ZPersonHasObject at line 25
  • queues DBQueue task ADS_ZAccountInADSGroup at line 25 Bulk DBQueue insert -> ADS-K-ADSAccountInADSGroup / ADS_ZAccountInADSGroup at line 25
  • queues DBQueue task ADS_ZAccountInADSGroup at line 32 Bulk DBQueue insert -> ADS-K-ADSAccountInADSGroup / ADS_ZAccountInADSGroup at line 32
  • trigger on table ADSAccount Trigger parent table: ADSAccount
  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FGIBitPatternXMarkedForDel source text reference
  • 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

Referenced By

Complete Source

SQL241 lines
1CREATE trigger ADS_TUADSAccount2  ON ADSAccount FOR3UPDATE NOT FOR Replication4AS5BEGIN6  IF EXISTS(7    SELECT TOP 1 18    FROM inserted)9  GOTO start10  IF EXISTS(11    SELECT TOP 1 112    FROM deleted)13  GOTO start14  RETURN start:15  DECLARE @GenProcID varchar(38)16  DECLARE @parameter nvarchar(1024)17  DECLARE @QBM_BitPatternXMarkedForDel_OutStanding int = dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',18  0)19  DECLARE @QBM_BitPatternXOrigin_Direct int = dbo.QBM_FGIBitPatternXOrigin('|Direct|',20  0)21  DECLARE @QBM_BitPatternXOrigin_Direct_inv int = dbo.QBM_FGIBitPatternXOrigin('|Direct|',22  1)23  DECLARE @XUser nvarchar(64) = object_name(@@procid)24  DECLARE @Xdate datetime = getutcdate()25  BEGIN TRY26    SELECT @GenProcID = dbo.QBM_FGISessionContext('')27    IF28    UPDATE(UID_ADSGroupPrimary)29    BEGIN30      IF EXISTS(31        SELECT TOP 1 132        FROM inserted i33        JOIN ADSGroup g34          ON i.UID_ADSGroupPrimary = g.UID_ADSGroup35        WHERE36          g.IsITshopOnly = 1)37      BEGIN38        RAISERROR('#LDS#The definition of a group as "IsITshopOnly = 1" and simultaneous use as primary group is not allowed.|',39        18,40        2)41          WITH nowait42      END43      IF EXISTS(44        SELECT TOP 1 145        FROM deleted d46        JOIN ADSAccount a47          ON d.UID_ADSAccount = a.UID_ADSAccount48        WHERE49          isnull(d.UID_aDSGroupPrimary, '') <> isnull(a.UID_ADSGroupPrimary, ''))50      BEGIN51        IF dbo.QBM_FGISessionContext('Fullsync') = ''52        BEGIN53          UPDATE ADSAccountInADSGroup54          SET XOrigin = CASE55          WHEN d.UID_ADSDomain = aneu.UID_ADSDomain THEN56          aig.XOrigin | @QBM_BitPatternXOrigin_Direct57          ELSE 058          END,59          XIsInEffect = CASE60          WHEN d.UID_ADSDomain = aneu.UID_ADSDomain THEN61          162          ELSE 063          END,64          XDateUpdated = @Xdate,65          XUserUpdated = @XUser66          FROM deleted d67          JOIN ADSAccountInADSGroup aig68            ON d.UID_ADSAccount = aig.UID_ADSAccount AND d.UID_ADSGroupPrimary = aig.UID_ADSGroup69          JOIN ADSAccount aneu70            ON d.UID_ADSAccount = aneu.UID_ADSAccount71          WHERE72            aig.XIsInEffect = 073        END74        ELSE75        BEGIN76          UPDATE ADSAccountInADSGroup77          SET XOrigin = aig.XOrigin & @QBM_BitPatternXOrigin_Direct_inv,78          XDateUpdated = @Xdate,79          XUserUpdated = @XUser80          FROM deleted d81          JOIN ADSAccountInADSGroup aig82            ON d.UID_ADSAccount = aig.UID_ADSAccount AND d.UID_ADSGroupPrimary = aig.UID_ADSGroup83          WHERE84            aig.XIsInEffect = 0 AND aig.XOrigin & @QBM_BitPatternXOrigin_Direct > 085        END86      END87    END88    IF89    UPDATE(ObjectSID)90    BEGIN91      IF EXISTS(92        SELECT TOP 1 193        FROM ADSAccount x94        JOIN deleted d95          ON x.uid_ADSAccount= d.uid_ADSAccount96        WHERE97          isnull(d.ObjectSID, N '') <> isnull(x.ObjectSID, N ''))98      BEGIN99        INSERT INTO ADSOtherSID(UID_ADSOtherSID,100        ObjectSID,101        DistinguishedName,102        canonicalName,103        Ident_ADSOtherSID,104        XDateInserted,105        XDateUpdated,106        XUserInserted,107        XUserUpdated,108        XObjectKey)109        SELECT110          x.UID_ADSOtherSID,111          x.ObjectSID,112          x.DistinguishedName,113          x.CanonicalName,114          x.Ident_ADSOtherSID,115          x.XDateInserted,116          x.XDateUpdated,117          x.XUserInserted,118          x.XUserUpdated,119          dbo.QBM_FCVElementToObjectKey1('ADSOtherSID',120          'UID_ADSOtherSID',121          x.UID_ADSOtherSID)122        FROM(123        SELECT124          newid() AS UID_ADSOtherSID,125          d.ObjectSID,126          d.DistinguishedName,127          d.canonicalName,128          d.cn AS Ident_ADSOtherSID,129          d.XDateInserted,130          d.XDateUpdated,131          d.XUserInserted,132          d.XUserUpdated133        FROM ADSAccount x134        JOIN deleted d135          ON x.uid_ADSAccount= d.uid_ADSAccount136        WHERE137          d.ObjectSID > ' ' AND isnull(d.ObjectSID, N '') <> isnull(x.ObjectSID, N '') AND NOT EXISTS(138        SELECT TOP 1 1139        FROM ADSOtherSID x140        WHERE141          x.ObjectSID = d.ObjectSID)) AS x142      END143      IF EXISTS(144        SELECT TOP 1 1145        FROM ADSOtherSID a146        JOIN inserted d147          ON a.ObjectSID = d.ObjectSID)148      BEGIN149        DELETE ADSOtherSID150        WHERE151          objectSID IN(152        SELECT x.ObjectSID153        FROM ADSAccount x154        JOIN deleted d155          ON x.uid_ADSAccount = d.uid_ADSAccount156        WHERE157          isnull(x.ObjectSID, N '') <> isnull(d.ObjectSID, N '')) AND UID_ADSOtherSID NOT LIKE 'ADS-%'158      END159    END160    IF161    UPDATE(UID_Person)162    BEGIN163      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw164      INSERT INTO @DBQueueElements_01(object,165      subobject,166      genprocid)167      SELECT168        x.uid,169        NULL,170        @GenProcID171      FROM(172      SELECT uid_person AS uid173      FROM inserted174      WHERE175        uid_person > ' '176      UNION177      SELECT uid_person178      FROM deleted179      WHERE180        uid_person > ' ') AS x181      EXEC QBM_PDBQueueInsert_Bulk 'ADS-K-PersonHasObject',182        @DBQueueElements_01183    END184    IF185    UPDATE(UID_Person) OR186    UPDATE(IsGroupAccount) OR187    UPDATE(UID_TSBBehavior) OR188    UPDATE(UID_ADSGroupPrimary) OR189    UPDATE(MatchPatternForMembership) OR190    UPDATE(AccountDisabled) OR191    UPDATE(objectclass) OR192    UPDATE(XMarkedForDeletion)193    BEGIN194      DECLARE @DBQueueElements_03 QBM_YDBQueueRaw195      INSERT INTO @DBQueueElements_03(object,196      subobject,197      genprocid)198      SELECT199        x.uid,200        NULL,201        @GenProcID202      FROM(203      SELECT a.UID_ADSAccount AS uid204      FROM ADSAccount a205      JOIN deleted d206        ON a.UID_ADSAccount = d.UID_ADSAccount207      WHERE208        isnull(d.UID_Person, '') <> isnull(a.UID_Person, '') OR isnull(d.IsGroupAccount, 0) <> isnull(a.IsGroupAccount,209      0) OR isnull(d.UID_TSBBehavior, '') <> isnull(a.UID_TSBBehavior, '') OR isnull(d.MatchPatternForMembership,210      0) <> isnull(a.MatchPatternForMembership, 0) OR isnull(d.AccountDisabled, 0) <> isnull(a.AccountDisabled,211      0) OR isnull(d.UID_ADSGroupPrimary, '') <> isnull(a.UID_ADSGroupPrimary, '') OR isnull(d.objectclass,212      '') <> isnull(a.objectclass, '') OR(d.XMarkedForDeletion ^ a.XMarkedForDeletion) & @QBM_BitPatternXMarkedForDel_OutStanding > 0) AS x213      EXEC QBM_PDBQueueInsert_Bulk 'ADS-K-ADSAccountInADSGroup',214        @DBQueueElements_03215    END216    IF217    UPDATE(XMarkedForDeletion) AND dbo.QBM_FGISessionContext('Fullsync') > ' '218    BEGIN219      UPDATE ADSAccountInADSGroup220      SET XMarkedForDeletion = aig.XMarkedForDeletion | @QBM_BitPatternXMarkedForDel_OutStanding,221      XDateUpdated = @Xdate,222      XUserUpdated = @XUser223      FROM deleted d224      JOIN ADSAccount a225        ON d.UID_ADSAccount = a.UID_ADSAccount226      JOIN ADSAccountInADSGroup aig227        ON d.UID_ADSAccount = aig.UID_ADSAccount228      WHERE229        d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding = 0 AND a.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding230      > 0231    END232  END TRY233  BEGIN CATCH234    EXEC QBM_PSessionErrorAdd DEFAULT235    RAISERROR('',236    18,237    1)238      WITH NOWAIT239  END CATCH240  RETURN241END
Open raw exported source
SQL ยท Raw38 lines
1create   trigger ADS_TUADSAccount on ADSAccount  for Update not for Replication as begin  if exists (select top 1 1 from inserted) goto start if2 exists (select top 1 1 from deleted) goto start return start: declare @GenProcID varchar(38) declare @parameter nvarchar(1024) declare @QBM_BitPatternXMarkedForDel_OutStanding3 int = dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 0)  declare @QBM_BitPatternXOrigin_Direct int = dbo.QBM_FGIBitPatternXOrigin('|Direct|', 0) 4 declare @QBM_BitPatternXOrigin_Direct_inv int = dbo.QBM_FGIBitPatternXOrigin('|Direct|', 1)  declare @XUser nvarchar(64) = object_name(@@procid) declare5 @Xdate datetime = getutcdate() BEGIN TRY select @GenProcID = dbo.QBM_FGISessionContext('')  if update (UID_ADSGroupPrimary) begin if exists (select top6 1 1 from inserted i join ADSGroup g on i.UID_ADSGroupPrimary = g.UID_ADSGroup where g.IsITshopOnly = 1 ) begin raiserror( '#LDS#The definition of a group as "IsITshopOnly = 1" and simultaneous use as primary group is not allowed.|'7, 18, 2) with nowait end  if exists (select top 1 1 from deleted d join ADSAccount a on d.UID_ADSAccount = a.UID_ADSAccount where isnull(d.UID_aDSGroupPrimary8, '') <> isnull(a.UID_ADSGroupPrimary, '') ) begin if dbo.QBM_FGISessionContext ('Fullsync') = '' begin update ADSAccountInADSGroup set XOrigin = case 9when d.UID_ADSDomain = aneu.UID_ADSDomain then aig.XOrigin | @QBM_BitPatternXOrigin_Direct  else 0  end , XIsInEffect = case when d.UID_ADSDomain = aneu.UID_ADSDomain10 then 1  else 0  end  , XDateUpdated = @Xdate , XUserUpdated = @XUser from deleted d join ADSAccountInADSGroup aig on d.UID_ADSAccount = aig.UID_ADSAccount11 and d.UID_ADSGroupPrimary = aig.UID_ADSGroup join ADSAccount aneu on d.UID_ADSAccount = aneu.UID_ADSAccount where aig.XIsInEffect = 0 end else begin  12update ADSAccountInADSGroup set XOrigin = aig.XOrigin & @QBM_BitPatternXOrigin_Direct_inv  , XDateUpdated = @Xdate , XUserUpdated = @XUser from deleted13 d join ADSAccountInADSGroup aig on d.UID_ADSAccount = aig.UID_ADSAccount and d.UID_ADSGroupPrimary = aig.UID_ADSGroup where aig.XIsInEffect = 0 and aig.XOrigin14 & @QBM_BitPatternXOrigin_Direct > 0 end end  end if update(ObjectSID) begin  if exists (select top 1 1 from ADSAccount x join deleted d on x.uid_ADSAccount=15 d.uid_ADSAccount where isnull(d.ObjectSID, N'') <> isnull(x.ObjectSID, N'') ) begin insert into ADSOtherSID (UID_ADSOtherSID, ObjectSID , DistinguishedName16, canonicalName, Ident_ADSOtherSID , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, XObjectKey) select x.UID_ADSOtherSID, x.ObjectSID , x.DistinguishedName17, x.CanonicalName, x.Ident_ADSOtherSID , x.XDateInserted, x.XDateUpdated, x.XUserInserted, x.XUserUpdated, dbo.QBM_FCVElementToObjectKey1('ADSOtherSID'18, 'UID_ADSOtherSID', x.UID_ADSOtherSID) from (select newid() as UID_ADSOtherSID, d.ObjectSID , d.DistinguishedName, d.canonicalName, d.cn as Ident_ADSOtherSID19 , d.XDateInserted, d.XDateUpdated, d.XUserInserted, d.XUserUpdated from ADSAccount x join deleted d on x.uid_ADSAccount= d.uid_ADSAccount where d.ObjectSID20 > ' ' and isnull(d.ObjectSID, N'') <> isnull(x.ObjectSID, N'') and not exists (select top 1 1 from ADSOtherSID x where x.ObjectSID = d.ObjectSID ) ) as21 x end   if exists (select top 1 1 from ADSOtherSID a join inserted d on a.ObjectSID = d.ObjectSID ) begin delete ADSOtherSID where objectSID in (select22 x.ObjectSID from ADSAccount x join deleted d on x.uid_ADSAccount = d.uid_ADSAccount where isnull(x.ObjectSID, N'') <> isnull(d.ObjectSID, N'') ) and UID_ADSOtherSID23 not like 'ADS-%'  end  end  if update(UID_Person) begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject24, genprocid) select x.uid, null, @GenProcID from ( select uid_person as uid from inserted where uid_person > ' ' union select uid_person from deleted where25 uid_person > ' ' ) as x exec QBM_PDBQueueInsert_Bulk 'ADS-K-PersonHasObject', @DBQueueElements_01   end if  update(UID_Person) or update(IsGroupAccount26)  or update(UID_TSBBehavior)  or update(UID_ADSGroupPrimary) or update(MatchPatternForMembership) or update(AccountDisabled)  or update(objectclass)  27or update(XMarkedForDeletion) begin declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, subobject, genprocid) select x.uid28, null, @GenProcID from (select a.UID_ADSAccount as uid from ADSAccount a join deleted d on a.UID_ADSAccount = d.UID_ADSAccount where   isnull(d.UID_Person29,'') <> isnull(a.UID_Person,'') or isnull(d.IsGroupAccount,0) <> isnull(a.IsGroupAccount,0)  or isnull(d.UID_TSBBehavior,'') <> isnull(a.UID_TSBBehavior30,'')  or isnull(d.MatchPatternForMembership,0) <> isnull(a.MatchPatternForMembership,0) or isnull(d.AccountDisabled,0) <> isnull(a.AccountDisabled,0)  31or isnull(d.UID_ADSGroupPrimary, '') <> isnull(a.UID_ADSGroupPrimary, '')  or isnull(d.objectclass,'') <> isnull(a.objectclass,'')  or (d.XMarkedForDeletion32 ^ a.XMarkedForDeletion ) & @QBM_BitPatternXMarkedForDel_OutStanding > 0 ) as x exec QBM_PDBQueueInsert_Bulk 'ADS-K-ADSAccountInADSGroup', @DBQueueElements_0333 end  if update(XMarkedForDeletion) and dbo.QBM_FGISessionContext ('Fullsync') > ' ' begin update ADSAccountInADSGroup set XMarkedForDeletion = aig.XMarkedForDeletion34 | @QBM_BitPatternXMarkedForDel_OutStanding  , XDateUpdated = @Xdate , XUserUpdated = @XUser from deleted d join ADSAccount a on d.UID_ADSAccount = a.UID_ADSAccount35 join ADSAccountInADSGroup aig on d.UID_ADSAccount = aig.UID_ADSAccount where d.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_OutStanding = 0 and a.XMarkedForDeletion36 & @QBM_BitPatternXMarkedForDel_OutStanding > 0 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  37return end 38