EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@TableName	nvarchar	IN
@Operation	nvarchar	IN

Definition

/*ERLON CESAR LIMA DE FREITAS - ZB.2020.06_01 - OC.:DFRWFOUNDATION-12777 - 21/08/2020 VERSAO.: 12.1.29 - EQUIPE:INOV  */
CREATE PROCEDURE [dbo].[p_SoftDelete](
@TableName sysname = null,
@Operation nvarchar(3) = '' /* CTT= Create Table Trigger | DT=Drop Trigger */
)
AS
DECLARE
    @ListOfCreateFields nvarchar(max) = '',
	@ListOfFields nvarchar(max) = '',
    @SqlCommand nvarchar(max),
    @ColumnName varchar(128),
    @ColumnType varchar(128),
    @ColumnLength INT,
    @ColumnNullable int,
    @ColumnCollation varchar(128),
    @ColumnPrecision tinyint,
    @ColumnScale tinyint,
    @ColumnUserType int,
	@TriggerHeader nvarchar(max),
	@TriggerName varchar(128),
	@TableSoftDeleteName varchar(128),
	@Owner varchar(64) = schema_name()
	SET @TriggerName = 'tg_' + @TableName +'_SoftDelete';--Formata o nome da trigger
    --PRINT (@TriggerName)
    SET @TableSoftDeleteName = @TableName + '_SoftDelete';--Formata o nome da tabela softDelete
    --PRINT (@TableSoftDeleteName)
	IF @Operation = 'DT'
	BEGIN
	  set @SQLCommand = 'IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = ''' + @TriggerName + ''')' + char(10)
	  set @SQLCommand += 'DROP TRIGGER ' + @Owner + '.' + @TriggerName
	  exec sp_executesql @SQLCommand
	END
    IF @Operation = 'CTT'
    BEGIN
      DECLARE TableColumns CURSOR READ_ONLY FOR
        select col.name, typ.name as typename, col.max_length, col.is_nullable, col.collation_name,
		       col.precision, col.scale, col.system_type_id-col.user_type_id
        from sys.tables tbl(nolock)
          inner join sys.columns col(nolock)
          inner join sys.types typ(nolock) on  col.user_type_id = typ.user_type_id
          and col.name <> 'sysname'
          on tbl.[object_id] = col.[object_id]
          where  OBJECT_NAME(tbl.[object_id]) = @TableName
                 and tbl.schema_id = SCHEMA_ID(@Owner)
                 and col.is_rowguidcol = 0
                 and col.is_computed = 0
                 and objectproperty(tbl.[object_id], N'IsUserTable') = 1
                 and typ.name not in (select name from sys.types where system_type_id in (select system_type_id from sys.types where system_type_id = user_type_id and name in ('TEXT','NTEXT','IMAGE','TIMESTAMP')))
				 and col.name in ('RECCREATEDBY', 'RECCREATEDON', 'RECMODIFIEDBY', 'RECMODIFIEDON')
        union
        select col.name, typ.name as typename, col.max_length, col.is_nullable, col.collation_name,
		       col.precision, col.scale, col.system_type_id-col.user_type_id
        from sys.tables tbl(nolock)
          inner join sys.columns col(nolock)
          inner join sys.types typ(nolock) on  col.user_type_id = typ.user_type_id
          and col.name <> 'sysname'
          on tbl.[object_id] = col.[object_id]
          where  OBJECT_NAME(tbl.[object_id]) = @TableName
                 and tbl.schema_id = SCHEMA_ID(@Owner)
                 and col.is_rowguidcol = 0
                 and col.is_computed = 0
                 and objectproperty(tbl.[object_id], N'IsUserTable') = 1
                 and typ.name not in (select name from sys.types where system_type_id in (select system_type_id from sys.types where system_type_id = user_type_id and name in ('TEXT','NTEXT','IMAGE','TIMESTAMP')))
                 and col.name in (SELECT COLUMN_NAME -- Somente chaves primárias.
                                  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                                  WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
                                  AND TABLE_NAME = @TableName
								  AND TABLE_SCHEMA = @Owner)
      OPEN TableColumns
         FETCH NEXT FROM TableColumns
         INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
         WHILE @@FETCH_STATUS = 0
         BEGIN
		   SET @ListOfFields += ',[' + @ColumnName + ']'
           SET @ListOfCreateFields += CHAR(10) + ',[' + @ColumnName + '] ' + @ColumnType + ISNULL(CASE WHEN @ColumnType IN ('NUMBER','NUMERIC','DECIMAL') THEN '(' + CAST(@ColumnPrecision AS VARCHAR) + ',' + CAST(@ColumnScale AS VARCHAR) + ')' WHEN @ColumnType LIKE 'DATE%' THEN '' WHEN @ColumnType LIKE 'MONEY' THEN '' WHEN @ColumnType LIKE 'BIT' THEN '' WHEN @ColumnType LIKE '%INT%' THEN '' WHEN @ColumnType LIKE '%BINARY%' THEN +'(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(@ColumnLength AS VARCHAR) END + ')' WHEN @ColumnType like '%CHAR%' AND @ColumnUserType = 0 THEN '(' + CASE WHEN @ColumnLength = -1 THEN 'MAX' ELSE CAST(CASE WHEN @ColumnLength = 8000 AND @ColumnType = 'NVARCHAR' THEN 4000 ELSE @ColumnLength END AS VARCHAR) END + ') COLLATE ' + @ColumnCollation WHEN @ColumnUserType != 0 THEN '' ELSE '' END, '') + CASE WHEN @ColumnNullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
           FETCH Next FROM TableColumns
           INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale, @ColumnUserType
         END
      CLOSE TableColumns
      DEALLOCATE TableColumns
      set @SQLCommand =  'IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''' + @TableSoftDeleteName + ''' and SCHEMA_NAME(schema_id) = ''' + @owner + '''' + ')' + CHAR(10)
      set @SQLCommand += 'BEGIN' + CHAR(10)
      set @SQLCommand += 'CREATE TABLE ' + @Owner + '.' + @TableSoftDeleteName + ' ('
      set @SQLCommand += 'SOFTDELETEID BIGINT IDENTITY NOT NULL,' + CHAR(10)
      set @SQLCommand += 'DELETED_DATE' + ' DATETIME NOT NULL' + CHAR(10)
      set @SQLCommand += @ListOfCreateFields + CHAR(10)
      set @SQLCommand += 'PRIMARY KEY CLUSTERED(SOFTDELETEID)) ON [PRIMARY]' + CHAR(10)
      set @SQLCommand += 'END' + CHAR(10)
      exec (@SQLCommand)
	  --PRINT @SQLCommand
	  set @SQLCommand =  'IF EXISTS (SELECT 1 FROM SYS.OBJECTS WHERE name = ''' + @TriggerName + ''')' + char(10)
	  set @SQLCommand += 'DROP TRIGGER dbo.' + @TriggerName
	  exec sp_executesql @SQLCommand
	  set @SQLCommand =   'CREATE TRIGGER ' + @Owner + '.' + @TriggerName + ' ON [' + 'dbo' + '].[' + @TableName + ']' + CHAR(10)
	  set @SQLCommand += 'FOR DELETE NOT FOR REPLICATION' + CHAR(10)
	  set @SQLCommand += 'AS' + CHAR(10)
	  set @SQLCommand += 'declare @rowsaffected int = @@ROWCOUNT, @eventData xml = eventdata()' + CHAR(10)
	  set @SQLCommand += 'SET CONCAT_NULL_YIELDS_NULL ON' + CHAR(10)
	  set @SQLCommand += 'SET NOCOUNT ON SET ANSI_PADDING ON SET ARITHABORT ON' + CHAR(10)
	  set @SQLCommand += 'begin' +CHAR(10)
	  set @SQLCommand += 'INSERT INTO [' + @Owner + '].' + @TableSoftDeleteName + ' (DELETED_DATE ' +  @ListOfFields + ')' + CHAR(10)
	  set @SQLCommand += 'SELECT getdate()' + @ListOfFields + ' FROM Deleted' + CHAR(10)
	  set @SQLCommand += 'END'
	  --PRINT (@SQLCommand)
	  EXEC (@SQLCommand)
    END