SQL Server database-level triggers

One of the many new features of SQL Server 2005 is the Data Definition Language (DDL) Trigger. In SQL Server 7.0 and 2000, we use a data operation language (DML) trigger to execute an SQL statement or stored procedure when an insert, update, or delete statement is executed, they can only be used for tables or views.

In SQL Server 2005, we can create a trigger on the DDL statement to perform any operation. The trigger can be used at the database or server layer. DDL triggers are usually executed when the database structure is to be changed.

Let's create a trigger on the database to prevent any user from creating, alter, and drop tables or stored procedures. Assume that we have a database named ddltrtest, which contains a table (mytable) and a stored procedure (usp_querymytable ).

use the following statements to create databases, tables, and stored procedures.
use [Master]
go
If exists (Select name from sys. databases where name = n' ddltrtest ')
drop database [ddltrtest]
go
Create Database ddltrtest
go
use [ddltrtest]
go
If exists (select * From sys. objects where object_id = object_id (n' [DBO]. [mytable] ')
and type in (n'u')
drop table [DBO]. [mytable]
go
Create Table mytable (ID int, name varchar (100)
go
insert into mytable select 1, 'A'
insert into mytable select 2, 'B'
insert into mytable select 3, 'C'
insert into mytable select 4, 'D'
insert into mytable select 5, 'E'
insert into mytable select 6, 'F'
go
use [ddltrtest]
go
If exists (select * From sys. objects where object_id =
object_id (n' [DBO]. [usp_querymytable] ')
and type in (n'p', n'pc')
drop procedure [DBO]. [usp_querymytable]
go
Create proc usp_querymytable
as
select * From mytable
go

Create another DDL trigger stop_ddl_on_table_and_proc.

Create trigger stop_ddl_on_table_and_proc
On database
For create_table, drop_table,
Alter_table, create_procedure,
Alter_procedure, drop_procedure
As
Select eventdata (). Value
('(/Event_instance/tsqlcommand/commandtext) [1]',
'Nvarchar (max )')
Print 'you are not allowed to create, alter and drop
Any tables and procedures'
Rollback;

Here we use a new function eventdata () provided by SQL Server 2005 to capture SQL statements.

Now we try to use the following command to add a column to mytable.
Alter table mytable add x int

The result is as follows:
Alter table mytable add x int
(1 row (s) affected)
You are not allowed to create, alter and drop any tables and procedures
MSG 3609, level 16, state 2, line 1
The transaction ended in the trigger. The batch has been aborted.

Let's try to delete the mytable table.
Drop table mytable

The result is as follows:
Drop table mytable
(1 row (s) affected)
You are not allowed to create, alter and drop any tables and procedures
MSG 3609, level 16, state 2, line 1
The transaction ended in the trigger. The batch has been aborted.

Let's try to create a stored procedure
Create procedure usp_querymytable2
Select Top 100 * From mytable

The result will be as follows:
Create procedure usp_querymytable2
Select Top 100 * From mytable
(1 row (s) affected)
You are not allowed to create, alter and drop any tables and procedures
MSG 3609, level 16, state 2, procedure usp_querymytable2, line 3
The transaction ended in the trigger. The batch has been aborted.

The stop_ddl_on_table_and_proc DDL trigger is only applied to the database ddltrtest. Create, alter, and drop can be executed on other databases.

These triggers can be disabled, enabled, or deleted using the following commands.
Disable trigger stop_ddl_on_table_and_proc
On database
Go
Enable trigger stop_ddl_on_table_and_proc
On database
Go
Drop trigger stop_ddl_on_table_and_proc on Database
Go

If you want to change the trigger scope from the database layer to the server layer, you can change on database to on all server as follows. This trigger stops users.

Execute create, alter, and drop database operations.

Create trigger stop_ddl_on_table_and_proc
On all server
For create_database, alter_database, drop_database
As
Print 'you are not allowed to create, alter and drop any databases'
Rollback;

Now, let's try to create, modify, and delete a database.

Use master
Go
Create Database Test
Go
Alter database ddltrtest Modify file
(
Name = ddltrtest,
Filename = 'd: \ ddltrtest. MDF ')
Go
Use master
Go
Drop database ddltrtest
Go

The result is as follows:

You are not allowed to create, alter and drop any databases
MSG 3609, level 16, state 2, line 1
The transaction ended in the trigger. The batch has been aborted.
The file "ddltrtest" has been modified in the system catalog.
The new path will be used the next time the database is started.
You are not allowed to create, alter and drop any databases
MSG 3609, level 16, state 2, line 1
The transaction ended in the trigger. The batch has been aborted.
You are not allowed to create, alter and drop any databases
MSG 3609, level 16, state 2, line 1
The transaction ended in the trigger. The batch has been aborted.

You can use the following command to disable, enable, or delete a server-Level Trigger.

Disable trigger stop_ddl_on_table_and_proc
On all server
Go
Enable trigger stop_ddl_on_table_and_proc
On all server
Go
Drop trigger stop_ddl_on_table_and_proc on all server
Go

Summary
This articleArticleThis article mainly explores the new DDL triggers and eventdata () functions of SQL Server 2005.

Original article: http://kb.cnblogs.com/a/1140060/