July 10, 2012 at 1:35 am
I need some help with a problem involving partitioned views. I think I have been able to narrow down my larger problem to a smaller set of symptoms. I will try to describe them in a general way.
My server is running SQL SQL 2008 sp2 x64 (sp3 didn't help).
I have 3 databases on this server: DB_DataAU, DB_DataUS and DB_Views.
DB_DataAU and DB_DataUS contain data only. The data in each database belongs to one country. Each country is identified by a field in every table called IdCountry. Every table within a database has a constraint on it limiting the value of IdCountry to only be one value. Otherwise the structure of the two databases is identical. Here is an example of the tables created in these databases:
USE DB_DataAU
GO
CREATE TABLE dbo.tblTableA
(
[IdTableA_PK] uniqueidentifier NOT NULL ROWGUIDCOL,
[IdCountry] int NOT NULL,
[Data1] varchar(50) NULL,
[Data2] varchar(50) NULL,
CONSTRAINT [PK_tblTableA] PRIMARY KEY CLUSTERED
(
[IdTableA_PK] ASC,
[IdCountry] ASC
)
)
ALTER TABLE dbo.tblTableA ADD CONSTRAINT [DF_tblTableA_PK] DEFAULT (newsequentialid()) FOR [IdTableA_PK]
GO
ALTER TABLE dbo.tblTableA WITH CHECK ADD CONSTRAINT [CHK_tblTableA_IdCountry] CHECK ([IdCountry]=(61))
GO
USE DB_DataUS
GO
CREATE TABLE dbo.tblTableA
(
[IdTableA_PK] uniqueidentifier NOT NULL ROWGUIDCOL,
[IdCountry] int NOT NULL,
[Data1] varchar(50) NULL,
[Data2] varchar(50) NULL,
CONSTRAINT [PK_tblTableA] PRIMARY KEY CLUSTERED
(
[IdTableA_PK] ASC,
[IdCountry] ASC
)
)
ALTER TABLE dbo.tblTableA ADD CONSTRAINT [DF_tblTableA_PK] DEFAULT (newsequentialid()) FOR [IdTableA_PK]
GO
ALTER TABLE dbo.tblTableA WITH CHECK ADD CONSTRAINT [CHK_tblTableA_IdCountry] CHECK ([IdCountry]=(1))
GO
The database DB_Views contains not data but only contains partitioned views. It is used to access the tables in the country specific databases (to hide the complexity and changes from any applications). They are very simple and just select all columns. Here is an example view:
USE DB_Views
GO
CREATE VIEW [dbo].[vwTableA]
AS
SELECT[IdTableA_PK],
[IdCountry],
[Data1],
[Data2]
FROM[DB_DataAU].[dbo].[tblTableA]
UNION ALL
SELECT[IdTableA_PK],
[IdCountry],
[Data1],
[Data2]
FROM[DB_DataUS].[dbo].[tblTableA]
GO
This all works perfectly. I can add, update and delete data using the views and the changes appear in the correct underlying database.
Now comes the problem. I create a trigger on tblTableA in the the DB_DataAU and DB_DataUS databases. All I did was write the current date and time to a log table so I could confirm the trigger fires.
CREATE TRIGGER dbo.TestInsertTrigger
ONdbo.tblTableA
FOR INSERT AS
insert into dbo.tblTriggerLog (TriggerMessage)
values ('Test Trigger insert')
GO
If I insert data directly into the table the trigger fires as expected. If I insert data into the view, the trigger does not fire. On a hunch, I reversed the order of the select statemets in the view (to change the database order) as follows.
CREATE VIEW [dbo].[vwTableA]
AS
SELECT[IdTableA_PK],
[IdCountry],
[Data1],
[Data2]
FROM[DB_DataUS].[dbo].[tblTableA]
UNION ALL
SELECT[IdTableA_PK],
[IdCountry],
[Data1],
[Data2]
FROM[DB_DataAU].[dbo].[tblTableA]
GO
This time the when I insert data into the view, the trigger fires. It tells me that even though I created the databases in the same way and used the same scripts to create the tables, views, etc there must be somethings different. I have been searching for settings, permissions and options across the databases, tables and views but cannot find the cause of my problem.
Unfortunately my simplest options are out. The triggers are merge replication triggers so I can't move them. I actually have 5 "country data" databases and they are replicated to servers in two locations and reshuffling the order of the views is not consistent in workign around the problem.
Any help would be very much appreciated. This is now the third time I have had this happen (when setting up merge replication on new databases). Even if I delete and rebuild the databases it doesn't always fix the problem.
Regards,
Jason
July 10, 2012 at 7:41 pm
Further testing has revealed additional information. If I insert data into the view, the data is going into the correct database but is firing the trigger from the other database.
INSERT INTO [vwTableA]
SELECT NEWID() as [IdTableA_PK],
61 as [IdCountry],
'Test 001 AU' as [Data1],
'Tesing View' as [Data2]
The data goes into [DB_DataAU] and fires the trigger in [DB_DataAU].
INSERT INTO [vwTableA]
SELECT NEWID() as [IdTableA_PK],
1 as [IdCountry],
'Test 002 US' as [Data1],
'Tesing View' as [Data2]
The data goes into [DB_DataUS] and but fires the trigger in [DB_DataAU].
Any ideas how to fix the dependency?
Regards,
Jason
July 10, 2012 at 10:51 pm
[font="Tahoma"]Could there be some other triggers on this table[/font]
July 11, 2012 at 12:07 am
I had the same thought but I couldn't find any additional triggers. I was wondering if an "instead" trigger may be firing. I may have finally found something which may be an answer to my problem but I can't explain how why or how it happened. Somehow object ids of tblTableA is the same in both databases. An this is with me creating both the databases and tables from scratch i.e. I did not restore them from any backups. I recreated the table in one of the databases, ensured the object ids were different then performed my tests again. The trigger now works perfectly when inserting data directly into the tables and through the partitioned view.
So the acid test, the production servers with the problems. Two databases on the same server (both created from scratch using scripts) where the triggers are not firing. The object ids of the tables in the first database are all unique within that database. Checking the second database, the object ids are a perfect match to the first database for every table... that's all 38 tables. Same databases on a second server again a perfect match for all object ids.
A second test comes from a different server. Again two databases but in this case, only 2 of 12 tables have problems with the trigger. Sure enough the two problem tables have matching ids in the two databases.
The conclusion I have at this stage is that for a partitioned view where the underlying tables have trigger (and possibly the tables have the sames but in different databases) the tables need to have unique object ids.
As I said, I didn't create any of these databases by restoring them, so if I can work out how it happened I'll post the cause here.
An easy enough fix for the new databases, I'll just drop recreate the tables. Fixing the other two tables which are in use (and using merge replication) is going to take some planning though.
July 11, 2012 at 9:43 pm
I found my cause and learnt somthing new. The internal object ids may not be sequential, but they are not random. I had built my database tables using a script so I created 4 test databases an ran the script againt 3 of them. Exactly the same object ids for each table. For the last database I create one additional table first. Then ran my script. Same ids but they were shifted "up" my table list by one.
My solution was to randomly create and drop tables before running my actual table generation script then verifying the object ids were unique across the databases. the triggers worked perfectly for the tables and partitioned view. I setup merge replication on all the databases and it functioned correctly first time.
Regards,
Jason
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply