423,103 Members | 1,428 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,103 IT Pros & Developers. It's quick & easy.

Triggers on tables underlying a partitioned view

P: n/a
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.

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Jul 23 '05 #2

P: n/a
Erland,

Thanks for confirming our observations (man that was quick). Now we
can stop banging our heads.

-rob (stacdab)

Jul 23 '05 #3

P: n/a
stacdab (st*****@gmail.com) writes:
Thanks for confirming our observations (man that was quick). Now we
can stop banging our heads.


A fellow MVP made me aware of this statement in Books Online:

The view will not be updatable if a trigger or cascading update or delete
is defined on one or more member tables.

So maybe the bug is that you are permitted to update at all. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

P: n/a
> So maybe the bug is that you are permitted to update at all. :-)

ha! I have to get a helmet. Thanks again.

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.