T-SQL: INSTEAD OF Triggers

  • Article

Introduction

As the name suggests the "INSTEAD of trigger", (1) is a trigger, which means that it can be executed on events like INSERT, UPDATE, or DELETE, but not like a regular trigger. An "INSTEAD of trigger" (2) is executed instead of the original operation, and not combining with the operation. INSTEAD OF triggers override the standard actions of the triggering statement. It can be used to bypass the statement and execute a whole different statement, or just help us check and examine the data before the action is done.

 Note!
This article focus on INSTEAD of trigger. For more information about trigger in general please check this article. In this article, unless specifically mention regular trigger, we will use the term trigger, as short name for INSTEAD of trigger.

We will go step by step, creating different triggers, and discuss different cases. This article is built as "step-by-step" tutorial.

** All the scripts from this article can be found and download here: https://gallery.technet.microsoft.com/INSTEAD-OF-Triggers-72109272

Preparation

Preparation, Creating simple table and view for examining the power of INSTEAD of trigger

-- Create simple base table
CREATE TABLE dbo.Ari_People_Tbl
   (
    PersonID         INT IDENTITY(1,1) NOT FOR REPLICATION PRIMARY KEY,
    PersonName        nvarchar(100) UNIQUE,
    PersonAddress     nvarchar(100),
    PersonBirthdate   datetime
   )
GO
 
-- Create simple table with FOREIGN KEY
CREATE TABLE dbo.Ari_Employees_Tbl
   (
    EmployeeID       INT IDENTITY PRIMARY KEY,
    PersonID         INT  UNIQUE,
    Department       nvarchar(100),
    Salary           money,
    CONSTRAINT Employee_People_fk FOREIGN KEY (PersonID) REFERENCES  Ari_People_Tbl (PersonID)
   )
GO
 
-- create simple view from one table
CREATE VIEW dbo.Ari_People_v AS
SELECT
    P.PersonID, P.PersonName, P.PersonBirthdate, P.PersonAddress
FROM Ari_People_Tbl P
GO
 
-- Create complex view based on several tables
CREATE VIEW dbo.Ari_Employees_v AS
SELECT
    P.PersonID, P.PersonName, P.PersonBirthdate, P.PersonAddress,
    E.EmployeeID, E.Department, E.Salary
FROM dbo.Ari_Employees_Tbl E
left join dbo.Ari_People_Tbl P on  E.PersonID = P.PersonID
GO

INSTEAD of trigger, create and explain

INSTEAD OF triggers can be defined on tables or views;

-- Create INSTEAD of trigger on table
CREATE TRIGGER dbo.Ari_People_ioTrig ON dbo.Ari_People_Tbl
INSTEAD OF  INSERT AS  BEGIN
    SET NOCOUNT ON
    -- Here we can impliment any logic that we need to execute INSTEAD of the insert stetement
    -- In this example we just print information and do the simple insert statement
    declare @C NVARCHAR(10) = CONVERT(NVARCHAR(10), (select count(*) from inserted))
    Print 'Trigger Ari_People_ioTrig: Insert started, trying to insert ' + @C
    insert dbo.Ari_People_Tbl (PersonName, PersonAddress, PersonBirthdate) 
    select I.PersonName, I.PersonAddress, I.PersonBirthdate
    from inserted I
END
GO
 
-- Create INSTEAD of trigger on view
CREATE TRIGGER dbo.Ari_People_v_ioTrig ON dbo.Ari_People_v
INSTEAD OF  INSERT AS  BEGIN
    SET NOCOUNT ON
    -- Here we can impliment any logic that we need to execute INSTEAD of the insert stetement
    -- In this example we just print information and do the simple insert statement
    declare @C NVARCHAR(10) = CONVERT(NVARCHAR(10), (select count(*) from inserted))
    Print 'Trigger Ari_People_v_ioTrig: Insert started, trying to insert ' + @C
     
    insert dbo.Ari_People_Tbl (PersonName, PersonAddress, PersonBirthdate) 
    select I.PersonName, I.PersonAddress, I.PersonBirthdate
    from inserted I
END

Let's test our triggers:

-- Test 01: insert one row to table
insert dbo.Ari_People_Tbl (PersonName, PersonAddress, PersonBirthdate)
values ('Ronen', 'Israel',  '2014-02-27')
GO
/*************************************************************** Output:
 Trigger Ari_People_ioTrig: Insert started, trying to insert  1
 (1 row(s) affected)
*/
 
SELECT * from dbo.Ari_People_Tbl
GO
/*************************************************************** Result:
PersonID    PersonName    PersonAddress   PersonBirthdate
----------- -----------   -------------- -----------------------
1           Ronen         Israel         2014-02-27 00:00:00.000
(1 row(s) affected)
*/

We can see that the data was inserted throw the INSTEAD of trigger, and we have 1 row in the table.

-- Test 02: insert one row to view
insert dbo.Ari_People_v (PersonName, PersonAddress, PersonBirthdate)
values ('Ariely', 'Israel',  '2014-02-27')
GO
/*************************************************************** Result:
Trigger Ari_People_ioTrig: Insert started, trying  to  insert 1
Trigger Ari_People_ioTrig: Insert started, trying  to  insert 1
(1 row(s) affected)
*/

In test 02 we insert one row into the view, which invokes the INSTEAD of trigger defined on it. The INSTEAD of trigger on the view is doing a simple insert to the table, which will cause the INSTEAD of trigger on the table to get invoked. Therefore, both triggers were executed and the output printed two lines.

We can make sure that only one row was actually inserted to the table

SELECT * from dbo.Ari_People_Tbl
GO
/*************************************************************** Result:
PersonID    PersonName  PersonAddress  PersonBirthdate
----------- ----------- -------------- -----------------------
1           Ronen       Israel         2014-02-27 00:00:00.000
2           Ariely      Israel         2014-02-27 00:00:00.000
(2 row(s) affected)
*/

The above example could be implemented using simple trigger on insert.. Once we understand how to write an INSTEAD of trigger, we could move to more complex cases where we can see the power of INSTEAD of trigger. INSTEAD OF triggers extend our ability to modify data which could not be done directly. We will look into some examples to show the power of INSTEAD of trigger.

Modify multiple base tables through a view

INSTEAD OF triggers can provide the logic to modify multiple base tables through a view, which cannot be implemented using AFTER triggers.

-- Create INSTEAD of trigger on complex view
CREATE TRIGGER dbo.Ari_Employees_ioTrig ON dbo.Ari_Employees_v
INSTEAD OF  INSERT AS  BEGIN
    SET NOCOUNT ON
    -- Here we can impliment any logic that we need to execute INSTEAD of the insert stetement
 
    -- Step 1:
    -- We will check if the Person already in people table.
    -- If it is not, then we will insert new people from the inserted table (can be one person or multiple people)
    insert Ari_People_Tbl (PersonName, PersonAddress, PersonBirthdate) 
    select I.PersonName, I.PersonAddress, I.PersonBirthdate
    from inserted I
    where not exists (select P_In.PersonName from Ari_People_Tbl P_In where P_In.PersonName = I.PersonName)
     
    -- Step 2:
    -- Now we can insert the data into the emploees table   
    insert Ari_Employees_Tbl (PersonID, Department, Salary)
    select P.PersonID, I.Department, I.Salary
    from inserted I
    inner join dbo.Ari_People_Tbl P on  P.PersonName = I.PersonName
 
END
GO

It is a common business scenario where we want to give clients the option to use views instead of working directly with the tables. In this case the client doesn't have any clue that the object is not a real table (if the name does not suggest it) and the logic behind the database structure can be hidden from him. The client can use a simple insert statement like this:

-- Test 03: insert one row to complex view with new person name
insert dbo.Ari_Employees_v (PersonName, PersonAddress, PersonBirthdate, Salary, Department)
values (N'Pituach', N'Virtual World', '2000-01-01', 0, N'Communities')
GO
-- Trigger Ari_People_ioTrig: Insert started, trying to insert 1

hide our real database structure

Behind the screen the INSTEAD of trigger executed and the data got inserted to two real tables, whilst giving the client a picture that we have one table named Ari_Employees_v and he inserted the data into this table. Here he even can check the data on this "table":

select * from dbo.Ari_Employees_v
GO

As much as the client care this is the output:

PersonID    PersonName  PersonBirthdate         PersonAddress   EmployeeID  Department     Salary
3                        Pituach     2000-01-01 00:00:00.000    Virtual World   1                        Communities    0.00

While we can see the real data :-)

Manipulating data in an Identity column

INSTEAD OF triggers can provide the logic to deal with queries that try to modify the IDENTITY column on the base tables.
For example A user can try to insert into the people table a new row using an external information about the PersonID column. Since this is an identity column, we might want to create our logic on, how to deal with this type of queries. we can use simple INSTEAD of trigger as we did above, and ignore the inserted data in the IDENTITY column, or we can chose to use more complex logic inside the trigger, like this (Do not use this in production as it is here! This is only example for the explanation):

-- Alter INSTEAD of trigger on table
ALTER TRIGGER dbo.Ari_People_v_ioTrig ON  Ari_People_v
INSTEAD OF  INSERT AS  BEGIN
    SET NOCOUNT ON
    -- Here we can impliment any logic that we need to execute INSTEAD of the insert stetement
    -- In this example we just print information and do the simple insert statement
    declare @C INT = (select count(*) from inserted)
    Print 'Trigger Ari_People_ioTrig: Insert started, trying to insert ' + CONVERT(NVARCHAR(10), @C)
     
    declare @ID INT = (select PersonID from inserted)
    IF @C = 1 and @ID > 0  and @ID > (IDENT_CURRENT('Ari_People_Tbl') + 99) BEGIN
        SET IDENTITY_INSERT [Ari_People_Tbl] ON
         
        insert dbo.Ari_People_Tbl (PersonID, PersonName, PersonAddress, PersonBirthdate) 
        select I.PersonID, I.PersonName, I.PersonAddress, I.PersonBirthdate
        from inserted I
 
        SET IDENTITY_INSERT [Ari_People_Tbl] OFF
    END
    ELSE  BEGIN
        insert dbo.Ari_People_Tbl (PersonName, PersonAddress, PersonBirthdate) 
        select I.PersonName, I.PersonAddress, I.PersonBirthdate
        from inserted I
    END
END
GO

Now we have a basic logic of dealing with insert value into IDENTITY column, and we can execute the next query

insert dbo.Ari_People_v (PersonID, PersonName, PersonAddress, PersonBirthdate)
values (999, 'TechNet03', 'Global',  '2014-02-27')
GO

Manipulating Computed columns

Computed columns cannot be updated or inserted directly. In most cases we insert the base column which the computed get it's value from. But in some cases we still need an option to deal with queries that try to insert or update the computed column (like given the client work with the view without even knowing that this is not the real table, and that this is a computed column and not a real column). The solution is same as above (dealing with IDENTITY column). While the original query include manipulating the computed column we actually parse the information and manipulate the base columns.

Improving Bulk Insert Error Handling

When running "BULK INSERT" and "INSERT... SELECT... OPENROWSET(BULK..." statements, you can control whether check constraints and triggers are enforced during the bulk load operation, using the FIRE_TRIGGERS, IGNORE_CONSTRAINTS, IGNORE_TRIGGERS, CHECK_CONSTRAINTS hints. By default, the "BULK INSERT" statement and bcp command ignores foreign keys, constraints, and triggers, while the "INSERT… SELECT… OPENROWSET(BULK…" statement executes check constraints and triggers. If FIRE_TRIGGERS is specified for a bulk-import operation, it executes any INSERT and INSTEAD OF triggers that are defined on the table for all rows inserted into the table.

We can use MAXERRORS option in order to ignore all or several errors, like data "conversion error", but we can not directly ignore constraint checking errors! So if we have one row violate the constraint, then the statement will be terminated! It is very annoying and might cause lot of time and resources to monitor one bad record in a milun. No one like to get the message: "The statement has been terminated" after he wait 10 minutes.

BOL: "The MAXERRORS option does not apply to constraint checking"

One simple solution is to use (1) With CHECK constraints disabled. (2) import the data, and then (3) use Transact-SQL statements to remove or fix the invalid data. This solution lead to the situation that our table include bad data, and we might be working with data that is not consist with our rules! This can lead to unexpected bugs in applications that relied on those rules.

Another option is to (1) use INSTEAD of Trigger, (2) insert into our main table only the data which fit our rules (after checking or filtering), (3) and on the same time we can archive the bad records, on another table for future analyzing. This will allow us to continue the bulk insert operation.

 Note :
>> If triggers are enabled in Bulk Importing Data, they are executed once for each batch

Play time: Just for fun

**Don't Do it in production! **

>> Replace insert query with "select query" or RAISERROR, using INSTEAD of trigger, can be a funny joke on April 4th.

Conclusions

>> We can use INSTEAD of trigger to hide our real database structure, using complex views that are open to the user, instead of directly work with the tables.

>> We can use INSTEAD of trigger to modify multiple base tables through a single view.

>> INSTEAD OF triggers we can provide the logic to deal with column types which cannot be directly manipulated, like IDENTITY, ROWVERSION/TIMESTAMP, COMPUTED column.

>> INSTEAD of trigger can be improve our ability for Error Handling. It can be in some cases the best option to deal with Bulk Insert Error Handling.

 Important !

>> INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined by using a DELETE or UPDATE cascading action.

>> Executing triggers can affect the performance of a bulk import operation. For example, a trigger that sends an e-mail message each time a record is imported reduces the speed of a bulk import operation, and creates a flurry of e-mail.

>> SQL Server use row versioning for triggers, and stores the row versions in the version store in tempdb. Before you can bulk import a large batch of data records using triggers, you may need to expand the size of tempdb to accommodate the impact of the triggers on the version store.

Resources and More information

** All the scripts from this article can found and downloaded from here: 
[https://gallery.technet.microsoft.com/INSTEAD-OF-Triggers-72109272

Forum Questions
](https://gallery.technet.microsoft.com/INSTEAD-OF-Triggers-72109272)

More articles regarding Triggers

See Also