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 as in AFTER DELETE or FOR DELETE. The other things 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.

1. If you had logged in as an account other than the first you had used to create the database, log in as that account.
   Click the top section of the Query window and press Ctrl + A
2. To create two new triggers, type the following:
   

   USE KoloBank3;
   GO
   
   -- ========================================================
   -- Description:	These triggers add a new record to the 
   --		Management.DatabaseOperations when an existing record 
   --		of a table has been deleted.
   -- ========================================================
   CREATE TRIGGER DepositDeleted
   ON Transactions.Deposits
   AFTER DELETE
   AS
   BEGIN
       INSERT INTO Management.DatabaseOperations
       VALUES(default, N'Deposits', SUSER_SNAME(), 
   	   N'Deleted a deposit from a bank account.', GETDATE())
   END
   GO
   CREATE TRIGGER WithdrawalDeleted
   ON Transactions.Withdrawals
   AFTER DELETE
   AS
   BEGIN
       INSERT INTO Management.DatabaseOperations
       VALUES(default, N'Withdrawals', SUSER_SNAME(), 
   	   N'Deleted a withdrawal from a bank account.', GETDATE())
   END
   GO

3. Press F5 to execute
4. Log in as the operez account
   1. In the Object Explorer, right-click Transactions.Deposits and click Edit Top 200 Rows
   2. To delete a record, right-click the row header of the 5th record:
       
      
   3.  Click Delete
   4.  Read the text in the Microsoft SQL Server Management Studio message box:
       
      
      
   5. To confirm that you want to delete, click Yes
   6. Notice that you receive an error because you don't have the right permission(s):
       
      
   7. Read the message and click OK
   8. Because some supervisor suspects that Orlando Perez tried to do something bad to the database, Orlando has been asked to log out of the database.
      Close Microsoft SQL Server
5. Log in as the first account used to create the database
6. Click the top section of the Query window press Ctrl + A to select everything
7. Type the following code:

   REVOKE SELECT, INSERT
   ON OBJECT::Transactions.Deposits
   TO Orly;
   GO
   REVOKE SELECT, INSERT
   ON OBJECT::Transactions.Withdrawals
   TO Orly;
   GO
   REVOKE SELECT, INSERT
   ON OBJECT::Transactions.CheckCashing
   TO Orly;
   GO
   
   DENY SELECT
   ON OBJECT::Transactions.Deposits
   TO Orly;
   GO
   DENY SELECT
   ON OBJECT::Transactions.Withdrawals
   TO Orly;
   GO
   DENY SELECT
   ON OBJECT::Transactions.CheckCashing
   TO Orly;
   GO

8. To execute, on the main menu, click Query -> Execute
9. Log in as the gmonay account
   1. In the Object Explorer, right-click Transactions.Deposits and click Edit Top 200 Rows
   2. To delete a record, right-click the row header of the 5th record
      
   3. Click Delete
      
   4.  Click Yes
      
   5.  Close the table
10. Log in as one of the accounts, such as the first account you used to create the database.
    If you don't have a Query window, in the Object Explorer, right-click KoloBank3 and click New Query, then type the following:

    SELECT ALL * FROM Management.DatabaseOperations;
    GO

11. Right-click the Query window and click Execute
     
    

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

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

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

DROP TRIGGER TriggerName

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

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.

You know 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.

You know 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. You know that 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 and 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.

You probably know already about 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.

Consider the following tables 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 Management.DatabaseOperations (
    EmployeeName nvarchar(50),
    ActionPerformed nvarchar(50),
    TimePerformed time
);
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 a record, 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 fires. For example, you may want to prevent the user from adding a new record on a table, or from changing an existing record, 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.

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, 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
• If you want to catch the editing operation of an existing record, use the UPDATE operator
• If you want 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 Management.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

1. If you are not logged in as the account used to create the database, log in as that one.
   Click the top section of the Query window press Ctrl + A to select everything
2. To create two new triggers, type the following code:

   USE KoloBank3;
   GO
   
   -- ========================================================
   -- This trigger acts on a view to update the
   -- Management.DatabaseOperations to let it know that an 
   -- attempt was made to create a new room
   -- ========================================================
   CREATE TRIGGER Transactions.AttemptedDeposit
   ON Transactions.DepositsSummary
   INSTEAD OF INSERT
   AS
   BEGIN
       INSERT INTO Management.DatabaseOperations
       VALUES(N'View', N'DepositsSummary', SUSER_SNAME(), 
   	   N'Attempted to make a new deposit.', GETDATE())
   END
   GO
   
   -- ========================================================
   -- This trigger acts on a view to update the
   -- Management.DatabaseOperations to let it know that an 
   -- attempt was made to create a new room
   -- ========================================================
   CREATE TRIGGER Transactions.AttemptedWithdrawal
   ON Transactions.WithdrawalsSummary
   INSTEAD OF INSERT
   AS
   BEGIN
       INSERT INTO Management.DatabaseOperations
       VALUES(N'View', N'WithdrawalsSummary', SUSER_SNAME(), 
   	   N'An attempt was made to withdraw money.', GETDATE())
   END
   GO

3. Press F5 to execute
4. Log back in as the operez account
   1.  Start Microsoft SQL Server
   2.  Make sure Windows Authentication is selected and click Connect
   3. In the Object Explorer, expand KoloBank3 and expand its Tables node.
      Notice that the Deposits, the Withdrawals, and the CheckCashing tables have disappeared
   4.  Still in the Object Explorer, under KoloBank3, expand Views
       
      
   5. Right-click Transactions.DepositsSummary and click Edit Top 200 Rows
   6.  Create a new record as follows:
       

      LocationID	EmployeeID	CustomerID	DepositDate	DepositAmount
      2	5	2	01/22/11	137.85

   7. Close the Transactions.DepositsSummary view
5. Log back in as the rkouma account
   1. In the Object Explorer, under KoloBank3, under Views, right-click Transactions.WithdrawalsSummary and click Edit Top 200 Rows
   2. Create a new record as follows:
       

      LocationID	EmployeeID	CustomerID	WithdrawalDate	WithdrawalAmount
      2	4	4	01/24/11	744.25

   3. Close the Transactions.WithdrawalsSummary view
6. Log in as any account, such as the first account you used to create the database
7. If you don't have a Query window, in the Object Explorer, right-click KoloBank3 and click New Query.
   In the Query window, type the following:

   SELECT ALL * FROM Management.DatabaseOperations;
   GO

8. Right-click inside the Query window and click Execute.
   Notice the entries in the PerformedBy column
9. Log in as any account
10.  Click the SQLQuery1.sql tab if necessary. Right-click the Query window and click Execute
    

11. Log in as each account other than the first that was used to create the database
    1. Close the Query window
    2. When asked whether you want to save, click No
    3. On the taskbar, let each student click Start -> Log Off
12. Log back in as the first account

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

• If a table has a relationship to another table and the referential integrity on that relationship is marked with either ON DELETE or ON UPDATE, an INSTEAD OF UPDATE or an INSTEAD OF DELETE trigger cannot act on that table
• 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

You probably know that the creation of a database uses a Data Definition Language (DDL) command. You may also know another example of a DDL command that involves 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 object (and what object) has been created. Or you can use a DDL trigger to discard the operation.

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 (the note) 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 Management.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.

1. Close Microsoft SQL Server
2. Re-start Microsoft SQL Server and log in as the account with administrative rights
3. In the Object Explorer, expand Databases
4. Right-click KoloBank3 and click Delete
5. In the Delete Object dialog box, click OK
6. Close Microsoft SQL Server