Back to OIM Explorer

dbo.QBM_FSQTriggerWatchTable

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 4.245 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVStringToIndent source text reference
  • references source dbo.QBM_FSQTriggerWatchColumn source text reference
  • references source dbo.QBM_FSQTriggerWatchDisplay source text reference
  • references source dbo.QBM_FTColumnsToWatch source text reference

References

Referenced By

Complete Source

SQL176 lines
1CREATE FUNCTION dbo.QBM_FSQTriggerWatchTable(2  @TableName nvarchar(64),3  @Operation nvarchar(16)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @erg nvarchar(max) = N ''9  DECLARE @ergTmp nvarchar(max) = N ''10  DECLARE @Tables QBM_YSingleGUID11  DECLARE @CurrentTable varchar(30)12  DECLARE @DebugSwitch int = 013  DECLARE @UmLadenTempEcht nvarchar(max) = '14 15	insert into @DialogWatchOperation (ObjectKeyOfRow, DisplayValue, UID_DialogWatchOperation)16		select x.ObjectKeyOfRow, x.DisplayValue, NEWID()17			from (18				select distinct ObjectkeyOfRow , DisplayValue19				from @DialogWatchProperty20				) as x2122	insert into DialogWatchProperty (UID_DialogWatchProperty,UID_DialogWatchOperation, UID_DialogColumn, ContentShort, HasContentFull, ContentFull)23			select newid(), o.UID_DialogWatchOperation, p.UID_DialogColumn, p.ContentShort, p.HasContentFull, p.ContentFull24				from @DialogWatchOperation o join @DialogWatchProperty p on o.ObjectkeyOfRow = p.ObjectkeyOfRow2526	insert into DialogWatchOperation ( UID_DialogWatchOperation , GenProcID ,OperationType,  OperationDate, OperationUser, ObjectKeyOfRow , OperationLevel, DisplayValue )27		select t.UID_DialogWatchOperation , @GenProcID , ''%OperationType%'',  GetUTCDate(), @ActionUser, t.ObjectKeyOfRow , @OperationLevel, t.DisplayValue28			from @DialogWatchOperation t2930'31  DECLARE @XOriginHandling BIT = 032  IF @operation IN('insert')33  BEGIN34    INSERT INTO @Tables(UID_SingleGuid)35    SELECT36      DISTINCT TableNameView37    FROM dbo.QBM_FTColumnsToWatch(@TableName,38    @Operation)39    WHILE EXISTS(40    SELECT TOP 1 141    FROM @Tables)42    BEGIN43      SELECT TOP 1 @CurrentTable = t.UID_SingleGuid44      FROM @Tables t45      IF @erg > ' '46      BEGIN47        SELECT48          @erg = CONCAT(@erg,49          '50				union all')51      END52      SELECT53        @erg = CONCAT(@erg,54        '			55			select newid() , @GenProcID , ''',56        upper(substring(@operation, 1, 1)),57        N ''',  GetUTCDate(), @ActionUser, t.XObjectKey , @OperationLevel, ')58      SELECT59        @erg = CONCAT(@erg,60        dbo.QBM_FSQTriggerWatchDisplay(@TableName, N 't'))61      SELECT62        @erg = CONCAT(@erg,63        N '64			from inserted t')65      IF @CurrentTable <> @TableName66      BEGIN67        SELECT68          @erg = CONCAT(@erg,69          '70			where t.XObjectKey like ''<Key><T>',71          @CurrentTable,72          '</T>%''')73      END74      DELETE @Tables75      WHERE76        UID_SingleGuid = @CurrentTable77    END78    IF @erg > ' '79    BEGIN80      SELECT81        @erg = CONCAT(N '82	 insert into DialogWatchOperation ( UID_DialogWatchOperation , GenProcID ,OperationType,  OperationDate, OperationUser, ObjectKeyOfRow , OperationLevel, DisplayValue )83'84        ,85        @erg)86    END87    GOTO endLabel88  END89  IF EXISTS(90    SELECT TOP 1 191    FROM DialogTable t92    WITH(readpast)93  JOIN DialogColumn c94    WITH(readpast)95    ON t.UID_DialogTable = c.UID_DialogTable96  WHERE97    t.TableName = @TableName AND c.ColumnName = 'XOrigin')98  BEGIN99    SELECT @XOriginHandling = 1100  END101  IF @operation = 'update'102  BEGIN103    SELECT104      @ergTmp = dbo.QBM_FSQTriggerWatchColumn(@TableName,105      @operation)106    IF @ergTmp > ' '107    BEGIN108      SELECT109        @erg = CONCAT(@ergTmp,110        REPLACE(@UmLadenTempEcht, '%OperationType%', 'U'))111    END112    IF @XOriginHandling = 1113    BEGIN114      SELECT115        @ergTmp = dbo.QBM_FSQTriggerWatchColumn(@TableName,116        'UpdateDelete')117      IF @ergTmp > ' '118      BEGIN119        SELECT120          @erg = CONCAT(@erg,121          '	-- Delete handling XOrigin122	if update(XOrigin)123	 begin124125		declare @XOriginWatchOperation QBM_YWatchBufferOp126		declare @XOriginWatchProperty QBM_YWatchBufferProp127'128          ,129          replace(REPLACE(@ergTmp, '@DialogWatchOperation', '@XOriginWatchOperation'), '@DialogWatchProperty', '@XOriginWatchProperty'),130          '131132',133          replace(REPLACE(dbo.QBM_FCVStringToIndent(REPLACE(@UmLadenTempEcht, '%OperationType%', 'D'), 1), '@DialogWatchOperation',134          '@XOriginWatchOperation'), '@DialogWatchProperty', '@XOriginWatchProperty'),135          '136137	 end -- if update(XOrigin)138	 -- / Delete handling XOrigin')139      END140    END141    IF @DebugSwitch > 0142    BEGIN143      SELECT144        @erg = CONCAT(@erg,145        ' -- ## DeleteHandling XOrigin im Update')146    END147    GOTO EndLabel148  END149  IF @operation = 'delete'150  BEGIN151    IF @XOriginHandling = 0152    BEGIN153      SELECT154        @ergTmp = dbo.QBM_FSQTriggerWatchColumn(@TableName,155        @operation)156      IF @ergTmp > ' '157      BEGIN158        SELECT159          @erg = CONCAT(@ergTmp,160          REPLACE(@UmLadenTempEcht, '%OperationType%', 'D'))161      END162    END163    ELSE164    BEGIN165      IF @DebugSwitch > 0166      BEGIN167        SELECT168          @erg = CONCAT(@erg,169          ' -- ## DeleteHandling XOrigin im delete: leer')170      END171    END172    GOTO EndLabel173  END174  endLabel:175  RETURN(@erg)176END
Open raw exported source
SQL ยท Raw56 lines
1       create   function dbo.QBM_FSQTriggerWatchTable (@TableName nvarchar(64) , @Operation nvarchar(16) ) returns nvarchar(max) as begin declare2 @erg nvarchar(max) = N'' declare @ergTmp nvarchar(max) = N''  declare @Tables QBM_YSingleGUID  declare @CurrentTable varchar(30) declare @DebugSwitch 3int = 0  declare @UmLadenTempEcht nvarchar(max) = '4 5	insert into @DialogWatchOperation (ObjectKeyOfRow, DisplayValue, UID_DialogWatchOperation)6		select x.ObjectKeyOfRow, x.DisplayValue, NEWID()7			from (8				select distinct ObjectkeyOfRow , DisplayValue9				from @DialogWatchProperty10				) as x1112	insert into DialogWatchProperty (UID_DialogWatchProperty,UID_DialogWatchOperation, UID_DialogColumn, ContentShort, HasContentFull, ContentFull)13			select newid(), o.UID_DialogWatchOperation, p.UID_DialogColumn, p.ContentShort, p.HasContentFull, p.ContentFull14				from @DialogWatchOperation o join @DialogWatchProperty p on o.ObjectkeyOfRow = p.ObjectkeyOfRow1516	insert into DialogWatchOperation ( UID_DialogWatchOperation , GenProcID ,OperationType,  OperationDate, OperationUser, ObjectKeyOfRow , OperationLevel, DisplayValue )17		select t.UID_DialogWatchOperation , @GenProcID , ''%OperationType%'',  GetUTCDate(), @ActionUser, t.ObjectKeyOfRow , @OperationLevel, t.DisplayValue18			from @DialogWatchOperation t1920'21 declare @XOriginHandling bit = 0 if @operation in ('insert') begin insert into @Tables(UID_SingleGuid ) select distinct TableNameView from dbo.QBM_FTColumnsToWatch22(@TableName , @Operation) while exists(select top 1 1 from @Tables ) begin select top 1 @CurrentTable = t.UID_SingleGuid  from @Tables t if @erg > ' ' 23begin select @erg = concat(@erg , '24				union all' ) end select @erg = concat(@erg , '			25			select newid() , @GenProcID , ''' , upper(substring(@operation26,1,1)) , N''',  GetUTCDate(), @ActionUser, t.XObjectKey , @OperationLevel, ' )  select @erg = concat(@erg , dbo.QBM_FSQTriggerWatchDisplay(@TableName ,27 N't') ) select @erg = concat(@erg , N'28			from inserted t' ) if @CurrentTable <> @TableName  begin select @erg = concat(@erg , '29			where t.XObjectKey like ''<Key><T>'30 , @CurrentTable , '</T>%''' ) end delete  @Tables  where UID_SingleGuid  = @CurrentTable end  if @erg > ' ' begin select @erg = concat(N'31	 insert into DialogWatchOperation ( UID_DialogWatchOperation , GenProcID ,OperationType,  OperationDate, OperationUser, ObjectKeyOfRow , OperationLevel, DisplayValue )32'33 , @erg ) end  goto endLabel end  if exists (select top 1 1 from DialogTable t with (readpast) join DialogColumn c with (readpast) on t.UID_DialogTable34 = c.UID_DialogTable where t.TableName = @TableName and c.ColumnName = 'XOrigin' ) begin select @XOriginHandling = 1 end if @operation = 'update' begin35 select @ergTmp = dbo.QBM_FSQTriggerWatchColumn(@TableName, @operation) if @ergTmp > ' ' begin select @erg = concat(@ergTmp , REPLACE(@UmLadenTempEcht,36'%OperationType%', 'U' )) end if @XOriginHandling = 1 begin select @ergTmp = dbo.QBM_FSQTriggerWatchColumn(@TableName, 'UpdateDelete') if @ergTmp > ' '37 begin select @erg = concat(@erg , '	-- Delete handling XOrigin38	if update(XOrigin)39	 begin4041		declare @XOriginWatchOperation QBM_YWatchBufferOp42		declare @XOriginWatchProperty QBM_YWatchBufferProp43'44 ,  replace(REPLACE(@ergTmp , '@DialogWatchOperation' , '@XOriginWatchOperation') , '@DialogWatchProperty' , '@XOriginWatchProperty')  , '4546' ,  replace47(REPLACE(dbo.QBM_FCVStringToIndent(REPLACE(@UmLadenTempEcht,'%OperationType%', 'D' ), 1) , '@DialogWatchOperation' , '@XOriginWatchOperation') , '@DialogWatchProperty'48 , '@XOriginWatchProperty')  , '4950	 end -- if update(XOrigin)51	 -- / Delete handling XOrigin' ) end  end  if @DebugSwitch > 0 begin select @erg = concat52(@erg , ' -- ## DeleteHandling XOrigin im Update') end goto EndLabel end  if @operation = 'delete' begin if @XOriginHandling = 0 begin select @ergTmp =53 dbo.QBM_FSQTriggerWatchColumn(@TableName, @operation) if @ergTmp > ' ' begin select @erg = concat(@ergTmp , REPLACE(@UmLadenTempEcht,'%OperationType%'54, 'D' )) end end else begin if @DebugSwitch > 0 begin select @erg = concat(@erg , ' -- ## DeleteHandling XOrigin im delete: leer' ) end end goto EndLabel55 end  endLabel: return(@erg) end 56