Thursday, March 17, 2011

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.

1 comment :

  1. Hello to every body, it's my first pay a visit of this weblog; this blog contains remarkable and really fine data in support of readers.

    Also visit my web site :: regex maker

    ReplyDelete