Database Reference
In-Depth Information
As you see, when the trigger fires, the table has already been altered and a new column called
NewColumn
is
already there. As a result, when the trigger rolls back the transaction, SQL Server needs to undo the table alteration.
This process can be very inefficient, especially with the large tables.
As you already have seen, we were using the
EVENTDATA
function to get the information about the DDL event from
within the trigger. This function returns an xml value that contains information about the type of event, session and
DDL command, affected object, as well as other attributes. For instance, in our example, you would get the following
XML code.
<EVENT_INSTANCE>
<EventType>ALTER_TABLE</EventType>
<PostTime>2013-05-12T12:26:44.453</PostTime>
<SPID>54</SPID>
<ServerName>SQL2012-STD1\SQL2012</ServerName>
<LoginName>SQL2012-STD1\Administrator</LoginName>
<UserName>dbo</UserName>
<DatabaseName>SqlServerInternals</DatabaseName>
<SchemaName>Delivery</SchemaName>
<ObjectName>Addresses</ObjectName>
<ObjectType>TABLE</ObjectType>
<AlterTableActionList>
<Create>
<Columns>
<Name>NewColumn</Name>
</Columns>
</Create>
</AlterTableActionList>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
ENCRYPTED="FALSE" />
<CommandText>alter table Delivery.Addresses
add NewColumn as AddressId persisted</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
DDL triggers can be created in either server or database scope. Some of the DDL events,
CREATE_DATABASE
for
example, would require the trigger to have the server scope. Others events,
ALTER_TABLE
for example, could use either
of them. When such a trigger is created on the server scope, it would fire in the instance of the corresponding event in
any database on the server.
In SQL Server Management Studio, database level DDL triggers can be found under
Programmability
node in the
database. Server level DDL triggers are displayed under
Server Objects
node. You can also use
sys.trigger
s and
sys.server_triggers
catalog views to find them with T-SQL.
Logon Triggers
Logon triggers fire after a user successfully authenticates on the server, but before the session has been established.
Some of the scenarios where you can use logon triggers are for preventing the same user from opening multiple
database connections, or for restricting access to the system based on some custom criteria. The trigger in Listing 8-14
prevents
HRLogin
login from accessing the system outside of business hours.
