INSTEAD OF INSERT trigger on view in SQL Server

Question

I am using SQL Server 2012:

The query is:

create table t11 (pid int not null, emp varchar(10))
create table t12 (id bigint, fn varchar(100))

create view tesr
as
   select 
       a.pid, b.fn 
   from 
       t11 a 
   join 
       t12 b on a.pid = b.id

create trigger tesr_trig on dbo.tesr
instead of insert
as 
begin
    insert into t11  
       select i.pid, i.emp 
       from inserted i

    insert into t12  
       select i.id, i.fn 
       from inserted i
end

I get the following error while executing:

Msg 207, Level 16, State 1, Procedure tesr_trig, Line 5
Invalid column name 'emp'.
Msg 207, Level 16, State 1, Procedure tesr_trig, Line 6
Invalid column name 'id'.

What is mistake I am making?

There is no column id or emp in the projection of your view — rene
– rene, Commented Apr 4, 2015 at 12:05

StuartLC · Accepted Answer · 2015-04-04 12:13:58Z

4

You are creating the trigger on the view, so the inserted pseudotable will contain the view's columns, and neither emp or id are present.

You'll need to change your view to :

alter view tesr
as
    select a.pid, b.fn, a.emp from t11 a join t12 b on a.pid=b.id

And your trigger needs to be cognaisant of the join in the view between a.pid and b.id, so the view's pid value needs to be inserted into both tables.

ALTER trigger tesr_trig on dbo.tesr
instead of insert
as 
begin
  SET NOCOUNT ON;
  insert into t11(pid, emp)
    select i.pid,i.emp from inserted i
  insert into t12(id, fn)
    select i.pid,i.fn from inserted i
end

Then the view insert:

INSERT INTO tesr(pid, fn, emp) VALUES (1, 'fn', 'emp')

Will insert into the two underlying tables of the view.

edited Apr 4, 2015 at 12:13

answered Apr 4, 2015 at 12:06

StuartLC

108k18 gold badges225 silver badges295 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marko Juvančič · Accepted Answer · 2015-04-04 12:07:22Z

1

Your view doesn't have the columns you're using in the trigger. Even more: there are only two columns in the view and you use three in the trigger.

answered Apr 4, 2015 at 12:07

Marko Juvančič

5,9201 gold badge27 silver badges43 bronze badges

Collectives™ on Stack Overflow

INSTEAD OF INSERT trigger on view in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related