Microsoft SQL Server - Lesson 27: Triggers

	Triggers

Triggers Fundamentals

Introduction to Triggers

When an action has been performed on a table, such as adding a new record, changing (editing/updating) an existing record, or deleting a (or some) records, the table produces a notification. We say that the table fires an event. You can use this occurring event to take some action.

A trigger is an action that is performed behind-the-scenes when an event occurs on a table.

Practical Learning: Introducing Triggers

Start Microsoft SQL Server and login to open the Microsoft SQL Server Management Studio
On the Standard toolbar, click the New Query button

To create a new database and a table, type the following:

-- ===================================================
-- Database:	CeilInn4
-- ===================================================

IF EXISTS(SELECT name FROM sys.databases
	  WHERE name = N'CeilInn4b')
DROP DATABASE CeilInn4;
GO
CREATE DATABASE CeilInn4;
GO

USE CeilInn4;
GO

IF OBJECT_ID('Rooms', 'U') IS NOT NULL
  DROP TABLE Rooms
GO

-- ===================================================
-- Database:	CeilInn4
-- Table:	Rooms
-- Description:	This table is used to hold information
--		about his room rented for the hotel
-- ===================================================
CREATE TABLE Rooms
(
    RoomNumber nvarchar(10),
    LocationCode nchar(10) default N'Silver Spring',
    RoomType nvarchar(20) default N'Bedroom',
    BedType nvarchar(40) default N'Queen',
    Rate money default 85.95,
    Available bit default 1
);
GO

-- ===================================================
-- Database: CeilInn4
-- Table:	 DatabaseOperations
-- Description: This table is used to hold information
--              about operations performed on any table
--              of the database. It specifies:
--              a. The type of object on which the action
--                 was performed. The types of object can
--                 be a table
--              b. The name of the table
--              c. The name of the employee who 
--                 performed the action.
--              d. The action that was performed. This
--                 can be an insert, an update, or a 
--                 delete operation
--              e. The date/time the action was performed
-- ===================================================
CREATE TABLE DatabaseOperations (
    ObjectType nchar(20),
    ObjectName nvarchar(40),
    EmployeeName nvarchar(50),
    ActionPerformed nvarchar(50),
    TimePerformed datetime2
);
GO

Press F5 to execute

Creating a Trigger

You create a trigger using SQL code. Later on, we will start analyzing the code necessary to do this. To assist you with skeleton code, open an Query window. Then, in the Template Explorer, expand the Triggers node. Drag Create T-SQL Trigger (New Menu) and drop it in the window:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER <Schema_Name, 
		sysname, 
		Schema_Name>.<Trigger_Name, 
			      sysname, 
			      Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

Executing a Trigger

Unlike a stored procedure, you never have to execute a trigger. The operating system (through the object(s) event(s)) and the database engine take care of this. A trigger acts behind the scenes when the object to which it is associated fires the appropriate event. In fact, the event fires whether the object received a change or not (whether a record was created, edited, or deleted, or not)l.

Trigger Management

Introduction

A trigger is a database object. As such, it has a name. It can be modified. It can also be deleted.

Modifying a Trigger

If the behavior of a trigger is not appropriate, you can change it. The formula to modify a trigger is:

ALTER TRIGGER schema_name.trigger_name 
ON  schema_name.table_name
AFTER , UPDATE>
AS
    statement

To get skeleton code generated for you, open a Query window. In the Templates Explorer, expand the Triggers node, drag the Alter option and drop it in the Query window:

--======================================
--  Alter T-SQL Trigger Template
--======================================
USE <database_name, sysname, AdventureWorks>
GO

ALTER TRIGGER <schema_name, sysname, Sales>.<trigger_name, sysname, uStore> 
ON  <schema_name, sysname, Sales>.<table_name, sysname, Store> 
AFTER <data_modification_statements, , UPDATE>
AS <T-SQL_statement,
   ,
   UPDATE Sales.Store 
   SET ModifiedDate = GETDATE()
   FROM inserted WHERE inserted.CustomerID = Sales.Store.CustomerID>
GO

Deleting a Trigger

If you do not need a trigger anymore, you can remove it from a table. The formula to do this is:

DROP TRIGGER TriggerName

After the DROP TRIGGER expression, enter the name of the trigger.

DML Triggers

Introduction

Microsoft SQL Server supports three types of triggers: DML, DDL, and logon.

A DML trigger is a procedure that acts as a result of a data manipulation language event occurring on a table. This means that the trigger must be created in connection to a table of a non-system database.

Types of DML Triggers: After Inserting

An insert trigger is a DML trigger that acts when a new record is added to its intended table. The primary formula to create an insert DML trigger on a table is:

CREATE TRIGGER TriggerName
ON TableName
AFTER/FOR INSERT
AS
    TriggerCode

The statement starts with CREATE TRIGGER, followed by a name. The name conforms to the rules we have applied so far for database objects. After specifying the name of the trigger, write ON followed by the name of the table on which the trigger will be applied. Of course, the table must exist in the database.

In our formula, we assume that the trigger will apply when a record has been added to the table. Therefore, you use either the AFTER INSERT or the FOR INSERT expression.

To start the SQL code that constitute the subject of the trigger, write AS, and then write your code.

After creating an INSERT trigger, at the right time (when its intended event fires), it will be executed. When this happens, the database engine automatically and internally creates a temporary table named inserted. This table holds a copy of the records that were created. You can access those records if necessary.

Practical Learning: Creating a DML Trigger

Click somewhere in the middle window to give it focus, then press Ctrl + A to select everything

To create a new trigger, type the following:

USE CeilInn4;
GO

-- ========================================================
-- Database:	CeilInn4
-- DML Trigger:	RecordInsertion
-- Description:	This trigger updates the DatabaseOperations
--		by letting it know that a new record was
--		added to the Rooms table. The trigger
--		also specifies the name of the employee
--		who performed the operation and the time
--		this occurred
-- ========================================================
CREATE TRIGGER RecordInsertion
ON Rooms
AFTER INSERT
AS
BEGIN
    INSERT INTO DatabaseOperations
    VALUES(N'Table', N'Rooms', SUSER_SNAME(), 
	   N'Created a new record', GETDATE())
END
GO

Press F5 to execute
In the Object Explorer, right-click Databases and click Refresh
Expand Databases and expand CeilInn4
Under CeilInn4, expand Tables

To perform data entry, right-click Rooms and click Edit Top 200 Rows (this exercise would be more interesting if you can first create 1 to 3 accounts, log out, then log in as different accounts and continue with the data entry)

RoomNumber	LocationCode	RoomType	BedType	Rate	Available
104	SLSP
105	SLSP		King	95.50	True
106	SLSP		King	95.50	True
107	SLSP				True
108	SLSP		King	95.50
109	SLSP				True
110	SLSP	Conference		450.00	True

Close the table
In the Object Explorer, right-click the DatabaseOperations table and click Select Top 1000 rows to see its records
Close the DatabaseOperations table

Types of DML Triggers: After Updating

Instead of record insertion time, a DML trigger can act when a record has been updated on a table. To support this operation, you can use the following formula:

CREATE TRIGGER TriggerName
ON TableName
AFTER/FOR UPDATE
AS
    TriggerCode

The new keyword in this formula is UPDATE. This indicates that the DML trigger will act when the record has been updated. Everything else is as described for the INSERT operator. Remember to use either AFTER UPDATE or FOR UPDATE.

Practical Learning: Updating With a DML Trigger

Click somewhere in the middle window to give it focus, then press Ctrl + A to select everything

To create a new trigger, type the following:

USE CeilInn4;
GO

-- ========================================================
-- Database:	CeilInn4
-- DML Trigger:	RecordUpdate
-- Description:	This trigger adds a new record to the 
--		DatabaseOperations when an existing record 
--		of the Rooms table is updated.
-- ========================================================
CREATE TRIGGER RecordUpdate
ON Rooms
AFTER UPDATE
AS
BEGIN
    INSERT INTO DatabaseOperations
    VALUES(N'Table', N'Rooms', SUSER_SNAME(), 
	   N'Changed an existing room record', GETDATE())
END
GO

Press F5 to execute
To edit some records, in the Object Explorer, right-click Rooms and click Edit Top 200 Rows

Change the following records

RoomNumber	LocationCode	RoomType	BedType	Rate	Available
105			Queen	88.75	False
107	LRL		King	98.50
110			(Delete Queen)	485.50	False

Close the table
In the Object Explorer, right-click the DatabaseOperations table and click Select Top 1000 rows to see its records
Close the DatabaseOperations table

Types of DML Triggers: After Deleting

When a record has been removed from a table, you can apply a DML trigger in response. To make it possible, you can use the following formula:

CREATE TRIGGER TriggerName
ON TableName
AFTER/FOR DELETE
AS
    TriggerCode

This time, the formula uses the DELETE operator in an AFTER DELETE or a FOR DELETE expression. This is used for record removal. The other factors follow the same description we saw for the INSERT operator.

When a DELETE trigger has acted on a table, the database engine creates a special temporary table named deleted. This table holds a copy of the records that were deleted. Eventually, if necessary, you can access this table to find out about those records.

Practical Learning: Deleting With a DML Trigger

Click somewhere in the middle window to give it focus, then press Ctrl + A to select everything

To create a new trigger, type the following:

USE CeilInn4;
GO

-- ========================================================
-- Database:	CeilInn4
-- DML Trigger:	RecordDeletion
-- Description:	This trigger adds a new record to the 
--		DatabaseOperations when an existing record 
--		of the Rooms table has been deleted.
-- ========================================================
CREATE TRIGGER RecordDeletion
ON Rooms
AFTER DELETE
AS
BEGIN
    INSERT INTO DatabaseOperations
    VALUES(N'Table', N'Rooms', SUSER_SNAME(), 
	   N'Deleted a room', GETDATE())
END
GO

Press F5 to execute
To delete a record, in the Object Explorer, right-click Rooms and click Edit Top 200 Rows
Right-click the row header of record 106 and click Delete
To confirm that you want to delete, click Yes
Close the table
In the Object Explorer, right-click the DatabaseOperations table and click Select Top 1000 rows to see its records
Close the DatabaseOperations table

Characteristics of DML Triggers

Introduction

Although we created only one trigger for a table so far, you can go farther than that:

You can create many DML triggers (using different names, of course) that perform the same action on a table. This means that you can create many INSERT triggers (or many UPDATE triggers or many DELETE triggers) that act on the same table and that target the same action
You can create different triggers that act on a table

DML triggers present many other characteristics.

DML Triggers and Constraints

In Lesson 11, we saw that, to assist a user with data entry, you can specify that a column would allow or not allow null values. If a column is marked as NOT NULL, during data entry, if the user does not or cannot provide a value for that column, the record cannot be created. If you create a DML trigger that must act against that table, if the nullity rule is violated, the trigger will not run.

In Lesson 11, we saw that you can create a check constraint on a table to make sure that every new record, or a record that is being edited, follows a certain rule. We know if the record does not abide by that rule, the record will not be created or changed. If a DML trigger is supposed to act on the table, if this rule is not respected, the trigger would fail.

One of the limitations of a check constraint is that it applies only to the table that owns it. A DML trigger can be created to perform a check constraint on more than one table. This provides its advantage over the normal check constraint.

In Lesson 16, we studied data relationships and referential integrity. This makes sure that, when a record is edited in a parent table, the change is also made on the child table. This also means that the integrity is applied to more than one table. When a DML trigger runs, if a referential rule is violated, the trigger, which also checks the referential integrity, fails.

Instead of DML Triggers

Introduction

Consider the following table and view in a database:

CREATE DATABASE SmallBusiness;
GO

USE SmallBusiness;
GO

CREATE TABLE Customers
(
    CustomerID int identity(1, 1) primary key not null,
    AccountNumber nchar(10),
    FullName nvarchar(50)
);
GO

CREATE TABLE DatabaseOperations (
    EmployeeName nvarchar(50),
    ActionPerformed nvarchar(50),
    TimePerformed datetime2
);
GO

From what we have seen so far, when a user opens a table or a view to perform data entry, when a new record has been created, the table or view fires an event. We saw that, using a DML trigger, you can make a notification. For example you can fill out a log to keep track of the changes. By default, when a record is submitted, it gets saved. In some cases, when a user has opened a table and tried to make a change, such as adding a new record, editing an existing record, or deleting one, instead of accepting the change, you can dismiss it. You can then use a DML trigger to make a note. This is the basis of another category of DML triggers: an "instead of" trigger.

While an AFTER/FOR trigger acts on a table after the action has occurred, you may want to do something before the event. For example, you may want to prevent the user from adding a new record on a tale, or from changing an existing, or from deleting a record. Of course, it is better to take care of this before the action is performed. One way you can do this is by creating an "instead of" trigger.

Creating an INSTEAD OF Trigger

While an AFTER trigger can be applied to a table only, an "instead of" trigger can be associated with either a table or a view. Therefore, to create an "instead of" trigger, you use the following formula:

CREATE TRIGGER TriggerName
ON TableOrViewName
INSTEAD OF INSERT/UPDATE/DELETE
AS
    TriggerCode

You start with the CREATE TRIGGER expression followed by a name for the trigger. After the name of the trigger, type ON followed by the name of either a table or a view on which the trigger will act.

From our review of the AFTER trigger, the new expression here is INSTEAD OF. This expression is followed by the type of operation to perform. If you want:

To catch the creation of a new record, use the INSERT operator
To catch the editing operation of an existing record, use the UPDATE operator
To catch the removal of a record, use the DELETE operator

To start the triggering code, type AS and write the desired code.

If you use the INSTEAD OF expression, the trigger starts when the table or view is opened but before a change has taken place. The difference with the AFTER trigger is that, this time, you can perform some action(s) before the change is made on the table or view. This also implies that, if the code of the trigger is to create a new record, at this time, the record doest not yet exist, which means you cannot catch that record. At this time also, you can prevent the record from being created (since it has not yet been created anyway). For example, the following code will not accept that a new record be added to the table:

USE SmallBusiness;
GO

CREATE TRIGGER CreateCustomer
ON Customers
INSTEAD OF INSERT
AS
    BEGIN
        INSERT INTO DatabaseOperations
        VALUES(SUSER_SNAME(), 
	       N'Attempt to create new record', GETDATE())
    END
GO

If you want to get a copy of the record that was affected by the event, you can access it from the inserted (for an INSERT or UPDATE trigger) or from the deleted (for a DELETE) trigger. Here is an example:

USE SmallBusiness;
GO

DROP TRIGGER CreateCustomer;
GO

CREATE TRIGGER CreateCustomer
ON Customers
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Customers 
    SELECT AccountNumber, FullName FROM inserted
END
GO

Practical Learning: Creating INSTEAD OF Triggers

Click somewhere in the middle window to give it focus, then press Ctrl + A to select everything

To create a new trigger, type the following:

USE CeilInn4;
GO

-- ===================================================
-- Database:	CeilInn4
-- View:	Logistics
-- Description:	This view retrieves the list of rooms
--              of this hotel
-- ===================================================
CREATE VIEW Logistics
AS
    SELECT RoomNumber, LocationCode, RoomType,
           BedType, Rate, Available
    FROM Rooms;
GO

-- ========================================================
-- Database:	CeilInn4
-- DML Trigger:	AttemptedRecordInsertion
-- Description:	This trigger acts on a table to update the
--		DatabaseOperations to let it know that an 
--		attempt was made to create a new room
-- ========================================================
CREATE TRIGGER AttemptedRecordInsertion
ON Rooms
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO DatabaseOperations
    VALUES(N'Table', N'Rooms', SUSER_SNAME(), 
	   N'Attempted to create a new record', GETDATE())
END
GO

-- ========================================================
-- Database:	CeilInn4
-- DML Trigger:	AttemptedRecordUpdate
-- Description:	This trigger acts on a view to update the
--		DatabaseOperations to let it know that an 
--		attempt was made to edit a record of 
--		the Rooms table
-- ========================================================
CREATE TRIGGER AttemptedRecordUpdate
ON Logistics
INSTEAD OF UPDATE
AS
BEGIN
    INSERT INTO DatabaseOperations
    VALUES(N'View', N'Logistics', SUSER_SNAME(), 
	   N'Attempted to change a rooom''s information',
	   GETDATE())
END
GO

Press F5 to execute
In the Object Explorer, under CeilInn4, right-click Rooms and click Edit Top 200 Rows

Add the following new records:

RoomNumber	LocationCode	RoomType	BedType	Rate	Available
104	LRL		King	95.50
112	SLSP		King	95.50	True

Notice a warning on the row headers of the records that were changed on the table.
Close the table
In the object Explorer, under Views, right-click Logistics and click Edit Top 200 Rows

Change the following record

RoomNumber	LocationCode	RoomType	BedType	Rate	Available
108	LRL	Conference	Delete	425.75

Notice a warning on the row header of the view.
Press Esc
Close the table
In the Object Explorer, right-click the DatabaseOperations table and click Select Top 1000 rows to see its records
Close the table

Characteristics of INSTEAD OF Triggers

An AFTER/FOR and an INSTEAD OF triggers have many differences. For example:

An INSTEAD OF UPDATE or an INSTEAD OF DELETE trigger cannot act on a table that has a column marked with the DELETE or the UNIQUE operators
You can create only one type of INSTEAD OF trigger for each table. For example, a table cannot have more than one INSTEAD OF INSERT trigger

DDL Triggers

Introduction

In Lesson 3, we saw that the creation of a database uses a Data Definition Language (DDL) command. In Lesson 9, we saw another example of a DDL command that involved creating a table. Each one of these creation operations fires an event.

A DDL trigger is a trigger that acts when a certain type of DDL event fires. These include the creation, modification, or removal of an object, not its records. This is the primary difference with a DML trigger that fires when a record is added or acted upon.

A DDL trigger gives you the opportunity to do some administrative work in response to the event. For example, you can get a notification, or notify someone else using an automatically generated email, that an (and what) object has been created. Or you can use a DDL trigger to discard the operation.

Creating a DDL Trigger

You create a DDL trigger using code. The basic formula is:

CREATE TRIGGER TriggerName
ON DATABASE/ALL SERVER
FOR/AFTER WhatEvent
AS
    TriggerCode

You start a DDL trigger with the CREATE TRIGGER expression followed by a name for the new trigger. The name follows the same rules we have applied to objects so far. After the name of the trigger, type the ON keyword:

If you want the trigger to act on the current database, type DATABASE. When the intended event occurs on the current database, the trigger will execute
If you want the trigger to act on the server, follow the ON operator with ALL SERVER. In this case, when the intended event occurs on any part of the server, the trigger executes

After specifying the object (the whole server or only the current database) on which the trigger will act, type either FOR or AFTER. This is followed by the event against which the trigger will act. As mentioned already, the events are DDL commands. To specify the event, use the formula of the command with the words separated by an underscore. For example, if you want the trigger to act when a CREATE TABLE command is executed, specify the event as CREATE_TABLE.

After specifying the event that will fire, type AS followed by the normal code of the trigger.

Here is an example that makes a note and adds it to a table when a new table has been created:

USE SmallBusiness;
GO

CREATE TRIGGER LogNewTableCreation
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    INSERT INTO DatabaseOperations
    VALUES(SUSER_SNAME(), 
	   N'A new table was created', GETDATE())
END
GO

Whenever a new table is created in the current database, the trigger runs, gets the name of the user who created the table, the date and time the table was created, and a small message. These pieces of information are then stored in a log table.

As mentioned for DML triggers, you manage DDL triggers by modifying or deleting them. These are done using the same description we saw for DML triggers.


Previous	Copyright © 2009 FunctionX, Inc.	Next