Database structure synchronization: using DDL triggers to record database structure changes-mysql tutorial

Requirement: when developing multi-person collaborative projects, it is generally necessary to use multiple databases at the same time. common situations include: a developer's database (development database ), if a tester uses a database (test database) and a database (formal database) that is officially open to the customer, the synchronization of data structures between the three databases will become a problem: when

Requirement: when developing multi-person collaborative projects, it is generally necessary to use multiple databases at the same time. common situations include: a developer's database (development database ), if a tester uses a database (test database) and a database (formal database) that is officially open to the customer, the synchronization of data structures between the three databases will become a problem: when

Requirements:

When developing projects with multi-person collaboration, you usually need to use multiple databases at the same time.

Common situations include:

A developer's database (development database ),

Database used by a tester (test database ),

A database (formal database) officially opened to the customer ),

The data structure synchronization between these three databases will become a problem.

For example, when developer A adds A table to the "development database", developer B modifies A table...

These database structure changes must be synchronized to the "test database" and "official Database ".

However, it is easy to make mistakes when you record database structure changes...

What do you do?

Thinking process:

I used to see a post on the database master mongojian (not found ),

But after trying, I found that exceptions may occur in many cases.

Later, I saw an article on Artech, a top member of WCF.

SQLCDC is a powerful tool for tracking changes in each business operation data

This article is a log that records CURD operations on database tables.

Not table structure

Later, I asked Artech my questions in my article comments,

Get the DDL Trigger answer.

Write down and share

Code and explanation:

USE MRLH_CM; GO -- create table LogTable (DB_User nvarchar (200), EventType nvarchar (200), SQLString nvarchar (2000), ChangeTime datetime) that records database structure changes ); gocreate trigger LogTrigger on database for DROP_TABLE, ALTER_TABLE, CREATE_TABLEASDECLARE @ data XMLSET @ data = EVENTDATA () INSERT LogTable (DB_User, EventType, SQLString, ChangeTime) VALUES (CONVERT (nvarchar (100 ), CURRENT_USER), @ data. value ('(/EVENT_INSTANCE/EventType) [1]', 'nvarchar (100) '), @ data. value ('(/EVENT_INSTANCE/TSQLCommand) [1]', 'nvarchar (2000) '), GETDATE (); GO

The process is similar to that of creating a trigger.

Let's talk about two of them.

1. FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
Only these events are recorded here.

Use

FOR DDL_DATABASE_LEVEL_EVENTS

For more information, visit

Http://msdn.microsoft.com/en-us/library/ms186456 (SQL .90). aspx

2. SET @ data = EVENTDATA ()
EVENTDATA () is the method of the database itself

Returns information about server or database events (in XML format)

EVENTDATA returns data only when EVENTDATA is directly referenced in the DDL or login trigger.

If EVENTDATA is called by other routines (even if these routines are called by DDL or login triggers), NULL is returned.

@ Data. value ('(/EVENT_INSTANCE/EventType) [1]

This is to use XQUERY to retrieve XML data.

For detailed XQUERY tutorial, please refer to here

Http://www.w3school.com.cn/xquery/index.asp

Note:

-- To delete a TABLE LogTable, you must first delete this trigger drop trigger LogTriggeron databaseGO -- delete the table drop table LogTableGO

The above code has passed the test in MSSQLSERVER2008.

Other databases are not tested