473,372 Members | 863 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Triggers on tables underlying a partitioned view

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
4 2689
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
Erland,

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

-rob (stacdab)

Jul 23 '05 #3
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
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Sporge | last post by:
Hi All Is there any way to determine what the underlying tables are in a view from within a stored procedure? I've written a function that parses the View text but I was hoping for something...
1
by: wireless | last post by:
We recently added a new database at the company. It has only one purpose - to hold massive amounts a daily data generated by telephone calls on a network. The amount of data was so large...
4
by: jeff brubaker | last post by:
Hello, Currently we have a database, and it is our desire for it to be able to store millions of records. The data in the table can be divided up by client, and it stores nothing but about 7...
3
by: EzekiŽl | last post by:
Hello, I need some help with implenting the following: I recently migrated from access to sql server and i now i want to use maintainable permissions on my tables, views, etc. The access...
4
by: Ryan | last post by:
Hello, I am pretty much a newbie with SQL server. I have what is probably a pretty stupid question. In SQL Enterprise manager, is there a way to easily see all triggers, or, even better, all...
17
by: Steve Jorgensen | last post by:
Terminology question: Is there a term for a set of records related directly or indirectly by key value in several tables? For example, a single invoice record and its line item records -or- a...
5
by: Chris | last post by:
I'm attempting to get Instead Of triggers working. My platform in Solaris, DB2 verison 8.1.x (not sure which). If I create two simple tables with 1 row each: create table test_cc_1 (col1...
11
by: Anthony Paul | last post by:
Hello everyone, I am involved in a scenario where there is a huge (SQL Server 2005) production database containing tables that are updated multiple times per second. End-user reports need to be...
3
by: Rafa≥ Bielecki | last post by:
Hi there, I have tables with such structure transaction_YYMM (idx,date,company_id,value) where YYMM stands for 2digits year and month I want to define query (maybe view, procedure):...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.