CREATE TABLE [dbo].[luBarcodeSetting]
(
[BarCodeSettingID] [int] NOT NULL,
[LineOfBusinessID] [int] NOT NULL,
[PrintQuantity] [int] NULL,
[RecordingUserName] [varchar](20) NOT NULL,
[RecordingTime] [varchar](20) NOT NULL,
[Rowversion] [int] NOT NULL,
CONSTRAINT [PK_luBarcodeSetting] PRIMARY KEY CLUSTERED 
(
[BarCodeSettingID] ASC
) ON [PRIMARY]
 
 
CREATE TABLE [dbo].[z_luBarcodeSetting](
[ActionDML] [varchar](1)  NOT NULL,
[BarCodeSettingID] [int] NOT NULL,
[LineOfBusinessID] [int] NOT NULL,
[PrintQuantity] [int] NULL,
[RecordingUserName] [varchar](20)  NOT NULL,
[RecordingTime] [varchar](20)  NOT NULL,
[Rowversion] [int] NOT NULL,
CONSTRAINT [PK_z_luBarcodeSetting] PRIMARY KEY CLUSTERED 
(
[BarCodeSettingID] ASC,
[Rowversion] ASC
) ON [PRIMARY]


Create TRIGGER [TR_UD_luBarcodeSetting] ON luBarcodeSetting
INSTEAD OF DELETE, UPDATE
    AS
      DECLARE @ErrorMessage VARCHAR(4000)
      DECLARE @ErrorSeverity INT
      DECLARE @ErrorState INT
 
      BEGIN  TRY
                  INSERT INTO z_luBarcodeSetting
                                    (ActionDML,BarcodeSettingID,LineOfBusinessID,PrintQuantity,RecordingUserName,RecordingTime,RowVersion)
                  SELECT CASE WHEN deleted.BarcodeSettingID IS NOT NULL AND inserted.BarcodeSettingID IS NOT NULL
                                    THEN CASE WHEN deleted.ROWVERSION = -1 THEN 'M' 
                                                  WHEN deleted.ROWVERSION = 0 THEN 'I'
                                                  ELSE 'U'
                                           END                     
                                     WHEN deleted.BarcodeSettingID IS NOT NULL AND inserted.BarcodeSettingID IS NULL 
                                     THEN 'D'
                           END as ActionDML,
                           deleted.BarcodeSettingID,deleted.LineOfBusinessID,deleted.PrintQuantity,deleted.RecordingUserName,deleted.RecordingTime,deleted.RowVersion
                           FROM deleted
                  LEFT JOIN luBarcodeSetting on luBarcodeSetting.BarcodeSettingID = deleted.BarcodeSettingID 
                  LEFT JOIN inserted on luBarcodeSetting.BarcodeSettingID = inserted.BarcodeSettingID 
                  BEGIN  
                        IF (SELECT COUNT(*) FROM inserted) = 0
                               Delete luBarcodeSetting where BarcodeSettingID in (select BarcodeSettingID from deleted) 
                        ELSE 
                               UPDATE luBarcodeSetting set LineOfBusinessID = inserted.LineOfBusinessID
                                                        ,PrintQuantity = inserted.PrintQuantity
                                                        ,RecordingUserName = inserted.RecordingUserName
                                                        ,RecordingTime = inserted.RecordingTime
                                                        ,RowVersion=inserted.RowVersion 
                              FROM luBarcodeSetting, inserted
                              WHERE luBarcodeSetting.BarcodeSettingID = inserted.BarcodeSettingID
                  END
     END TRY 
     BEGIN CATCH
         SELECT @ErrorMessage = 'TR_UD_luBarcodeSetting:' + ERROR_MESSAGE(),
                  @ErrorSeverity = ERROR_SEVERITY(),
                  @ErrorState = ERROR_STATE();
         IF @@TRANCOUNT > 0
         BEGIN
          RAISERROR (@ErrorMessage, 16,1) with nowait
              ROLLBACK TRAN
            RETURN
         END
     END CATCH

DECLARE Table_Trigger CURSOR 
FOR
SELECT name TableName FROM sys.tables
 
DECLARE @table_name SYSNAME
DECLARE @Primary_key SYSNAME
DECLARE @Trigger VARCHAR(MAX)
 
-- Loop through All tables 
 
OPEN Table_Trigger
FETCH NEXT FROM Table_Trigger INTO @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
      SET NOCOUNT ON
      SELECT @Primary_key = COL_NAME(ic.OBJECT_ID,ic.column_id)
      FROM sys.indexes AS i
      INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
      WHERE i.is_primary_key = 1 and i.object_id = Object_id(@table_name)
 
SET @Trigger = '-------------------'+@table_name+'
 
Create TRIGGER [TR_UD_'+@table_name+'] ON '+@table_name+
                        '
                        INSTEAD OF DELETE, UPDATE
                        AS
                          DECLARE @ErrorMessage VARCHAR(4000)
                          DECLARE @ErrorSeverity INT
                          DECLARE @ErrorState INT
                   
                          BEGIN  TRY
                                            INSERT INTO z_'+@table_name+'
                                                                     ([ActionDML],'
 
-- Loop through All Columns for a table 

 DECLARE Table_Column_Tri CURSOR 
 FOR 
 SELECT column_name
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = @table_name
 
 DECLARE @column_name SYSNAME
 DECLARE @Delete VARCHAR(MAX)
 DECLARE @select VARCHAR(MAX)
 DECLARE @Insert VARCHAR(MAX)
 DECLARE @Pos int SET @Pos = 1
 
 OPEN Table_Column_Tri
 FETCH NEXT FROM Table_Column_Tri INTO @column_name
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
 
 IF (@select IS NULL And @Pos = 1)
      SET @select =  QUOTENAME(@column_name)
   ELSE
      SET @select = @select + ' , ' +QUOTENAME(@column_name)
 
   IF (@Delete IS NULL)
      SET @Delete =  'Deleted.'+ QUOTENAME(@column_name)
   ELSE
      SET @Delete = @Delete + ' , ' + 'Deleted.'+ QUOTENAME(@column_name)
 
   IF (@Insert IS NULL AND @column_name <> @Primary_key)
      SET @insert =  + QUOTENAME(@column_name)+ ' = inserted.'+QUOTENAME(@column_name)
   ELSE
      SET @insert = @insert + ' , ' + QUOTENAME(@column_name)+ ' = inserted.'+QUOTENAME(@column_name)
 
 FETCH NEXT FROM Table_Column_Tri INTO @column_name
 
 END
 CLOSE Table_Column_Tri
 DEALLOCATE Table_Column_Tri
 
 SET @TRIGGER = @TRIGGER +@select+')
                              SELECT CASE WHEN deleted.'
+@Primary_key+' IS NOT NULL AND inserted.'+@Primary_key+' IS NOT NULL
                                    THEN CASE WHEN deleted.ROWVERSION = -1 THEN '+''''+'M'+''''+' 
                                                  WHEN deleted.ROWVERSION = 0 THEN '+''''+'I'+''''+'
                                                  ELSE '+''''+'U'+''''+'
                                           END                     
                                    WHEN deleted.'+@Primary_key+' IS NOT NULL AND inserted.'+@Primary_key+' IS NULL 
                                     THEN '+''''+'D'+''''+'
                           END as ActionDML,
                           '+@Delete+'
                           FROM deleted
                  LEFT JOIN '+@table_name+' on '+@table_name+'.'+@Primary_key+' = deleted.'+@Primary_key+' 
                  LEFT JOIN inserted on '+@table_name+'.'+@Primary_key+' = inserted.'+@Primary_key+' 
                  BEGIN  
                        IF (SELECT COUNT(*) FROM inserted) = 0
                               Delete '+@table_name+' where '+@Primary_key+' in (select '+@Primary_key+' from deleted) 
                        ELSE 
                               UPDATE '+@table_name+' set '+@Insert+'
                               FROM '+@table_name+', inserted
                               WHERE '+@table_name+'.'+@Primary_key+' = inserted.'+@Primary_key+'
                  END
     END TRY 
     BEGIN CATCH
         SELECT @ErrorMessage = '+''''+'TR_UD_'+@table_name+':'+''''+'+ ERROR_MESSAGE(),
                  @ErrorSeverity = ERROR_SEVERITY(),
                  @ErrorState = ERROR_STATE();
         IF @@TRANCOUNT > 0
         BEGIN
         RAISERROR (@ErrorMessage, 16,1) with nowait
              ROLLBACK TRAN
           RETURN
         END
     END CATCH'
 
 
---Print Trigger  
  
   SET @Delete = Null
   SET @select = Null
   SET @Insert = Null
   PRINT @TRIGGER
   PRINT ''
  FETCH NEXT FROM Table_Trigger INTO @table_name
END
 
CLOSE Table_Trigger
DEALLOCATE Table_Trigger