SQL Server DDL triggers (Trigger)--Introduction

SQL Server DDL triggers (Trigger)--Introduction

Triggers (Trigger) are used only on specific objects, such as data tables. When the INSERT, Delete, update syntax for data Maintenance language (DML) works on these objects, the corresponding trigger is automatically called by the system, and instead of Trigger is added in SQL Server 2000. The trigger replaces the added, modified, and deleted syntax that was intended to be performed, and can be set on the view. Since instead of Trigger is triggered before the DML syntax really alters the data written to the transaction log, it is also known as before Trigger, and we generally write triggers after a transaction has already occurred and written to the log, which is known as after Trigger.

After SQL Server 2005, the application polygons for the triggers are extended, and the data definition language (DDL) triggers feature is added. The execution of the DDL syntax, and even the server instance/database execution related stored procedures, events can also start the trigger. Can be used to standardize, track, and monitor changes to the database schema, such as when creating, modifying, or deleting a data table, what conditions are required to allow changes to the data table, and what is changed when the definition of which data table is made. If a DML trigger is a data-table-level program, a DDL trigger is a database-level or server-level program that triggers a DDL trigger for the syntax of the database level.

You might trigger DDL triggers for database-level syntax, such as create_table, or server-level syntax, such as Create_login, by executing ROLLBACK TRANSACTION syntax within a trigger, The ability to roll back the DDL syntax that was performed by the original user is as if the syntax was not executed. As with DML triggers, executing a single DDL may trigger multiple DDL triggers at the same time, but we cannot control the order in which they are executed, so there is no sequential dependency between DDL triggers.

The syntax for creating DDL triggers is also create TRIGGER, which is defined as follows:

CREATE TRIGGER <trigger name>

On {All SERVER | DATABASE}

[With <ddl_trigger_option> [..., N]]

{for | After} {event_type | event_group} [,... N]

As {SQL [... n] | EXTERNAL NAME <method Definition>}

When writing a DDL trigger, you specify that the target is on database or on all SERVER, and that the events that cause the trigger are not the same, for example, the former is the object within the definition database, the event_type option has create_table, Create_ USER, Create_schema and so on, while the latter is the entire server as the goal, its event_type have create_login, Create_endpoint and so on. As you can see, to list the DDL syntax that the trigger is targeting, it is an underscore (_) connection between the syntax. If you do not want to list event_type, the system has pre-categorized various DDL grammars, for example, ddl_server_security_events represents all kinds of server-targeted DDL syntax events, and Ddl_table_veiw_ Events represents DDL events for data tables, views, indexes, and statistics. As for the Event_type and Event_group, and the relationship between the tree structure, you can refer to the detailed list of Books Online.

Finally, when you specify the actual content definition of a DDL trigger, it can be a syntax written in T-SQL, or it can be referenced. NET to write the method within the component (Assembly).

When a DML trigger executes, it can refer to the deleted and inserted temporary tables created by the SQL Server engine, and when the DDL trigger executes, it can call the EVENTDATA () system function to obtain the system information associated with the execution of the DDL trigger. The function returns data in XML format and can parse its contents with a T-SQL XQuery.

This article is from the SQL Server deep dives blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1598184

SQL Server DDL triggers (Trigger)--Introduction