· 8 years ago · Feb 19, 2018, 01:56 AM
1CREATE Procedure usp_GenerateTrigger @TableName sysname AS BEGIN DECLARE @Body_G nvarchar(1000), @Body_I nvarchar(4000) , @Body_U nvarchar(4000), @Body_D nvarchar(4000) DECLARE @PreCond nvarchar(200), @EquCond nVarchar(1000), @InEquCond nvarchar(1000) DECLARE @Cond_U nvarchar(1000), @Cond_D nvarchar(1000), @Keys nvarchar(900) DECLARE @Table sysname, @Schema sysname DECLARE @Column sysname, @DataType Sysname, @IsIdentity bit SELECT @Table =PARSENAME(@TableName,1) , @Schema=ISNULL(PARSENAME(@TableName, 2), CURRENT_USER), @IsIdentity=0 SELECT @Body_G='IF EXISTS (SELECT * FROM SysObjects '+char(13) + ' WHERE Type = ''TR'' AND id = Object_id('''+@Schema+'.insTR_Audit_'+ @Table+''')) '+char(13) + ' Drop Trigger '+@Schema+'.insTR_Audit_'+@Table+char(13) + ' IF EXISTS (SELECT * FROM SysObjects '+char(13) + ' WHERE Type = ''TR'' AND id = Object_id('''+@Schema+'.updelTR_Audit_' + @Table+''')) '+char(13) + ' Drop Trigger '+@Schema+'.updelTR_Audit_'+@Table+char(13) + ' IF EXISTS (SELECT * FROM SysObjects '+char(13) + ' WHERE Type = ''TR'' AND id = Object_id('''+@Schema+'.delTR_Audit_' + @Table + ''')) '+char(13) + ' Drop Trigger '+@Schema+'.delTR_Audit_'+@Table+char(13) Select @Body_I ='', @Body_U ='', @Body_D ='', @EquCOnd ='', @InEquCond ='', @PreCond='', @Cond_U='', @Cond_D ='', @Keys ='' select @Body_I ='Create Trigger insTR_Audit_'+@Table +' ON '+ @TableName +' For Insert As ', @Body_U='Create Trigger updelTR_Audit_'+@Table +' ON '+@TableName+ ' For Update As ', @Body_d='Create Trigger delTR_Audit_'+@Table+' ON '+@TableName+' For Delete As ' Select @Keys= @Keys +', ['+ COL_Name(Object_ID(@TableName), k.COLID)+']' from sysIndexKeys k, sysindexes I Where i.id=object_ID(@TableName) and i.id = k.id and i.indid=k.indid and i.status & 0x800 =0x800 order by keyno --Select @keys IF LEN(@Keys)>1 select @Keys = SUBSTRING(@Keys, 3, LEN(@Keys)-2) ELSE BEGIN PRINT 'No Primary Keys are available to handle the operation' RETURN END IF CharIndex(', [', @Keys, 2)=0 --there is only one field now check whether it is identity BEGIN IF Exists(Select * from syscolumns where name =Substring(@Keys,2, LEn(@Keys)-2) and autoval is not null) Set @IsIdentity=1 END Declare FieldList Cursor FOR Select COLUMN_NAME, DATA_TYPE from Information_Schema.Columns Where TABLE_Name =@Table and TABLE_SCHEMA=@Schema and Data_Type <> 'TimeStamp' Open FieldList FETCH NEXT FROM FieldList INTO @Column, @DataType WHILE @@FETCH_STATUS=0 BEGIN IF CHARINDEX(''+@Column+'', @Keys)>0 SELECT @Cond_U =@Cond_U+ '+'' AND ' +@Column +' = ''+'+ CASE WHEN @DataType in ('Smalldatetime', 'DateTime') THEN ' ''''''''+ Convert(Varchar(30), I.'+ @Column +', 113)+'''''''' ' WHEN @DataType in ('bigint','bit','BINARY', 'varbinary', 'int', 'smallint', 'tinyint', 'real', 'decimal', 'float', 'money', 'numeric') THEN ' Convert(Varchar(35), I.'+ @Column +') ' ELSE ' ''''''''+I.'+ @Column + '+'''''''' ' END, @EquCond = @EquCond+ ' AND I.'+ @Column +' = D.'+@Column, @InEquCond = @InEquCond+ ' AND I.'+ @Column +' <> D.'+@Column, @PreCond = @preCond+' AND Update('+ @Column+')', @Cond_d =@Cond_d+ '+'' AND ' +@Column +' = ''+'+ CASE WHEN @DataType in ('Smalldatetime', 'DateTime') THEN ' ''''''''+ Convert(Varchar(30), D.'+ @Column +', 113)+'''''''' ' WHEN @DataType in ('bigint','bit','BINARY', 'varbinary', 'int', 'smallint', 'tinyint', 'real', 'decimal', 'float', 'money', 'numeric') THEN ' Convert(Varchar(35), D.'+ @Column +') ' ELSE ' ''''''''+D.'+ @Column + '+'''''''' ' END FETCH NEXT FROM FieldList INTO @Column, @DataType END CLOSE FieldList deallocate FieldList IF LEN(@Cond_D)>7 SELECT @Cond_D = ''''+SUBSTRING(@Cond_D, 8, LEN(@Cond_D )-7) IF LEN(@Cond_U)>7 SELECT @Cond_U = ''''+SUBSTRING(@Cond_U, 8, LEN(@Cond_U )-7) IF LEN(@PreCond)>5 SELECT @PreCond= 'IF '+ SUBSTRING(@PreCond, 6, LEN(@PreCond)-5) IF LEN(@EquCond)>5 SELECT @EquCond= ' WHERE '+ SUBSTRING(@EquCond, 6, LEN(@EquCond)-5) IF LEN(@InEquCond)>5 SELECT @InEquCond= 'WHERE '+ SUBSTRING(@InEquCond, 6, LEN(@InEquCond)-5) select @Body_I = @Body_I + 'IF EXISTS (Select * from Inserted) Insert into LogTable (LOG_Table, LOG_Condition, LOG_Action) Select '''+@Table+''', '+@Cond_U+',''I'' from Inserted I ' IF @IsIdentity=0 SELECT @Body_U =@Body_U + 'IF EXISTS (Select * from Inserted) '+@PreCond +' Insert into LogTable (LOG_Table, LOG_Condition, LOG_ConditionD, LOG_Action) Select '''+@Table+''', '+@Cond_U+','+@Cond_D+',''U'' from Inserted I, Deleted D '+@InEquCond+' ELSE Insert into LogTable (LOG_Table, LOG_Condition, LOG_ConditionD, LOG_Action) Select '''+@Table+''', '+@Cond_U+','+@Cond_D+',''U'' from Inserted I, Deleted D '+@EquCond Else SELECT @Body_U =@Body_U + 'Insert into LogTable (LOG_Table, LOG_Condition, LOG_ConditionD, LOG_Action) Select '''+@Table+''', '+@Cond_U+','+@Cond_D+',''U'' from Inserted I, Deleted D '+@EquCond Select @Body_D = @Body_D+'IF EXISTS (Select * from Deleted) Insert into LogTable (LOG_Table, LOG_ConditionD, LOG_Action) Select '''+@Table+''', '+@Cond_D+',''D'' from Deleted D ' Execute sp_ExecuteSQL @Body_G Execute sp_ExecuteSQL @Body_I Execute sp_ExecuteSQL @Body_U Execute sp_ExecuteSQL @Body_D END