ASP.NET, SQL Server, WPF, C#, VB.NET

Hi,  
  
I want to use a trigger on a view.  
  
I have 2 tables (t1 and t2) and a view (v1) which looks to the table t1.  
  
Definitions  
  
CREATE TABLE [t1] (  
  
[ID] [int] IDENTITY (1, 1) NOT NULL ,  
  
[name] [char] (10)  
  
)  
  
CREATE TABLE [t2] (  
  
[ID] [int] IDENTITY (1, 1) NOT NULL ,  
  
[name] [char] (10)  
  
)  
  
I use the following trigger on the view.  
  
CREATE TRIGGER trv1 ON [dbo].[v1]  
  
INSTEAD OF Insert  
  
AS  
  
update t2 set name = 'paul'  
  
When I insert a new value to the view, the values is strored in t1 but  
  
nothing happes to t2.  
  
What's wrong?  
  
Thanks Otto

On Fri, 20 Jan 2006 09:48:29 +0100, Otto Miros wrote:  
  
Hi Otto,  
  
I have no idea. I took your code, added some missing bits (like the view  
  
definition, the insertion of some rows in t2 to see if they are updated  
  
or not and the insert statement into the view to test things), and it  
  
did what I expected it to do. Just copy and paste the code below into  
  
Query Analyzer or Management Studio and execute it.  
  
-- Your table definitions  
  
CREATE TABLE [t1] (  
  
[ID] [int] IDENTITY (1, 1) NOT NULL ,  
  
[name] [char] (10)  
  
)  
  
CREATE TABLE [t2] (  
  
[ID] [int] IDENTITY (1, 1) NOT NULL ,  
  
[name] [char] (10)  
  
)  
  
-- Make sure something is in table t2  
  
INSERT INTO t2 (name)  
  
VALUES ('john')  
  
INSERT INTO t2 (name)  
  
VALUES ('george')  
  
-- Check it  
  
SELECT * FROM t2  
  
go  
  
-- Add the view (absent in your post)  
  
CREATE VIEW v1  
  
AS  
  
SELECT name  
  
FROM   t1  
  
go  
  
-- Create the trigger  
  
CREATE TRIGGER trv1 ON [dbo].[v1]  
  
INSTEAD OF Insert  
  
AS  
  
update t2 set name = 'paul'  
  
go  
  
-- Attempt an insert  
  
INSERT INTO v1 (name)  
  
VALUES ('ringo')  
  
-- Check results. First t1  
  
SELECT * FROM t1  
  
-- Next t2  
  
SELECT * FROM t2  
  
go  
  
-- Clean up  
  
DROP VIEW v1  
  
DROP TABLE t1  
  
DROP TABLE t2  
  
go  
  
Maybe you can explain in some more detail what you did, then I can try  
  
and reproduce it here and look for a fix.  
  
--  
  
Hugo Kornelis, SQL Server MVP

Hi Hugo,  
  
my description wasn't exaclty enough.  
  
I create a view to merge data from 10 tables. I want to determine if one  
  
table has a new row.  
  
If so the view has changed and I think this could be recognized by a trigger  
  
on a view.  
  
So I used your example and evrything is fine, when I use  
  
insert into v1 ....  
  
but doen't work if I use  
  
insert into t1 ...  
  
Maybe my idea is wrong, but i have no other idea to check if one table has  
  
changed. I don't want to create 10 insert triggers, one for each table.  
  
So if you have an idea this will be fine.  
  
Thanks Otto  
  
"Hugo Kornelis" <hugo@perFact.REMOVETHIS.info> schrieb im Newsbeitrag  
  
news:95c5t1t37rhn8cfaeph0jbant2rjkdtmav@4ax.com...

On Mon, 23 Jan 2006 12:12:18 +0100, Otto Miros wrote:  
  
Hi Otto,  
  
Unfortunately, no. A trigger on a view will fire only if the view itself  
  
is used in the insert, update or delete statement.  
  
I guess you'll have to bite the bullet and create the 10 insert triggers  
  
for the 10 tables anyway.  
  
Remember that probably only the first will require real effort. Write  
  
it, test it (don't forget to test multi-row and zero-row operations!),  
  
debug it until it works. Then simply copy and change table and column  
  
names for the remaining 9 tables.  
  
--  
  
Hugo Kornelis, SQL Server MVP