The first part of this chapter described DML triggers, which specify an action that is performed by the server when a modification of the table using an INSERT, uPdATE, or DELETE statement is executed. The Database Engine allows you to define triggers for DDL statements, such as CREATE DATABASE, DROP TABLE, and ALTER TABLE. The syntax for DDL triggers is
CREATE TRIGGER [schema_name.]trigger_name
ON {ALL SERVER | DATABASE }
[WITH {ENCRYPTION | EXECUTE AS clause_name]
{FOR | AFTER } { event_group | event_type | LOGON}
AS {batch | EXTERNAL NAME method_name}
As you can see from the preceding syntax, DDL triggers are created the same way DML triggers are created. (The ALTER TRIGGER and DROP TRIGGER statements are used to modify and drop DDL triggers, too.) Therefore, this section describes only those options of CREATE TRIGGER that are new in the syntax for DDL triggers.
When you define a DDL trigger, you first must decide on the scope of your trigger. The DATABASE clause specifies that the scope of a DDL trigger is the current database. The ALL SERVER clause specifies that the scope of a DDL trigger is the current server.
After specifying the trigger’s scope, you have to decide whether the trigger fires to a single DDL statement or a group of statements. event_type specifies a DDL statement that, after execution, causes a trigger to fire. event_group defines a name of a predefined group of Transact-SQL language events. The DDL trigger fires after execution of any Transact-SQL language event belonging to event_group. You can find the list of all event groups and types in Microsoft Docs. The LOGON keyword specifies a logon trigger (see Example 14.7, later in this section).
Besides the similarities that exist between DML and DDL triggers, there are several significant differences. The main difference between these two trigger forms is that a DDL trigger can be used to define as its scope an entire database or even an entire server, not just a single object. Also, DDL triggers do not support INSTEAD OF triggers. As you might have guessed, inserted and deleted virtual tables are not necessary, because DDL triggers do not change a table’s content.
The two different forms of DDL triggers, database-level and server-level, are described next.
1. Database-Level Triggers
Example 14.6 shows how you can implement a DDL trigger whose scope is the current database.
Example 14.6
USE sample;
GO
CREATE TRIGGER prevent_drop_synonyms
ON DATABASE FOR DROP_SYNONYM
AS PRINT ‘You must disable “prevent_drop_synonyms” to drop any synonym’
The trigger in Example 14.6 prevents all users from deleting any synonym that belongs to the sample database. The DATABASE clause specifies that the prevent_drop_synonyms trigger is a database-level trigger. The DROP_SYNONYM keyword is a predefined event type that prevents a deletion of any synonym.
2. Server-Level Triggers
Server-level triggers respond to changes on the server. You use the ALL SERVER clause to implement server-level triggers. Depending on the action, there are two different flavors of server-level triggers: conventional DDL triggers and logon triggers. The triggering action of conventional DDL triggers is based on DDL statements, while the triggering action of logon triggers is a logon event.
Example 14.7 shows a server-level trigger that is at the same time a logon trigger.
Example 14.7
USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = ‘dpetkovic§$!’,
CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS ‘login_test’
FOR LOGON AS
BEGIN
IF ORIGINAL_LOGIN()= ‘login_test’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘login_test’) > 1
ROLLBACK;
END;
Example 14.7 first creates the login called login_test. This login is subsequently used in a server-level trigger. For this reason, it requires server permission VIEW SERVER STATE, which is given to it with the GRANT statement. After that, the connection_limit_trigger trigger is created. This trigger belongs to logon triggers, because of the LOGON keyword. The use of the sys.dm_exec_sessions view allows you to check if there is already a session established using the login_test login. In that case, the ROLLBACK statement is executed. That way, the login_ test login can establish only one session at a time.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.