|
|
Posted by Erland Sommarskog on 06/09/05 01:21
stacdab (stacdab@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, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|