Notes on SQL 7

Previous related posts:

• Notes on SQL 1


• Notes on SQL 2


• Notes on SQL 3


• Notes on SQL 4


• Notes on SQL 5


• Notes on SQL 6

Triggers

DML VS DDL triggers.

The order of trigger execution (when several are defined) can be modified with the sp_settriggerorder system SP.

A trigger only executes once regardless of the number of rows affected (e.g. DELETE FROM TableName deletes all rows in the table, but only causes a FOR DELETE trigger to execute once).

The affected rows (inserted/deleted) are available in the inserted and deleted special tables inside the trigger definition.

DDL triggers can be specified at the instance (ON ALL SERVER) or database (ON DATABASE) levels. The events on which DDL triggers can be defined (e.g. ALTER_TABLE, CREATE_DATABASE) are grouped (e.g. DDL_TABLE_EVENTS), so some triggers can be written more concisely. DDL triggers have access to the EVENTDATA function, which returns an XML with information about the event type, the time it happened, the SPID, ServerName, LoginName, UserName, DatabaseName, SchemaName, ObjectName, ObjectType, and TSQLCommand that caused it to fire. Each event has a different XML schema.

LOGON triggers can also be defined, but they cannot show messages to the user, since they execute before the session is established. A ROLLBACK inside a LOGON trigger causes the connection to terminate.

Views

Besides being able to use the ENCRYPTION and SCHEMABINDING options when creating a view, you can specify VIEW_METADATA, which is used for updatable views, so SQL Server returns metadata about it to client applications (instead of metadata about the underlying tables).

Data modifications through a view can only happen if:

• It references exactly one table


• Columns in the view reference columns in a table directly


• The column is not derived from an aggregate


• The column is not computed as a result of a UNION (ALL), CROSSJOIN, EXCEPT or INTERSECT.


• The column is not affected by the DISTINCT, GROUP BY, or HAVING clause.


• The TOP operator is not used

Also, the WITH CHECK OPTION clause (when creating a view) means that the only data manipulation that can occur through the view must also be retrievable when you select from it.

An exception to the previous restrictions are partitioned views (created with UNION ALL statements), which have a somewhat restrictive set of conditions. Broadly speaking, they need to ensure that the result set in each of the member tables (which must be all equally defined) are unique.

Indexed views also have a special list of requirements because data is materialized and physically stored (aka materialized views). Among these requirements are:

• The SELECT statement cannot reference other views


• All functions must be deterministic


• AVG, MIN, MAX, STDEV, STDEVP, VAR, VARP are not allowed


• The index must be both clustered and unique


• ANSI_NULLS must have been set to ON when the view and any tables referenced by it were created


• It must be created with the SCHEMABINDING option


• The SELECT statement must not contain subqueries, outer joins, EXCEPT, INTERSECT, TOP, UNION, ORDER BY, DISTINCT, COMPUT/COMPUTE BY, CROSS/OUTER APPLY, PIVOT or UNPIVOT.