Triggers in SQL Server

DDL Triggers

DDL triggers respond to an event associated with a Data Definition Language

(DDL) statement. These DDL statements are

 CREATE

 ALTER

 DROP

You use DDL triggers for the following purposes:

 To prevent changes being made to a schema

 To log who makes changes to a schema

 To respond in some desired way to changes made in a schema

 

Preventing undesired changes

The following example shows you how to prevent undesired changes being made to the tables of the
TriggerImpl database.

1. Use SQL Server Management Studio to connect to the desired SQL Server 2005 instance.

2. Click the New Database Query button.

3. Ensure you're using the TriggerImpl database:

USE TriggerImpl

4. Create a trigger to prevent changes being made to the TriggerImpl

database:

CREATE TRIGGER PreventChanges

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE

AS
PRINT 'Making alterations to the TriggerImpl database

is not permitted.'

PRINT 'To make changes you must disable this DDL

trigger.'

ROLLBACK

The first line provides a name for the trigger. The second line specifies that the trigger apply the database
changes. A trigger is bound to a database object; in this case, the current database, TriggerImpl . The third

line specifies that the trigger executes for DROP TABLE, ALTER TABLE, and CREATE TABLE statements. The
FOR keyword indicates that the trigger runs before the DDL statement executes.

5. Attempt to create a new table called DDLTriggerTest:

CREATE TABLE DDLTriggerTest

(ID int PRIMARY KEY,

SomeColumn varchar(30))

An error message displays.

6. Attempt to drop the dbo.ConstraintTest table that you created earlier in this chapter:

DROP TABLE dbo.ConstraintTest

The attempted change is prevented with a message similar to the message shown in previous step.

7. Drop the trigger:

DROP TRIGGER PreventChanges

ON DATABASE

8. Retry creating the DDLTriggerTest table, which failed in Step 5:

CREATE TABLE DDLTriggerTest

(ID int PRIMARY KEY,

SomeColumn varchar(30))

Because you dropped the trigger in Step 7, you can now successfully create the DDLTriggerTest table.

Auditing changes

Another use of a DDL trigger is to log how and when changes are made indat abase or table structure.

In the following example, I show you how to create a DDL trigger for the ALTER TABLE statement.
Follow these steps:

1. Ensure you are using the Chapter14 database:
    USE nir

2. Create a table called AuditedTable. Later you monitor this table for

    changes in its structure made by using the ALTER TABLE statement.

    CREATE TABLE AuditedTable

    (MessageID int PRIMARY KEY,

    Message varchar(100))

3. Insert a sample row into the AuditedTable table.

    INSERT INTO AuditedTable

    VALUES (1, 'Hello World!')

4. Confirm that the row has been inserted .

    SELECT *

    FROM AuditedTable

5. Create a table DDLAudit to contain the information used for auditing.

    Using a TIMESTAMP column allows easy monitoring of the sequence of alterations made:

    CREATE TABLE DDLAudit

    (

    Changed TIMESTAMP,

    DateChanged DateTime,

    TableName char(30),

    UserName varchar(50)

    )

6. Confirm that the DDLAudit table has been created and is empty.

    SELECT *

    FROM DDLAudit

7. Insert a sample row manually into the DDLAudit table.

    INSERT INTO DDLAudit (DateChanged, UserName)

    VALUES (GetDate(), 'John Smith')

8. Confirm that the sample row has been inserted.

    SELECT *

    FROM DDLAudit

9. Create a trigger named AuditDDL, which responds to an ALTER TABLE statement.

Notice that in the FOR clause, you write ALTER_TABLE with an underscore.

Notice too that the GetDate() function is used to retrieve the date and time when the row is inserted
into the DDLAudit table. The suser_sname() function is used to retrieve the system name of the

user making the change in the table schema.

CREATE TRIGGER AuditDDL

ON DATABASE

FOR ALTER_TABLE

AS

INSERT INTO dbo.DDLAudit(DateChanged,

TableName, UserName)

SELECT GetDate(), 'AuditedTable', suser_sname()

-- End of Trigger

 

The trigger now responds to any attempt to use the ALTER TABLE statement to alter the structure of the
AuditedTable table.

10. Use the following code to attempt to add an additional column to the AuditedTable table.

ALTER TABLE AuditedTable

ADD Comment varchar(30)

11. Inspect the content of the DDLAudit table.

SELECT *

FROM DDLAudit

DML Triggers

A DML trigger is executed in response to an event associated with a Data Modification Language (DML)
statement. A DML trigger is associated with one of the following statements:

 INSERT

 UPDATE

 DELETE

You can use DML triggers either to replace a DML statement or to execute after a DML statement. A
trigger that replaces a DML statement is called an INSTEAD OF trigger. A trigger that executes after
a DML statement is called an AFTER trigger.

The inserted and deleted tables

SQL Server automatically manages the deleted and inserted tables. If you delete rows from a table, the
deleted table contains a row that matches the rows deleted from the other table. Similarly, if you update
a row, the deleted table contains a row with the old values. When you execute an UPDATE, values are
inserted into both the inserted and deleted tables.

If you insert data into a table, a copy of that row or those rows is contained in the inserted table.

You can use the inserted and deleted tables to determine what kind of change has been made to the
data, as I show you in the next section.

Triggers for auditing DML

A common use for DML triggers is to record, for audit purposes, changes made to data. The following
steps show you how to create a DML trigger to store information about who changed data:

1. Open a new database engine query in SQL Server Management Studio.

2. Ensure you are working in the NIR database.

    USE NIR

3. Create a table to store messages called DMLAuditedTable:

   CREATE TABLE DMLAuditedTable

    (MessageID int PRIMARY KEY,

    Message varchar(100))

    This is the table you want to audit.

4. Enter a sample value in the DMLAuditedTable table:

    INSERT INTO DMLAuditedTable

    VALUES (1, 'Hello World!')

5. Confirm the successful INSERT operation:

    SELECT *

    FROM DMLAuditedTable

6. Create a table, DMLAudit, to store the audit information:

    CREATE TABLE DMLAudit

    (

    Changed TIMESTAMP,

    DateChanged DateTime,

    TableName char(30),

    UserName varchar(50),

    Operation char(6)

    )   

The changed column is of type TIMESTAMP to store information about the sequence in changes made
to the DMLAuditedTable table. In the Operation column, you store information about whether the DML

change was an INSERT or an UPDATE operation.

7. Enter a sample row manually into the DMLAudit table:

INSERT INTO DMLAudit (DateChanged, UserName)

VALUES (GetDate(), 'John Smith')

8. Confirm the successful INSERT operation into the DMLAudit table:

    SELECT *

    FROM DMLAudit

9. Create a DML trigger called AuditDML:

    CREATE TRIGGER AuditDML

    ON dbo.DMLAuditedTable

    AFTER INSERT, UPDATE

-- NOT FOR REPLICATION

AS

DECLARE @Operation char(6)

IF EXISTS(SELECT * FROM deleted)

SET @Operation = 'Update'

ELSE

SET @Operation = 'Insert'

INSERT INTO dbo.DMLAudit(DateChanged,

TableName, UserName, Operation)

SELECT GetDate(), 'DMLAuditedTable', suser_sname(),

@Operation

-- End of Trigger

Notice the IF clause that uses information from the deleted table to determine whether the operation is
an UPDATE or an INSERT. That information is stored in the @Operation variable. The GetDate() function

retrieves the data and time of the operation and the suser_sname() function retrieves the username.
The Operation column stores the value in the @Operation variable.

10. Test whether the DML trigger responds to an INSERT operation on the

    DMLAuditedTable table by using the following code:

    INSERT INTO DMLAuditedTable

    VALUES (2, 'To be or not to be, that is the

    question.')

11. Execute a SELECT statement on the DMLAudit table to confirm that

    the INSERT operation has been executed:

    SELECT *

    FROM dbo.DMLAudit

12. Execute an UPDATE statement against the DMLAuditedTable table:

    UPDATE DMLAuditedTable

    SET Message = 'Goodbye World!'

    WHERE MessageID = 1

13. Test whether the AuditDML trigger has added a row to the DMLAudit table by using the following code:

    SELECT *

    FROM DMLAudit

output window shows that the UPDATE operation also caused a row to be added to the DMLAudit table.
Notice that the value in the Operation column is Update.

The information you store in an audit table can be much more extensive than shown in this example.
The scope is limited only by your knowledge of T-SQL and your business setting.