dbo.QBM_PRITriggerCreate

1            create   procedure QBM_PRITriggerCreate (@Table nvarchar(32),  @Operation nvarchar(16)  ) as begin  declare @TriggerName nvarchar(64
2) declare @TrigggerOpPrefix nvarchar(1) declare @TriggerBody nvarchar(max) = N'' declare @SQLcmd nvarchar(max) declare @PkUpdateRestrict nvarchar(max) 
3declare @PKUpdateJoin nvarchar(max) declare @PKName1 varchar(30) declare @PKName2 varchar(30) declare @PKCount int declare @VarDefinition nvarchar(max)
4 declare @UID_QBMRelation varchar(38) declare @Fragment nvarchar(16) declare @uid_Dialogtable varchar(38) declare @DebugLevel char(1) = 'W' declare @erg
5 int = 0 declare @DebugSwitch int = 0  declare @Relations QBM_YCursorBuffer    SET XACT_ABORT OFF BEGIN TRY select @TrigggerOpPrefix = case @Operation 
6when 'Insert' then '1' when 'update' then '2' when 'delete' then '3' end select top 1 @uid_Dialogtable = uid_dialogtable from DialogTable with (readpast
7) where TableName = @Table select @TriggerName = dbo.QBM_FGICodeName( 'T' + @TrigggerOpPrefix, rtrim(@Table))   if @Table in (select TableName from QBM_VHeavyLoadTables
8  ) begin goto ende end  if not exists (select top 1 1 from sys.tables t where t.name = @Table ) begin  goto ende end select @VarDefinition = N'
9	declare @IsSimulationMode bit = dbo.QBM_FGIIsSimulationMode() 
10	declare @DBQueueElements QBM_YDBQueueRaw
11	declare @XUser nvarchar(64) = object_name(@@procid)
12	declare @Xdate datetime = getutcdate()
13	declare @SimulationModeBuffer QBM_YTriggerOperation
14	declare @InvalidValue varchar(138)
15	declare @InvalidMessage nvarchar(max)
16	'
17   if @operation = 'Delete' and exists (select top 1 1 from QBMRelation r with (readpast) join DialogColumn c with (readpast) on r.UID_ChildColumn = c.UID_DialogColumn
18 where c.UID_DialogTable = @uid_Dialogtable and ( GenerateParent = 'TDX' ) ) begin select @VarDefinition = @VarDefinition + nchar(13) + nchar(10) + N'  declare @ElementsLater QBM_YParameterList '
19 + nchar(13) + nchar(10) + N'  declare @WorkWhere nvarchar(max)' + nchar(13) + nchar(10) end  if @operation = 'update' begin select @PKUpdateRestrict =
20 N'' select @PKUpdateJoin = N'' select @PKCount = dbo.QBM_FGIPrimaryKeyCount(@Table)  if not exists (select top 1 1 from DialogTable t where t.UID_DialogTable
21 = @uid_Dialogtable and t.isMNTable = 1   and t.usagetype not in ('WORK') ) and @PKCount > 0 begin select @PKName1 = dbo.QBM_FGIPrimaryKeyName_F(@table
22, 1) select @PKName2 = dbo.QBM_FGIPrimaryKeyName_F(@table, 2) select @PKUpdateRestrict = N'update(' + @PKName1 + ')' select @PKUpdateJoin = N'deleted.'
23 + @PKName1 + N' = inserted.' + @PKName1 if @PKCount = 2 begin select @PKUpdateRestrict = @PKUpdateRestrict + N'
24			 or update(' + @PKName2 + ')' select
25 @PKUpdateJoin = @PKUpdateJoin + N'
26			 and deleted.' + @PKName2 + N' = inserted.' + @PKName2 end end  if @PKUpdateRestrict > ' ' begin select @TriggerBody
27 = @TriggerBody + N'
28 if ' + @PKUpdateRestrict + N'
29  begin
30	if (select count(*) from deleted, inserted
31		where ' + @PKUpdateJoin + N'
32		)  <> (select count(*) from inserted)
33	 begin
34			RAISERROR(''#LDS#Not allowed to change the primary key in table {0}.|'
35 + @Table + N'|'', 18, 2) WITH NOWAIT
36	 end
37  end
38
39' end  end    if @operation = 'Update' and exists (select top 1 1 from QBMRelation r with (readpast
40) join DialogColumn c with (readpast) on r.UID_ChildColumn = c.UID_DialogColumn where c.UID_DialogTable = @uid_Dialogtable and ( GenerateParent = 'TDS'
41 or GenerateChild = 'CDS' ) )  and exists (select top 1 1 from dialogColumn c with (readpast) where c.UID_DialogTable = @uid_Dialogtable and Columnname
42 = 'XObjectKey' )  begin select @TriggerBody = @TriggerBody + N'  -- select @ I s S i m u l a t i o n M o d e   =   d b o . Q B M _ F G I I s S i m u l a t i o n M o d e ()  -- ist jetzt schon im declare
43	if @IsSimulationMode = 1
44	  begin
45
46		delete @SimulationModeBuffer
47'
48 delete  @Relations  insert into @Relations (UID1  , Ident1 ) select r.UID_QBMRelation, 'DS' from QBMRelation r with (readpast) join DialogColumn c with
49 (readpast) on r.UID_ChildColumn = c.UID_DialogColumn where c.UID_DialogTable = @uid_Dialogtable and ( GenerateParent = 'TDS' or GenerateChild = 'CDS' 
50)  order by 1  select @UID_QBMRelation = '#' while @UID_QBMRelation > ' ' begin select @UID_QBMRelation = null select top 1 @UID_QBMRelation = r.UID1  
51, @fragment = r.Ident1  from @Relations r order by r.Int1  if @UID_QBMRelation is null begin continue end delete  @Relations where UID1  = @UID_QBMRelation
52 select @TriggerBody = @TriggerBody + dbo.QBM_FSQFKCheckFix (@UID_QBMRelation, 'DS' , @Operation) exec QBM_PJournal @sqlcmd, @@PROCID, 'D', @DebugLevel
53 end  select @TriggerBody = @TriggerBody + N'
54		exec QBM_PFESimulationFill @SimulationModeBuffer
55
56	  end /* if @IsSimulationMode = 1 */ 
57' end   if
58 @operation = 'Delete' and exists (select top 1 1 from QBMRelation r with (readpast) join DialogColumn c with (readpast) on r.UID_ChildColumn = c.UID_DialogColumn
59 where c.UID_DialogTable = @uid_Dialogtable and ( GenerateParent in( 'TDC', 'TDX') or GenerateChild = 'CDC' ) )  and exists (select top 1 1 from dialogColumn
60 c with (readpast) where c.UID_DialogTable = @uid_Dialogtable and Columnname = 'XObjectKey' )  begin select @TriggerBody = @TriggerBody + N'  -- select @ I s S i m u l a t i o n M o d e   =   d b o . Q B M _ F G I I s S i m u l a t i o n M o d e()  -- ist jetzt schon im declare
61	if @IsSimulationMode = 1
62	 begin
63		delete @SimulationModeBuffer
64
65		insert into @SimulationModeBuffer (operation, BaseObjectType, ColumnName, Objectkey, OldValue) 
66						select ''D'', '''
67 + @Table + N''', '''', deleted.XObjectKey , N''''
68						 from deleted
69
70		exec QBM_PFESimulationFill @SimulationModeBuffer
71
72	 end 
73' end    if @operation
74 in ( 'Insert', 'Update') begin delete  @Relations  insert into @Relations (Ident1 , UID1 ) select  case r.GenerateChild when 'TIR' then case @Operation
75 when 'insert' then 'TII' else 'TIR' end else r.generateChild end as fragment , r.UID_QBMRelation from QBMRelation r with (readpast) join DialogColumn 
76c with (readpast) on r.UID_ChildColumn = c.UID_DialogColumn where c.UID_DialogTable = @uid_Dialogtable and r.GenerateChild > ' ' and r.GenerateChild like
77 'T%'  order by r.UID_QBMRelation  select @UID_QBMRelation = '#' while @UID_QBMRelation > ' ' begin select @UID_QBMRelation = null select top 1 @UID_QBMRelation
78 = r.UID1  , @fragment = r.Ident1  from @Relations r order by r.Int1  if @UID_QBMRelation is null begin continue end delete  @Relations  where UID1  = 
79@UID_QBMRelation  select @TriggerBody = @Triggerbody + dbo.QBM_FSQFKCheckFix (@UID_QBMRelation, @Fragment, @Operation) end  end  if @operation in ( 'Delete'
80) begin delete  @Relations  insert into @Relations (Ident1 , UID1 ) select r.Generateparent, r.UID_QBMRelation from QBMRelation r with (readpast) join