Change Default DML Behavior

DML, Data Manipulation Language, is used to add data to table and modify existing rows in tables. There are 3 commands

1. Insert : insert records to a table
2. Delete: remove records to a table
3. Update: modify records in a table

In SQL Server, you are allowed to change the default behaviors of those 3 commands. For instance, while inserting a record, the new record can be applied to a table as an update(can be a delete as well).

This is implemented by creating INSTEAD OF TRIGGER. The meaning of instead of trigger is INSTEAD OF <OPERATION> TRIGGER, which you can have INSTEAD OF INSERT, DELETE, and UPDATE. It can be created on a table or a view. When a view has a INSTEAD OF trigger, it will become modifiable even it’s a read-only view such as a view with aggregates. The code within the trigger instructs SQL Server how to perform the operation. For an instance below

use test
go
if object_id('test') is not null
	drop table test
go
create table test(ID int not null identity(1,1), Col1 varchar(10), Ver rowversion)
go
create trigger tri_test on test
instead of insert
as
begin
	select * from inserted
	select * from deleted
	return
end
go
insert into test(Col1) values('a')
select * from test
/*
ID          Col1       Ver
----------- ---------- ------------------
0           a          0x

(1 row(s) affected)

ID          Col1       Ver
----------- ---------- ------------------

(0 row(s) affected)

(1 row(s) affected)

ID          Col1       Ver
----------- ---------- ------------------

(0 row(s) affected)
*/

A record is inserted to table test with INSERT command, but nothing happens to the table due to the instead of trigger. This is because the trigger does not tell SQL Server to perform any data manipulation operation. Now if we change the instead of trigger like below.

alter trigger tri_test on test
instead of insert
as
begin
	set nocount on
	insert into test(Col1)
		select Col1 from inserted
	return
end
go
insert into test(Col1) values('a')
select * from test
go
/*
(1 row(s) affected)
ID          Col1       Ver
----------- ---------- ------------------
1           a          0x00000000000007D7

(1 row(s) affected)
*/

What we get is one row in test table. Every table or view can only have up to 3 instead of triggers, INSTEAD OF INSERT, DELETE, and UPDATE. Instead of triggers cannot be nested by an instead of trigger on the same table in general but if an instead of trigger on the first table is inserting a row to a second table which has an instead of trigger on it, the instead of trigger on the second table will be fired. If the instead of trigger on the second table inserting record back to the first table, the instead of trigger on the first table will be fired as well.

Instead of triggers are useful when you try to build an database object (table or view) which provides customized data access patterns. For instance, there is a table called Settings. The setting items in this table is pre-defined. Setting names and number of settings are not allowed to be changed. You also want to have following features

1. When end users issue insert command, if setting name exists, perform an update to the setting values, otherwise discard the changes
2. When end users issue delete commnad, set the setting values to NULL
3. When end users issue update command, update setting values only

use master
go
if DB_ID('test') is not null
begin
	alter database test set single_user with rollback immediate
	drop database test
end
go
create database test
go
use test
go
create table Settings
(
	SettingName varchar(20) primary key,
	SettingValue varchar(20)
)
go
insert into Settings values('Setting1', 'Value1')
insert into Settings values('Setting2', 'Value2')
go
create trigger TRI_Settings_Instead on Settings
instead of insert, delete, update
as
begin
	if @@rowcount = 0
		return;
	set nocount on
	update s
		set SettingValue = null
	from Settings s
		inner join deleted d on d.SettingName = s.SettingName

	update s
		set SettingValue = i.SettingValue
	from Settings s
		inner join inserted i on i.SettingName = s.SettingName
end
go
select * from Settings
go
/*
SettingName          SettingValue
-------------------- --------------------
Setting1             Value1
Setting2             Value2

-- now you have 2 records in the table.
*/
-- now you have 2 records in the table. issuing following query will not remove the record
delete settings
select * from Settings
/*
SettingName          SettingValue
-------------------- --------------------
Setting1             NULL
Setting2             NULL
*/
go
-- let's insert a new setting value, nothing is changed
insert into Settings values('Setting3', 'Value3')
select * from Settings
go
/*
SettingName          SettingValue
-------------------- --------------------
Setting1             NULL
Setting2             NULL
*/
--then, insert a duplicated record. An update is performed instead of insert
insert into Settings values('Setting2', 'Value2')
select * from Settings
go
/*
SettingName          SettingValue
-------------------- --------------------
Setting1             NULL
Setting2             Value2
*/
go
--What if I perform an update?
update Settings set SettingValue = 20 where SettingName = 'Setting1'
select * from Settings
go
SettingName          SettingValue
-------------------- --------------------
Setting1             20
Setting2             Value2

Settings table now is a very special table comparing to other tables – the behaviors of DML commands on different than regular tables.