Tsql basics-DDL triggers

A ddl trigger is a special trigger that is triggered in response to a Data Definition Language (DDL) statement. They can be used to perform management tasks in the database, such as review and standardize database operations. It is only supported after sqlserver2005. The following example uses a DDL trigger to implement database version control.

1. Create a database dbcontroller supporting the control to record the database change history.
Database Core table t_version_details

 

2. triggersCodeTrigger. SQL

Create trigger into create_table, alter_table, drop_table, create_function, alter_function, drop_function, create_index, alter_index, drop_index, indexes, tables, tables, create_trigger, alter_trigger, drop_trigger, create_view, alter_view, drop_viewas set nocount on declare @ data as XML declare @ clientuser as nvarchar (128) Declare @ spid as nvarchar (128) Declare @ servername as nvarchar (128) declare @ dbname as nvarchar (128) Declare @ dbid as int declare @ objname as nvarchar (512) Select @ DATA = eventdata () Select @ spid = @ data. value ('(/event_instance/spid) [1]', 'nvarchar (128)') Select @ servername = @ data. value ('(/event_instance/servername) [1] ', 'nvarchar (256)') Select @ dbname = @ data. value ('(/event_instance/databasename) [1] ', 'nvarchar (128)') Select @ objname = @ data. value ('(/event_instance/objectname) [1]', 'nvarchar (128) ') Select @ dbid = dbid from sys. sysdatabases where name = @ dbname/* Get the machine name of the client */select @ clientuser = hostname from master .. sysprocesses where spid = @ spid -- add version record insert into dbcontroller. DBO. t_version_details (post_computer_name, sys_dbid, uid, [schema], posttime, eventtype, objecttype, objectname, commandtext, remark) values (@ clientuser, @ dbid, @ data. value ('(/event_instance/loginname) [1]', 'nvarchar (256) '), @ data. value ('(/event_instance/username) [1] ', 'nvarchar (256)'), @ data. value ('(/event_instance/posttime) [1]', 'datetime'), @ data. value ('(/event_instance/eventtype) [1]', 'nvarchar (128) '), @ data. value ('(/event_instance/objecttype) [1]', 'nvarchar (128) '), @ objname, @ data. value ('(/event_instance/tsqlcommand/commandtext) [1]', 'nvarchar (max )'),'')

 

3. Execute the preceding statements on the target database.

4. If you want to disable the execution statement

 
Disable trigger trig_dbversioncontroller on database;