Designing INSTEAD OF Triggers
The primary advantage of INSTEAD OF triggers is that they enable views that would not be updatable to support updates. A view based on multiple base tables must use an INSTEAD OF trigger to support inserts, updates, and deletes that reference data in more than one table. Another advantage of INSTEAD OF triggers is that they enable you to code logic that can reject parts of a batch while letting other parts of a batch to 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 when an error condition is encountered.
INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key that is defined by using a DELETE or UPDATE cascading action.
Coding this logic as part of an INSTEAD OF trigger prevents all applications that access 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. Additionally, the following approaches to handling errors are shown:
- Duplicate inserts to the
Persontable are ignored, and the information from the insert is logged in the
- Inserts of duplicates to the
EmployeeTableare turned into an UPDATE statement that retrieves the current information into the
EmployeeTablewithout 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. The following 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) )
The following 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 tried 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 )
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 there is 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 FROM inserted ELSE -- Log an 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 there is 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 FROM inserted ELSE --If there is a 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 FROM EmployeeTable E, inserted I WHERE E.SSN = I.SSN END