stacdab (st*****@gmail.com) writes:
We have a partitioned view with 4 underlying tables. The view and each
of the underlying tables are in seperate databases on the same server.
Inserts and deletes on the view work fine. We then add insert and
delete triggers to each of the underlying tables. The triggers modify
a different set of tables in the same database as the view (different
than the underlying table). The problem is those triggers aren't fired
when inserting or deleteing via the view. Inserting or deleteing the
underlying table directly causes the the triggers to fire, but not when
the tables are accessed as a result of using the view.
Am I missing something? The triggers are 'for insert' and 'for
delete'. No 'instead of' or 'after' triggers.
This appears to be a bug. I was able to reproduce the problem. More
exactly the problem happens when if the underlying table is in a
different database than the view. In my repro one of the table is in
the same database as the view and it fires.
The reason I believe that it is a bug in SQL 2000, is that when I run
the repro in SQL 2005, I get the expected output.
I will report the bug to Microsoft. However, if you need this to work,
you should open a case with Microsoft. This could result in a hotfix,
depending on your support contract etc.
Here is my repro:
USE tempdb
go
CREATE TABLE t1 (a int NOT NULL PRIMARY KEY CHECK (a < 100),
b datetime NOT NULL)
go
CREATE TRIGGER t1_tri ON t1 FOR INSERT AS
PRINT 'a < 100'
go
CREATE DATABASE pviewtest
go
USE pviewtest
go
CREATE TABLE t2 (a int NOT NULL PRIMARY KEY CHECK (a >= 100),
b datetime NOT NULL)
go
CREATE TRIGGER t2_tri ON t2 FOR INSERT AS
PRINT 'a >= 100'
go
CREATE VIEW v AS
SELECT a, b FROM tempdb.dbo.t1
UNION ALL
SELECT a, b FROM t2
go
INSERT tempdb.dbo.t1 (a, b) VALUES(11, getdate())
INSERT t2 (a, b) VALUES(111, getdate())
INSERT v VALUES(12, getdate())
INSERT v VALUES(112, getdate())
go
DROP VIEW v
DROP TABLE t2
go
USE tempdb
go
DROP TABLE t1
DROP DATABASE pviewtest
Output:
a < 100
(1 row(s) affected)
(1 row(s) affected)
a >= 100
(1 row(s) affected)
(1 row(s) affected)
a >= 100
One more 'a < 100' is expected.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
