SQL Server generates triggers for all user tables

The test is important. We can know which tables are affected by the current transaction-used to record when and what operations the user performs on the current table: update, insert, deletecreatetableTriggerRecord (operdtdatetime, -- trigger time opertpvarchar (10), -- operation type: update, insert, deleteope

The test is important. We can know which tables are affected by the current transaction-used to record when and what operations the user performs on the current table: update, insert, delete create table TriggerRecord (operdt datetime, -- trigger time opertp varchar (10), -- operation type: update, insert, delete ope

The test is important. We can know which tables are affected by the current transaction.

-- Used for recordUserWhen and what operations are performed on the current table: update, insert, and delete
Create table TriggerRecord
(
Operdt datetime, -- trigger time
Opertp varchar (10), -- operation type: update, insert, delete
Opertb varchar (50) -- table name
)
-- This table is used for savingGenerateOfTriggerStatement, which is executed cyclically During the Process
-- Because Sqlserver cannot execute multiple create trigger statements in a batch at the same time
Create table T (sqlTrigger varchar (500 ))
-- Circular execution is stored in the tableTriggerStored Procedures
Create proc loopExecTrigger
As
Begin
Declare @ SQL varchar (500)
Declare cur cursor for select sqlTrigger from T
Open cur
Fetch cur into @ SQL
While @ fetch_status = 0
Begin
Execute (@ SQL)
Fetch cur into @ SQL
End
Close cur
Deallocate cur
Delete T
End

-- UsedGenerateInsert statementTrigger, AndTriggerSave statement to table
Select 'insert into T values (''create trigger T _ '+ name + 'on' + name +' for insert as insert into TriggerRecord values (getdate (), ''' insert ''', ''' + name + ''''');'') 'From sysobjects where type = 'U' and name not in ('T', 'triggerrecord ')
-- Replace the aboveGenerateStatement.

-- UsedGenerateUpdate statementTrigger, AndTriggerSave statement to table
Select 'insert into T values (''create trigger T _ '+ name +' _ U on '+ name +' for update as insert into TriggerRecord values (getdate (), ''' update ''', ''' + name + ''''');'') 'From sysobjects where type = 'U' and name not in ('T', 'triggerrecord ')
-- Replace the aboveGenerateStatement.

-- UsedGenerateDelete statementTrigger, AndTriggerSave statement to table
Select 'insert into T values (''create trigger T _ '+ name +' _ D on '+ name +' for delete as insert into TriggerRecord values (getdate (), '''''delete''', ''' + name + ''''');'') 'From sysobjects where type = 'U' and name not in ('T', 'triggerrecord ')
-- Replace the aboveGenerateStatement.

-- Run the preceding statementGenerateStatement, and then execute the storageGenerateTriggerStored Procedures
Exec loopExecTrigger

--GenerateDelete allTriggerStatement
Select 'drop trigger' + name + ';' from sysobjects where type = 'tr' and name like 't_%'

Feng Libin's blog