instead of trigger on a view

[vindi]

Hi all, I have a question about instead of trigger.
I have a situation in which I have 2 tables A and B, and a view V which is derived from both these tables.
Now I need to have a table T which, gets its values from the view V. However, since we can write only instead of triggers on views, and I need view V for other purposes, I created a temporary view V1, from view V. I then wrote an instead of trigger on this temp view which inserts into the table T instead of the temp view V1.
the problem here is, if i manually enter data into the temp view V1, the trigger works correctly, but if I update the tables A&B, which in turn updates view V, which in turn changes temp view V1, the trigger does not work. It inserts in the temp view and not in the table T.
How do I fix this? Or is there any easier way to implement what im doin? After reading what i just wrote, it does seem a little complicated. Is this got something to do with bulk inserts?
Someone please help me out on this. I'm using SQL Server 2000 btw.

 

[swampBoogie]

Need some clarifications.

What do you mean by

Now I need to have a table T which, gets its values from the view V.

?

and

and I need view V for other purposes

Why does that stop you from writing a instead of trigger for V instead of V1?

I don't understand what you are trying to accomplish.

 

[vindi]

Thanks for your reply, I'll try to make things more clear,

I have a lot of convoluted data which i need to JOIN from 3 tables to make a view, which is say V.(I said two tables before since i wanted to make it easier to explain). I decided to make a view so that whenever the tables are updated, the view of course will get modified. I need this view as it helps me display my data.

Now, I need to extract the unique(distinct) rows from this view and put them in a new table T, dynamically whenever data changes in any of the base tables.I also need to have an extra (previously nonexistent)column attached to these rows(which is why i have to use a table and not another view because, in a view,i cannot have a column not associated with any table ?)

So to populate this table i now need to have a temporary view which will extract the unique(distinct) rows from the base View, and that data will be entered into the new table(which has an extra column, default set to null).

I have to create this new temporary view(V1) because, a view can only have an INSTEAD OF trigger. If i have the trigger written for the base view V, then the base view will never get populated.

I hope its a little more clear. Its very difficult to explain the whole thing and im trying to be as clear as I can be. If u disagree, ask me more questions!!!

So the problem here is, if i manually insert a row in the temporary view, then the instead of trigger gets executed and the new table gets populted correctly.

but if I run a PERL code, which populates the main base tables(with multiple rows of data at a time), then the instead of trigger does not run. That is, The parent tables are modified, the view V is modified and the temp view V1 gets modified, but the child table of view V1 does not get modified i.e. the trigger does not insert into it INSTEAD Of the temp view.

Whoa...that was tiring. Hope it helps

 

[tdatgod]

Hi,
You can only write

Instead Of

triggers on Views?

is this an ANSI SQL restriction or a SQL SERVER restriction?

If this is a SQL server restriction I suggest asking your question in the SQL SERVER FORUM forum183.