sql- instead of trigger doesnt trigger on basetable from view

Question

When new data is inserted into BASETABLE I want it to make the View (InsteadView)'s trigger to insert data into a different table. It only works when I insert data manually to the view, but not when i insert data into the Basetable.

CREATE TABLE BaseTable
  (PrimaryKey     int PRIMARY KEY IDENTITY(1,1),
   Color          nvarchar(10) NOT NULL,
   Material       nvarchar(10) NOT NULL,
   ComputedCol AS (Color + Material)
  )
GO

--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
GO

--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
  --Build an INSERT statement ignoring inserted.PrimaryKey and 
  --inserted.ComputedCol.
  INSERT INTO anotherTable
       SELECT Color, Material
       FROM inserted
END
GO

INSERT INTO BaseTable (Color, Material) VALUES (N'Red', N'Cloth')

--View the results of the INSERT statement. SELECT PrimaryKey, Color, Material, ComputedCol FROM another table

This is a simple example. The real view is formed from 15 tables. If I can get this to work, it would help me tackle the real thing. — tdjfdjdj
– tdjfdjdj, Commented Jun 23, 2011 at 17:59

billinkc · Accepted Answer · 2011-06-23 17:52:45Z

3

That's working as designed. To restate your problem in non-database terms "I electrified the front doorknob but whenever I walk in the back door, I don't get shocked, only when I go in the front door." If your desire is to get the same shock when you open the back door, then electrify that one too.

The trigger is defined on the View, therefore when you insert data into it, it goes off. Nothing is defined on the base table, ergo no trigger fires. Putting an instead of trigger on the base table will cause it to push data into the alternate table but then you'll never get data into it (unless you perform some bloody hack like add another column and do conditional logic based on the value).

answered Jun 23, 2011 at 17:52

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

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

5 Comments

tdjfdjdj Over a year ago

So why would anyone want to use an Intead of trigger? I dont want to insert data into my view manually. I want it it to grow in size when my basetable grows, and on each insert the trigger writes to another table.

billinkc Over a year ago

What do you mean by you want your view to "grow in size" when the basetable grows? A view is nothing more than just a "saved" query. Instead Of triggers are useful if you need to modify an object (table/view) but don't want to worry about refactoring calling code. The contract between caller and db remains in force but underneath the cover, the trigger pushes data into NewTable instead of the old. At least, that's the scenario I had to use them on. I'm sure others have different use cases.

tdjfdjdj Over a year ago

If basetable has 20 rows, the View I created will also have 20 rows. So lets say I have 5 more tables, and the view joins their rows. Also a few algorithms are then applied. Now I want that new data in that view to write to a new table. Do you see why this is important, and why I need to get the data out of the view on every insert?

billinkc Over a year ago

I'm trying hard to grok you. The view is composited of multiple tables T1 through Tn (either joined or unioned). Adding row 21 to T1/basetable should be reflected in thew view (unless eliminated due to filter/joins). Data inserted to the view should write to Tn+1? Rows written to T1 should go to Tn? What is the problem we are trying to solve?

tdjfdjdj Over a year ago

I think were almost on the same page here. The data in the table is reflected in the view. My goal is every time new data is written to the table, that same data in the view gets written to a different table. On my real database, the View consists of multiple tables with many calculations, so that data is more meaningful to me then the data in its original tables. How can I make an instead of trigger to get the data from the view to a different table?

Collectives™ on Stack Overflow

sql- instead of trigger doesnt trigger on basetable from view

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related