Hi

I have a view 'vwTest' that is based on 'table1' and 'table2'. Whenever
a new entry appears in that view i'd like to fire a trigger that inserts
the new data into a third table named 'results'.

If there's an insert into the basetables I can see the new entry in my
view but my trigger doesn't fire and nothing is beeing inserted into the
results table.
I'm quite familiar with triggers on tables but I haven't used them in
combination with views so far.
Can you please tell me where I'm wrong?

Thank you in advance

Michael


----------- TABLES ------------------
CREATE TABLE [table1] (
  [ROWID] [int] IDENTITY (1, 1) NOT NULL ,
  [name] [char] (10)
  )

CREATE TABLE [table2] (
[ROWID] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10)
)

CREATE TABLE [results] (
[ROWID] [int] IDENTITY (1, 1) NOT NULL ,
[name] [char] (10)
)

-------------VIEW-----------------------
CREATE VIEW dbo.vwTest
AS
SELECT     dbo.table1.*
FROM
dbo.table1 INNER JOIN
dbo.table2 ON
dbo.tabelle1.rowid = dbo.table2.rowid

------------TRIGGER---------------------

CREATE TRIGGER trgInsert ON vwTest
INSTEAD OF INSERT
AS

DECLARE @rowid int
DECLARE @name char(10)

DECLARE mycursor CURSOR
FOR
SELECT rowid, name
FROM inserted

OPEN mycursor
FETCH NEXT FROM mycursor
INTO
@rowid, @name

WHILE @@FETCH_STATUS = 0
BEGIN

  INSERT INTO results
    (rowid, name)
    VALUES
    (@rowid, @name)

  FETCH NEXT FROM mycursor
  INTO
  @rowid, @name

END

CLOSE mycursor
DEALLOCATE mycursor

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

This can't be done by a trigger on the view. You'd have to create an
assitional trigger on at least one of the tables that will insert the data
into the destination table when the conditions used in the view are met.

But more importantly, replace that cursor with an INSERT...SELECT statement.


ML

---
http://milambda.blogspot.com/

ML schrieb:
Thanks for your reply

Pity it doesn't work.
But that's a good hint (and I will replace that cursor).

Michael

Nope. In your specific case the triggers must exist on the base tables. But
there might be a way to somehow automate the process of creating them - I
believe the essential part of each trigger will be the same. But for this
we'll need some DDL and sample data.


ML

---
http://milambda.blogspot.com/

Hi, Michael

If you write an "INSTEAD OF INSERT" trigger on a view, it will be fired
when someone executes a "INSERT INTO TheView ...", not when the base
tables are modified. This kind of trigger is especially useful if you
want to update the base table, based on the insert performed on the
view. If you want to do something when the base tables are modified
directly, you will have to write triggers on the base tables, too.

On the other hand, in your trigger you have used a cursor to do
something that is much easier done in a single SQL statement (and with
much better performance, too). You can just use something like this:

INSERT INTO results (rowid, name) SELECT rowid, name FROM inserted

Finally, I recommend you get the habbit of writing "SET NOCOUNT ON" at
the beginning of a trigger, to eliminate the additional "n row(s)
affected" message(s) (that can cause some nasty problems with
applications using ADO or ODBC).

Razvan

Razvan Socol schrieb:
Show quoteHide quoteThanks Razvan

Unfortunately your reply confirms what I have expected. I can't use a
trigger on a view to monitor it's basetables. :(
The point is, that I have to join like 10 tables in that view and I
wasn't very happy about putting a trigger onto each basetable.

Thanks for the hint with my cursor. I didn't realise that it consumes
much more performance than an INSERT ... SELECT statement.

Michael

Really really slow cursor
Problem copying data from column in same table
reverse like?
Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?
inserting single precision data into sql server float column