INSTEAD OF Triggers

Using INSTEAD OF Triggers

INSTEAD OF triggers override the standard actions of the triggering statement (INSERT, UPDATE, or DELETE). For example, an INSTEAD OF trigger can be defined to perform error or value checking on one or more columns, and then perform additional actions before inserting the record. For instance, when the value being updated in an hourly wage column in a payroll table exceeds a specified value, a trigger can be defined to either produce an error message and roll back the transaction, or insert a new record into an audit log before inserting the record into the payroll table.
INSTEAD OF triggers can be defined on either tables or views; however, INSTEAD OF triggers are most useful for extending the types of updates a view can support. For example, INSTEAD OF triggers can provide the logic to modify multiple base tables through a view or to modify base tables that contain these columns:

• timestamp data type
• Computed columns
• Identity columns

Designing INSTEAD OF Triggers

The primary advantage of INSTEAD OF triggers is that they allow views that would not be updatable support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. Another advantage of INSTEAD OF triggers is that they allow you to code logic that can reject parts of a batch while allowing other parts of a batch succeed.
An INSTEAD OF trigger can take actions such as:

• Ignoring parts of a batch.
• Not processing a part of a batch and logging the problem rows.
• Taking an alternative action if an error condition is encountered.

Note  INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined with a DELETE or UPDATE action.

Coding this logic as part of an INSTEAD OF trigger prevents all applications accessing the data from having to reimplement the logic.
In the following sequence of Transact-SQL statements, an INSTEAD OF trigger updates two base tables from a view. In addition, two approaches to handling errors are shown:

• Duplicate inserts to the Person table are ignored, and the information from the insert is logged in the PersonDuplicates table.
• Inserts of duplicates to the EmployeeTable are turned into an UPDATE statement that retrieves the current information into the EmployeeTable without generating a duplicate key violation.

The Transact-SQL statements create two base tables, a view, a table to record errors, and the INSTEAD OF trigger on the view. These tables separate personal and business data and are the base tables for the view:

CREATE TABLE Person

   (

    SSN         char(11) PRIMARY KEY,

    Name        nvarchar(100),

    Address     nvarchar(100),

    Birthdate   datetime

   )

CREATE TABLE EmployeeTable

   (

    EmployeeID       int PRIMARY KEY,

    SSN              char(11) UNIQUE,

    Department       nvarchar(10),

    Salary           money,

    CONSTRAINT FKEmpPer FOREIGN KEY (SSN)

    REFERENCES Person (SSN)

   )

This view reports all relevant data from the two tables for a person:

CREATE VIEW Employee AS

SELECT P.SSN as SSN, Name, Address,

       Birthdate, EmployeeID, Department, Salary

FROM Person P, EmployeeTable E

WHERE P.SSN = E.SSN

You can record attempts to insert rows with duplicate social security numbers. The PersonDuplicates table logs the inserted values, the name of the user who attempted the insert, and the time of the insert:

CREATE TABLE PersonDuplicates

   (

    SSN           char(11),

    Name          nvarchar(100),

    Address       nvarchar(100),

    Birthdate     datetime,

    InsertSNAME   nchar(100),

    WhenInserted  datetime

   )

The INSTEAD OF trigger inserts rows into multiple base tables from a single view. Attempts to insert rows with duplicate social security numbers are recorded in the PersonDuplicates table. Duplicate rows in the EmployeeTable are changed to update statements.

CREATE TRIGGER IO_Trig_INS_Employee ON Employee

INSTEAD OF INSERT

AS

BEGIN

SET NOCOUNT ON

-- Check for duplicate Person. If no duplicate, do an insert.

IF (NOT EXISTS (SELECT P.SSN

      FROM Person P, inserted I

      WHERE P.SSN = I.SSN))

   INSERT INTO Person

      SELECT SSN,Name,Address,Birthdate,Comment

      FROM inserted

ELSE

-- Log attempt to insert duplicate Person row in PersonDuplicates table.

   INSERT INTO PersonDuplicates

      SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()

      FROM inserted

-- Check for duplicate Employee. If no duplicate, do an insert.

IF (NOT EXISTS (SELECT E.SSN

      FROM EmployeeTable E, inserted

      WHERE E.SSN = inserted.SSN))

   INSERT INTO EmployeeTable

      SELECT EmployeeID,SSN, Department, Salary,Comment

      FROM inserted

ELSE

--If duplicate, change to UPDATE so that there will not

--be a duplicate key violation error.

   UPDATE EmployeeTable

      SET EmployeeID = I.EmployeeID,

          Department = I.Department,

          Salary = I.Salary,

          Comment = I.Comment

   FROM EmployeeTable E, inserted I

   WHERE E.SSN = I.SSN

END

INSTEAD OF DELETE Triggers

INSTEAD OF DELETE triggers can be defined on a view or table to replace the standard action of the DELETE statement. Usually, the INSTEAD OF DELETE trigger is defined on a view to modify data in one or more base tables.
DELETE statements do not specify modifications to existing data values. DELETE statements specify only the rows that are to be deleted. The inserted table passed to a DELETE trigger is always empty. The deleted table sent to a DELETE trigger contains an image of the rows as they existed before the UPDATE statement was issued. In the case of an INSTEAD OF DELETE trigger on a view or table, the format of the deleted table is based on the format of the select list defined for the view.

Note  INSTEAD OF DELETE triggers cannot be defined on a table that has a foreign key defined with a DELETE action

SQL Profiler

SQL Profiler is a tool that captures Microsoft® SQL Server™ 2000 events from a server. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. SQL Profiler is used for activities such as:

• Stepping through problem queries to find the cause of the problem.
• Finding and diagnosing slow-running queries.
• Capturing the series of SQL statements that lead to a problem. The saved trace can then be used to replicate the problem on a test server where the problem can be diagnosed.
• Monitoring the performance of SQL Server to tune workloads.

SQL Profiler also supports auditing the actions performed on instances of SQL Server. Audits record security-related actions for later review by a security administrator. SQL Server 2000 auditing meets C2 security certification requirements