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.
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, Creating simple table and view for examining the power of INSTEAD of trigger
-- Create simple base table
CREATE
TABLE
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
Ari_Employees_Tbl
EmployeeID
INT IDENTITY
INT
UNIQUE
Department nvarchar(100),
Salary money,
CONSTRAINT
Employee_People_fk
FOREIGN
(PersonID)
REFERENCES
Ari_People_Tbl (PersonID)
-- create simple view from one table
VIEW
Ari_People_v
AS
SELECT
P.PersonID, P.PersonName, P.PersonBirthdate, P.PersonAddress
FROM
Ari_People_Tbl P
-- Create complex view based on several tables
Ari_Employees_v
P.PersonID, P.PersonName, P.PersonBirthdate, P.PersonAddress,
E.EmployeeID, E.Department, E.Salary
Ari_Employees_Tbl E
left
join
on
E.PersonID = P.PersonID
INSTEAD OF triggers can be defined on tables or views;
-- Create INSTEAD of trigger on table
TRIGGER
Ari_People_ioTrig
ON
INSTEAD
OF
INSERT
BEGIN
SET
NOCOUNT
-- 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
Ari_People_Tbl (PersonName, PersonAddress, PersonBirthdate)
I.PersonName, I.PersonAddress, I.PersonBirthdate
inserted I
END
-- Create INSTEAD of trigger on view
Ari_People_v_ioTrig
'Trigger Ari_People_v_ioTrig: Insert started, trying to insert '
Let's test our triggers:
-- Test 01: insert one row to table
values
'Ronen'
'Israel'
'2014-02-27'
/***************************************************************
Output
:
Trigger Ari_People_ioTrig:
Insert
started, trying
to
1
(1 row(s) affected)
*/
*
/*************************************************************** Result:
PersonID PersonName PersonAddress PersonBirthdate
----------- ----------- -------------- -----------------------
1 Ronen Israel 2014-02-27 00:00:00.000
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
Ari_People_v (PersonName, PersonAddress, PersonBirthdate)
'Ariely'
Trigger Ari_People_ioTrig: Insert
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
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.
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
Ari_Employees_ioTrig
-- 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)
where
not
exists (
P_In.PersonName
Ari_People_Tbl P_In
P_In.PersonName = I.PersonName)
-- Step 2:
-- Now we can insert the data into the emploees table
Ari_Employees_Tbl (PersonID, Department, Salary)
P.PersonID, I.Department, I.Salary
inner
P.PersonName = I.PersonName
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
Ari_Employees_v (PersonName, PersonAddress, PersonBirthdate, Salary, Department)
(N
'Pituach'
, N
'Virtual World'
'2000-01-01'
, 0, N
'Communities'
-- Trigger Ari_People_ioTrig: Insert started, trying to insert 1
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":
As much as the client care this is the output:
PersonID PersonName PersonBirthdate PersonAddress EmployeeID Department Salary
3
Pituach
2000
-01
00:
00.000
Virtual World
Communities
0.00
While we can see the real data :-)
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
@C
= (
inserted)
+
(NVARCHAR(10), @C)
@ID
IF @C = 1
and
@ID > 0
@ID > (IDENT_CURRENT(
'Ari_People_Tbl'
) + 99)
IDENTITY_INSERT [Ari_People_Tbl]
Ari_People_Tbl (PersonID, PersonName, PersonAddress, PersonBirthdate)
I.PersonID, I.PersonName, I.PersonAddress, I.PersonBirthdate
OFF
ELSE
Now we have a basic logic of dealing with insert value into IDENTITY column, and we can execute the next query
Ari_People_v (PersonID, PersonName, PersonAddress, PersonBirthdate)
(999,
'TechNet03'
'Global'
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.
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.
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.
>> 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.
>> 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.
** All the scripts from this article can found and downloaded from here: https://gallery.technet.microsoft.com/INSTEAD-OF-Triggers-72109272 Forum Questions
More articles regarding Triggers