Instead Of Update Example – SQL Server

While studying of the 70-461 exam I keep seeing sample exam questions talking about instead of update triggers.

These type of triggers are a DML trigger. There core purposes include enforcing integrity rules and auditing.

Instead of triggers can be used on a view that accesses multiple tables to perform an insert that would not normally be able to be done.

Lets look at one so we can clearly understand the reasoning.

Example of Instead Of Trigger

-----------------------------------------
CREATE TABLE Writers
(
Id int PRIMARY KEY,
Name varchar(100),
Type varchar(100),
StartDate datetime
)

CREATE TABLE WriterDetails
(
WriterDetailsID int PRIMARY KEY,
WriterCode char(11) UNIQUE,
Division varchar(10),
CostPer100Words money,
CONSTRAINT FKwriter FOREIGN KEY (WriterDetailsID)
REFERENCES Writers (Id)
)

---- View to combine the tables

CREATE VIEW WriterComplete AS
SELECT W.id as ID, Name, WriterCode,Division,CostPer100Words, StartDate
FROM Writers W
JOIN WriterDetails WD ON W.id = WD.WriterDetailsID

--------------------------------------------------------

Now lets try and insert some data via the view!

--------------------------------------------------------
INSERT INTO WriterComplete
SELECT 1,'Jonny Twofingers','CHQ375','',7.50,getdate() Startdate
--------------------------------------------------------

You will get an error message, because of multiple tables, and it not being clear which data is going into which, the SQL engine spits the dummy.

Msg 4405, Level 16, State 1, Line 1
View or function ‘WriterComplete’ is not updatable because the modification affects multiple base tables.

This is the type of situation where we can use an instead of trigger to insert the data as we require.

To make it simple, lets say we don’t want to configure the details yet, we will enter the details later and only want the id and name entered into the database.

We will need to create an instead of trigger to do this work.

--------------------------------------------------------
CREATE TRIGGER IO_Trig_INS_Writer ON WriterComplete
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate writer. If none then insert the record
IF (NOT EXISTS (SELECT w.id
FROM writers w JOIN inserted I on i.id = w.id
))
INSERT INTO Writers
SELECT id,Name,'', StartDate
FROM inserted
ELSE PRINT 'Error'
END
--------------------------------------------------------

Now if you rerun the insert code from above it will work and insert one row in the writers table.

I hope this has helped you understand why an instead of trigger would be used, and how it can help with this type of scenario.

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.