For some reason, I have some misconceptions about triggers. Here
is the correct understanding:
· DML Triggers (or triggers for DML Events)
can be AFTER (or FOR) or INSTEAD OF triggers. It is also true for the Azure
off-premise database. See the Syntax below:
Trigger on an INSERT, UPDATE, or DELETE statement to a table or
view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [
,...n ] | EXTERNAL NAME <method specifier [ ; ] > }
<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
<method_specifier> ::=
assembly_name.class_name.method_name
· DDL Triggers (or triggers for DDL Events)
can only be AFTER (or FOR) triggers. No INSTEAD OF triggers for DDL events. See
the Syntax below:
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 NAME < method specifier > [ ; ] }
<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
· LOGON Triggers (or triggers for LogOn
Events), the same as DDL Triggers, can only be AFTER trigger, no INSTEAD OF
trigger. See the Syntax below:
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 ]
DDL Triggers can be of two scopes.
· Server Level Trigger – To capture events for all
Databases on the Server,
· Database Level Trigger – Limited to a single Database on which
the Trigger is created.
DDL Trigger
often captures the information about the EVENT that fired it by using EVENTDATA()function. EVENTDATA() returns
an XML. The following example not only captures the DDL EVENT Type, but the
entire structure of the Event Data as well:
--A trigger that works with DDL
statements
CREATE TRIGGER
Database_CreateTable_DropTable
ON DATABASE
AFTER CREATE_TABLE, DROP_TABLE
AS
DECLARE @EventData XML;
SELECT
@EventData = EVENTDATA();
DECLARE @EventType VARCHAR(100);
SET @EventType =
@EventData.value('(/EVENT_INSTANCE/EventType)[1]',
'varchar(100)');
IF @EventType = 'CREATE_TABLE'
PRINT 'A new table has been
created.';
ELSE
PRINT 'A table has been
dropped.';
PRINT CONVERT(VARCHAR(MAX), @EventData);
--A CREATE TABLE statement that fires
the trigger
CREATE TABLE VendorsTest
(
VendorID INT ,
VendorName VARCHAR(50)
);
/*
The response from the system:
A new table has been created.
*/
-- Below shows the structure of the
Event Data or EVENT_INSTANCE:
<EVENT_INSTANCE>
<EventType>CREATE_TABLE</EventType>
<PostTime>2012-05-09T17:01:15.390</PostTime>
<SPID>51</SPID>
<ServerName>ANNE-PC\SQLEXPRESS</ServerName>
<LoginName>murach\Anne</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AP</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>VendorsTest</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
ENCRYPTED="FALSE"/>
<CommandText>
CREATE TABLE VendorsTest (VendorID
int,
VendorName varchar(50));
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
