Synchronised Drowning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Synchronised Drowning

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-10-17 : 06:44:30

I have two tables that are related 1-1 which I have had to import from MS Access

CREATE TABLE [dbo].[tbl_QuestEvents] (
[EventID] [int] NOT NULL ,
[INTID] [int] NULL
-- ... More fields
) ON [PRIMARY]

AND

CREATE TABLE [dbo].[tbl_QReplyCodeDate] (
[QEventID] [int] NOT NULL ,
[QReplyDate] [varchar] (30) NULL) ON [PRIMARY]

EventID has a 1-1 relationship with QEventID

Some of the EventID's need to be changed which means that I need to change the corresponding QEventID.

So for example I may have an EventID of 100 (and therefore a corresponding QEventID of 100) which I need to alter to 200 (which means I have to alter the QEventID to 200)

Does anyone have any suggestions on the best approach that will ensure that all the data in both tables is updated correctly.

Thanks

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-10-17 : 06:49:18

I dont whether it makes .. sense but you can try ..

First set the relationship between the 2 tables.. and then can set
On Update Cascade...

so it will reflect in the other table.. as it changed in the primary table..

Complicated things can be done by simple thinking

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 07:10:43

... and if you have an FK and do NOT have a Update Cascade [was that available in SQL7?] you will NOT be able to change it [change on parent will fail, single UPDATE "transaction block" which updates both tables won't be allowed, you could INSERT a dupe on Primary table, modify Secondary table, then delete old record from Primary, but that's a right arse - and will probably fall foul of other constraints on the Primary Table - e.g. the PK!!] - so if you go for a trigger or somesuch then you won't be able to use FK to ensure referential integrity - and will have to enforce that through trigger too.

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-10-17 : 07:19:45

Thanks for this guys. I was sort of thinking of Cascade Update but as Kristen suggests it doesn't exist in SQL server 7. I could do a one off update in SQL Server 2000 and then move the database over to SQL Server 7. I am getting the feeling that this is going to be even more of a PITA than I thought

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 08:37:05

Its OK if you have no FK ... but I dunno if you think that's a bit daft ... ?

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-10-17 : 13:30:23

I don't have an FK at the moment as I've only just put the tables into SQL.

Thanks Kristen

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-17 : 13:38:33

would this work for you??
there's only one problem... you need to have SomeOtherColumnsThatHaventChangedButAreDifferent


create trigger trigName on tbl_QuestEvents
for update
as
update t1
set    QEventID = i.EventID
from   tbl_QReplyCodeDate t1 
       join deleted d on t1.QEventID = d.EventID 
       join inserted i on d.SomeOtherColumnsThatHaventChangedButAreDifferent = i.SomeOtherColumnsThatHaventChangedButAreDifferent

Go with the flow & have fun! Else fight the flow

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-17 : 17:26:41

One-to-one relationships? Databases are like Mormons: monogomy is not encouraged.
Can you get away with dumping the split structure like this:

CREATE TABLE [dbo].[tbl_QuestEventData]
	([EventID] [int] NOT NULL ,
	[QReplyDate] [varchar] (30) NULL,
	[INTID] [int] NULL,
	-- ... More fields
	)

create view [dbo].[tbl_QReplyCodeDate] 
as
select	[QEventID],
	[QReplyDate]
from	[tbl_QuestEventData]
where	[QReplyDate] is not null

create view [dbo].[tbl_QuestEvents]
as
select	[EventID],
	[INTID],
	-- ... More fields
from	[tbl_QuestEventData]

...of course, this is why I scowl every time I see a database object name prepended with the object type...bad form...

Kristen
Test

22859 Posts

Posted - 2005-10-17 : 20:20:02

"this is why I scowl every time I see a database object name prepended with the object type"

Please Doctor ... should I name all my tables with the prefix "V_" in case I ever need to replace them with views?

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-18 : 00:38:57

Absolutely not. Just name them something that describes the data they contain, not the object that they are.
Classic case: Data warehouse with table names prepended with 'tbl_' and view prepended with 'vw_'. Everything fine until six months later when refined business rules require one of the tables to be split for normalization, and one of the views is materialized into a table during nightly maintenance for faster daytime processing. Now to keep from breaking existing code references you end up with a view prepended by 'tbl_', and an aggregated data table prepended with 'vw_'. Or you find and replace all your code references.
Adding prefixes or postfixes to object names unnecessarily intermingle the logical and physical database design.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-18 : 01:42:02

Here is a nice artcile on Naming Convensions
http://vyaskn.tripod.com/object_naming.htm

Madhivanan

Failing to plan is Planning to fail

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-10-18 : 03:07:25

The one to one structure is enforced as QuestEvents is a legacy table and the rest is extra data that should have been in it in the first place. With regards to the naming - I did say it came from Access originally - I seem to recall that it can be useful in there to indicate the type of object.

Spirit/Blindman - many thanks for the code I'll take a look at it

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

Kristen
Test

22859 Posts

Posted - 2005-10-18 : 03:15:59

"and the rest is extra data that should have been in it in the first place"

Just the two tables, eh?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources