locked
Instead of Insert trigger for updatable View. RRS feed

  • Question

  • Hello,

    I have a View that is a Union of several structurally identical tables.

    COL1, COL2, COL3

    SELECT 'A' As Attrib, * from A
    UNION ALL
    SELECT 'B' As Attrib, * from B
    UNION ALL
    SELECT 'C' As Attrib, * from C

    So the Attrib column in the view is equal to the table that the data comes from.

    I want an INSTEAD OF INSERT and INSTEAD OF UPDATE trigger to update the underlying table.

    If I do the following query.

    INSERT INTO MyView_v ('Attrib', 'COL1', 'COL2', COL3') VALUES ('A', 'One', 'Two' , "Three') 

    would insert a record into Table A.  Likewise if I do:

    INSERT INTO MyView_v ('Attrib', 'COL1', 'COL2', COL3') VALUES ('C', 'One', 'Two' , "Three') 

    The insert would take place in Table C.

    Essentially I want to dynamically select the target table based on the value of the Attrib Column.

    Any SQL Gurus out there that can help me out?

    Thanks

    Bill

    Tuesday, March 18, 2014 8:30 PM

Answers

  • William,

    Practically, we WOULD have knowledge of the tables getting involved in the view - hence, Naomi's solution shud be the simple and best :)

    If you would still want it to operate 'on the fly', you MAY go in with dynamic sql inside the trigger, but beware - it DOES come with its associated problems. There are chances of security issues and performance since this being a trigger gets executed for EVERY insert or update. 'm sure this may not be the best solution.. :)

    create table A(id int,name varchar(10))
    create table B(id int,name varchar(10))
    create table C(id int,name varchar(10))
    GO
    
    insert A select 1,'aa'
    insert B select 2,'bb'
    insert C select 3,'cc'
    GO
    
    create view myview
    as
    SELECT 'A' As Attrib, * from A
    UNION ALL
    SELECT 'B' As Attrib, * from B
    UNION ALL
    SELECT 'C' As Attrib, * from C
    GO
    
    --check available data
    select * from myview
    select * from A
    select * from B
    select * from C
    GO
    
    --Instead of Insert trigger on myview
    create trigger tr_myview_instead_insert
    on myview
    instead of insert
    as 
    begin
    	declare @tab nvarchar(255),@sql nvarchar(max)
    	select @tab=Attrib from inserted
    	select * into #temp from inserted
    	set @sql='
    	insert ['+@tab+']
    		select id,name from #temp where Attrib='''+@tab+''''
    	exec(@sql)
    end
    GO
    
    --Instead of Update Trigger on myview
    create trigger tr_myview_instead_update
    on myview
    instead of update
    as 
    begin
    	declare @tab nvarchar(255),@sql nvarchar(max)
    	select @tab=Attrib from deleted
    	select * into #temp from inserted
    	set @sql='
    	update t
    	set t.name=i.name
    	from ['+@tab+'] t
    	join #temp i on t.id=i.id'
    	exec(@sql)
    end
    GO
    
    --Insert into the view
    insert into myview select 'B',223,'bbc'
    
    --Update view
    update myview set name='bbb' where Attrib='B' and id=223
    
    --check modified data
    select * from myview
    select * from A
    select * from B
    select * from C
    
    --cleanup
    drop table A,B,C
    drop view myview


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, March 19, 2014 3:38 AM

All replies

  • CREATE TRIGGER tr_InsertIntoView ON myView INSTEAD OF INSERT 

    AS

    INSERT INTO TableA (col1, col2, col3)

    SELECT col1, col2, col3 FROM Inserted where Attrib = 'A';

    INSERT INTO TableB (col1, col2, col3)

    SELECT col1, col2, col3 FROM Inserted where Attrib = 'B';

    INSERT INTO TableC (col1, col2, col3)

    SELECT col1, col2, col3 FROM Inserted where Attrib = 'C';

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

    Similar logic should apply for INSTEAD OF UPDATE trigger.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by Elvis Long Thursday, March 20, 2014 7:10 AM
    Tuesday, March 18, 2014 9:39 PM
  • Hi,

    Yes that will work. However what I was looking for was a way to dynamically set the table based on the value in Attrib. 

    Thanks

    Wednesday, March 19, 2014 1:30 AM
  • I think the solution I suggested is the simplest. You still will need to use UNION clause as the insert may touch all 3 tables.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, March 19, 2014 3:21 AM
  • William,

    Practically, we WOULD have knowledge of the tables getting involved in the view - hence, Naomi's solution shud be the simple and best :)

    If you would still want it to operate 'on the fly', you MAY go in with dynamic sql inside the trigger, but beware - it DOES come with its associated problems. There are chances of security issues and performance since this being a trigger gets executed for EVERY insert or update. 'm sure this may not be the best solution.. :)

    create table A(id int,name varchar(10))
    create table B(id int,name varchar(10))
    create table C(id int,name varchar(10))
    GO
    
    insert A select 1,'aa'
    insert B select 2,'bb'
    insert C select 3,'cc'
    GO
    
    create view myview
    as
    SELECT 'A' As Attrib, * from A
    UNION ALL
    SELECT 'B' As Attrib, * from B
    UNION ALL
    SELECT 'C' As Attrib, * from C
    GO
    
    --check available data
    select * from myview
    select * from A
    select * from B
    select * from C
    GO
    
    --Instead of Insert trigger on myview
    create trigger tr_myview_instead_insert
    on myview
    instead of insert
    as 
    begin
    	declare @tab nvarchar(255),@sql nvarchar(max)
    	select @tab=Attrib from inserted
    	select * into #temp from inserted
    	set @sql='
    	insert ['+@tab+']
    		select id,name from #temp where Attrib='''+@tab+''''
    	exec(@sql)
    end
    GO
    
    --Instead of Update Trigger on myview
    create trigger tr_myview_instead_update
    on myview
    instead of update
    as 
    begin
    	declare @tab nvarchar(255),@sql nvarchar(max)
    	select @tab=Attrib from deleted
    	select * into #temp from inserted
    	set @sql='
    	update t
    	set t.name=i.name
    	from ['+@tab+'] t
    	join #temp i on t.id=i.id'
    	exec(@sql)
    end
    GO
    
    --Insert into the view
    insert into myview select 'B',223,'bbc'
    
    --Update view
    update myview set name='bbb' where Attrib='B' and id=223
    
    --check modified data
    select * from myview
    select * from A
    select * from B
    select * from C
    
    --cleanup
    drop table A,B,C
    drop view myview


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Wednesday, March 19, 2014 3:38 AM
  • Hi William,

    Please try below code , i have modified Jayakumar code below, that can work for multiple insert & update, i believe your problem may be getting the table names in trigger

    create table A(id int,name varchar(10))
    create table B(id int,name varchar(10))
    create table C(id int,name varchar(10))
    GO
    insert A select 1,'aa'
    insert B select 2,'bb'
    insert C select 3,'cc'
    GO
    create view myview
    as
    SELECT 'A' As Attrib, * from A
    UNION ALL
    SELECT 'B' As Attrib, * from B
    UNION ALL
    SELECT 'C' As Attrib, * from C
    GO
    --check available data
    select * from myview
    select * from A
    select * from B
    select * from C
    GO
    --Instead of Insert trigger on myview
    create trigger tr_myview_instead_insert
    on myview
    instead of insert
    as 
    begin
    	declare @sql nvarchar(max)=''
    	select @sql = @sql + 'insert into ['+ Attrib+']'+CHAR(10)+
    		'select id,name from inserted where Attrib='''+Attrib+''''+CHAR(10) 
    		from 
    	(select distinct Attrib from inserted) a
    	exec (@sql)
    end
    GO
    --Instead of Update Trigger on myview
    create trigger tr_myview_instead_update
    on myview
    instead of update
    as 
    begin
    	
    	declare @sql nvarchar(max)=''
    	select @sql = @sql + '
    	update t
    	set t.name=i.name
    	from ['+Attrib+'] t
    	join inserted i on t.id=i.id' + CHAR(10) 
    		from 
    	(select distinct Attrib from inserted) a
    	exec (@sql)
    	
    end
    GO


    • Edited by SaravanaC Wednesday, March 19, 2014 5:03 AM
    Wednesday, March 19, 2014 5:02 AM
  • Thanks this worked for me.

    Friday, March 21, 2014 3:01 AM
  • I don't think this is correct code (which you marked) as it's not going to work in you insert several rows with different attributes. I suggest to use simpler code and not dynamic.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, March 21, 2014 3:20 AM