Ms SQL SERVER: DDL triggers

Ms SQL Server: DDL trigger

DDL trigger (this article is on the Network)

1. DDL trigger event summary:
When creating an event notification to monitor and respond to activities in the database or server instance, you can specify the corresponding event type or event group.

events include:
ddl_trigger_events,
ddl_function_events,
ddl_synonym_events,
ddl_ B _ssevents,
events,
events,
ddl_procedure_events,
ddl_table_view_events,
ddl_type_events,
events,
ddl_partition_events,
events

Ddl_server_level_events includes:
Ddl_database_events,
Drop_database,
Ddl_endpoint_events,
Create_database,
Ddl_server_security_events,
Alter_database

2. DDL trigger syntax

-- Trigger on a create, alter, drop, Grant, deny, revoke, or update
-- Statistics Statement (DDL trigger)
Create trigger trigger_name
On {all server | database}
[With <ddl_trigger_option> [,... n]
{For | after} {event_type | event_group} [,... n]
As {SQL _statement [;] [,... n] | external [url = url] Name [/url] <method specifier> [;]}

<Ddl_trigger_option>: =
[Encryption]
[Execute as clause]

<Method_specifier >::=
Assembly_name.class_name.method_name

-- Trigger on a logon event (logon trigger)
Create trigger trigger_name
On all server
[With <logon_trigger_option> [,... n]
{For | after} Logon
As {SQL _statement [;] [,... n] | external name <method specifier> [;]}
<Logon_trigger_option >::=
[Encryption]
[Execute as clause]

<Method_specifier >::=
Assembly_name.class_name.method_name

3. Delete DDL trigger
Deleting a DDL trigger from the system cannot be implemented simply by dropping object_type object_name, just like deleting other objects. For DDL triggers, a prefix must be used in the trigger scope.

Syntax:
Drop trigger trigger_name on {database | all server}

4. eventdata () function
when an event is triggered, although there is no inserted or deleted table to check what has been changed, you can use a function named eventdata. This function returns an XML data type that contains the trigger event information. The basic syntax of XML data is as follows. However, the function content varies depending on the trigger time.

date-time
spid
name

database-level events have the following basic syntax, which is different from the previously displayed Syntax:

date-time
spid
name
name
name
name

The XML element is described as follows:
Posttime: date and time when the event is triggered.
Ø spid: assigned to the triggerCodeSqlserver process ID on
Computername: name of the computer that triggered the event.
Databasesname: name of the database that triggers the event
Username: name of the user that triggers the event.
Ø loginname: User Login Name that causes the event to be triggered

Instance: if it is a working time, it is not allowed to modify any stored procedures; otherwise, roll back
Drop trigger test_ddl_trigger on Database
Go
Create trigger test_ddl_trigger
On database
For create_procedure, alter_procedure, drop_procedure
As
If datepart (hour, getdate ()> = 9 and datepart (hour, getdate () <= 17
Begin
Declare @ message nvarchar (max)
Select @ message =
'Completing work during core hours. Trying to release -'
+ Eventdata (). Value (
'(/Event_instance/tsqlcommand/commandtext) [1]',
'Nvarchar (max )')

Raiserror (@ message, 16, 1)
Rollback
End
Go

Example 2: Create a trigger triggered when any behavior occurs in the database:
Drop trigger test_ddl_trigger_3 on Database
Go
Create trigger test_ddl_trigger_3
On database
ForDdl_database_level_events
As
Select eventdata ()
Go

Next, create a stored procedure to view the returned XML data:
Create procedure test_trigger_util
As
Select 'Hello all ~ '
Go

The returned XML data is as follows (formatted ):
<Event_instance>
<Eventtype> create_procedure </eventtype>
<Posttime> 2008-03-31t13: 53: 35.397 </posttime>
<Spid> 56 </spid> <servername> zhangzjsqlexpress </servername>
<Loginname> zhangzjdavid _zhang </loginname>
<Username> DBO </username>
<Databasename> mysqlserver </databasename>
<Schemaname> DBO </schemaname>
<Objectname> test_trigger_util </objectname>
<Objecttype> procedure </objecttype>
<Tsqlcommand>
<Setoptions ansi_nulls = "on" ansi_null_default = "on" ansi_padding = "on" quoted_identifier = "on" encrypted = "false"/>
<Commandtext>
Create procedure test_trigger_util
As
Select 'Hello all ~ '
</Commandtext>
</Tsqlcommand>
</Event_instance>

5. Summary
DML triggers can be seen as a special stored procedure. The moderator system can maintain its integrity, perform cascade updates in the system, or force business rules. Through the inserted and deleted tables, we can retrieve which columns have been updated. The essence of DML triggers is the stored procedure that runs automatically when data changes occur in the two tables.

DDL triggers are built to ensure security or notify the system of changes as required by the Department. You can use XML Information in triggers by using the eventdata () function.