At work, we sometimes need to analyze an existing software database structure, simply, just want to know two points.
1. Which table 2 is saved in various data, in what circumstances, the data in the table will update
Below I write your method, if you have a better way, please discuss with me.
1. Establish a corresponding update table for each business table in the database When the data of the corresponding business table is updated, the trigger will write the update type and record to the corresponding update table update table. In addition to the corresponding business table All fields have also added three fields
(1) A self-increasing ID (2) update type (I insert; D deletion; U update) (3) Update time
2. Create a total update table in the database
When the data of any business table is updated, the trigger writes the update type and table name into the total update table, and the role is to quickly find the current data update table total update table has four fields.
(1) A self-increasing ID (2) update type (I insert; D Delete; U Update) (3) Update Table Name (4) Update Time
3. Establish three triggers for each business table, respectively, insert, delete, modify three operations, and write records, updated records, deleted records, inserted records, and inserted records, updated records, deleted records, and inserted. Enter the corresponding update table
To this end, I have written two stored procedures for SQL Server 2000. If your database is not SQL Server 2000, you can also refer to the newly established table and trigger and the original table and trigger in the database, Adopting a measuring method, such as a table name name User, the corresponding update table is the Users suffix, when the error is in _1234567, the table name of the update table is Users_1234567
Note: (1) All must be DBO (2) Original Watch No identification field (3) Unable to process text, ntext or image field
Below is the script of the stored procedure p_analysis and p_clearup
/ * ================================================================================================================================================================ ========================= stored procedure P_analysis effect To analyze a total update table UPDATE suffix suffix to establish an update form for each table Table name suffix creates three triggers for each table TR_ table name _ trigger type (i: insert D: delete u: update) suffix
Enter parameter @postfix to avoid analytical table and service table name duplicate, analyze triggers and re-triggers Reutect-use Exec P_analysis '_1234567' =================== ============================================================================================================================================================================================================= ======== * / create procedure p_analysis @postfix char (8) AS - testing if the database original object name (field name) Repeat IF EXISTS (Select * from sysobjects where right (Name, 8) = @ postfix) or exists (Name, 8) = @ postfix) Print 'object name Repeat, use different suffix name' else beginning - establish an update record table Declare @tablename for each table nvarchar (128) declare @columns varchar (8000) declare cur INSENSITIVE cursor FOR SELECT name from sysobjects where xtype = 'U' and status> 0 OPEN cur FETCH NEXT fROM cur INTO @TableName while (@@ fetch_status = 0) BEGIN set @ Column = '' - Establish Update Table EXEC ('SELECT * INTO' @ Tablename @ Postfix 'from' @ Tablename 'Where 1 = 0') - Add three fields EXEC ('ALTER TABLE' @ TABLENAME @ Postfix 'add id' @ postfix 'int identity (1, 1), oprtype' @ postfix 'char (2), oprtime' @ postfix 'DateTime default getdate () - for each Business table build three triggers Select @ columns = @ columns ',' name from syscolumns where id = Object_id (@tablename)
- Insert trigger EXEC ( 'CREATE TRIGGER TR _' @ TableName '_ I' @ postfix 'ON' @ TableName 'FOR INSERT AS' 'INSERT UPDATE' @ postfix @ postfix '(TableName, OprType)' 'VALUES (' '' @ TableName '', 'I' ')' 'INSERT' @ TableName @ Postfix '(OPRTYPE' @ Postfix @ Column ')' 'SELECT' 'I' '' @ columns 'FROM INSERTED') - delete triggers EXEC ( 'CREATE tRIGGER TR _' @ TableName '_ D' @ postfix 'ON' @ TableName 'FOR dELETE AS' 'INSERT UPDATE' @ postfix @ postfix '(Tablename, OPRTYPE)' 'VALUES (' ' @ TableName ' ',' 'D' ')' 'INSERT' @ TableName @ Postfix '(Oprtype' @ Postfix @ Column ')' 'SELECT' '' '' @ Column 'from deleted')
- update trigger EXEC ( 'CREATE TRIGGER TR _' @ TableName '_ U' @ postfix 'ON' @ TableName 'FOR UPDATE AS' 'INSERT UPDATE' @ postfix @ postfix '(TableName, OprType)' 'VALUES (' '' @ Tablename '', 'U' ')' 'INSERT' @ Tablename @ Postfix '(Oprtype' @ Postfix @ Column ')' 'SELECT' 'Bu' '' @ columns 'fROM DELETED' 'INSERT' @ TableName @ postfix '(OprType' @ postfix @ columns ')' 'SELECT' 'AU' '' @ columns 'fROM INSERTED') fetch next from cur into @Tablename End Close Cur deallocate Cur - Create total record update table EXEC ('Create Table Update' @ Postfix @ Postfix "(ID Numeric (18, 0) Identity (1, 1), Tablename Varchar (256), Oprtype Char (1) OPRTIME DATETIME DEFAULT GETDATE ()) ') EndGo
/ * ================================================================================================================================================================ ================== stored procedure p_clearup
Role: Clear new table / trigger
Enter parameters: @postfix default _1234567
Example: Use Exec P_clearup '_1234567' ========================================== ===================================== * / create procedure p_clearup @ postfix char (8) = '_ 1234567'as - Delete the total update table
