Please Help me find the correct newsgroup 
Author Message
 Please Help me find the correct newsgroup

Hi guys,

I posted the query below 5 days ago and got no replies. It doesn't seem
rocket science and must have been seen before, so I guess I'm in the wrong
newsgroup.

Does anyone know which SQL Server newsgroup covers schema design / trigger
problems ?

TIA

Mark Sugden
BeST UK / Telegentia

.
.
.
---------------------------------------------------------------------
Hi Everyone,

I have inherited maintenance of a SQL Server 2000 database and schema.

I am trying to write some admin procedures to clean up unwanted data and I
have a problem with the above.

We have a Hardware table with 2 self-referencing links. A Definition
fragment follows:

Hardware table:
    Id                       bigint    (Primary Key)            Not Null
    ComponentOf    bigint    (Foreign Key to Id)    Null
    PrevId               bigint    (Foreign Key to Id)    Null

Basically the ComponentOf column can be null or contain the Id of a Hardware
record that it is a component (child) of. The PrevId column can be null or
contain the id of the previous version of the record.

This last is a little weird: when we change a record an Instead Of trigger
intercepts the modification and copies the entire record to a new record
with a new Id (generated by an Identity property). The trigger then sets the
PrevId of the current record to be the Id of the just-inserted record, i.e.
the initial hardware record remains the current record and history is always
added as subsequent inserts (seem the wrong way round, but I inherited it
and it works so I'm leaving it alone).

The problem is: how can I delete a hardware record if it is already
referenced by component and history records ? For referential integrity I
would like to delete the components and history at the same time.

I have tried instead of triggers with all sorts of logic, but I just can't
get it to work.

Anyone figured this out before ???

TIA

Mark        ;-)))

N.B. Anti-Spam warning - to email me remove the underscore and suffix from
my email NAME (this will make sense when you see the address)



Sat, 14 Feb 2004 18:36:05 GMT
 Please Help me find the correct newsgroup

Post your programming issues to the .programming newsgroup.

Haven't gone through your question in detail, but here's a quick question. I
guess you want to delete the child records when the parent record is
deleted. Why is that being a problem? What do you mean by "I just can't get
it to work".

Are you setting an explicit Foreign Key relationship between these rows?
Have you investigated the new CASCADEing functionality of SQL2K?
--
HTH,
Vyas
Visit my site for Replication FAQ, Code samples, Database Interview
questions,
Database naming conventions, Database programming guidelines,
Interesting articles, Links and much more at:
http://vyaskn.tripod.com


Quote:
> Hi guys,

> I posted the query below 5 days ago and got no replies. It doesn't seem
> rocket science and must have been seen before, so I guess I'm in the wrong
> newsgroup.

> Does anyone know which SQL Server newsgroup covers schema design / trigger
> problems ?

> TIA

> Mark Sugden
> BeST UK / Telegentia

> .
> .
> .
> ---------------------------------------------------------------------
> Hi Everyone,

> I have inherited maintenance of a SQL Server 2000 database and schema.

> I am trying to write some admin procedures to clean up unwanted data and I
> have a problem with the above.

> We have a Hardware table with 2 self-referencing links. A Definition
> fragment follows:

> Hardware table:
>     Id                       bigint    (Primary Key)            Not Null
>     ComponentOf    bigint    (Foreign Key to Id)    Null
>     PrevId               bigint    (Foreign Key to Id)    Null

> Basically the ComponentOf column can be null or contain the Id of a
Hardware
> record that it is a component (child) of. The PrevId column can be null or
> contain the id of the previous version of the record.

> This last is a little weird: when we change a record an Instead Of trigger
> intercepts the modification and copies the entire record to a new record
> with a new Id (generated by an Identity property). The trigger then sets
the
> PrevId of the current record to be the Id of the just-inserted record,
i.e.
> the initial hardware record remains the current record and history is
always
> added as subsequent inserts (seem the wrong way round, but I inherited it
> and it works so I'm leaving it alone).

> The problem is: how can I delete a hardware record if it is already
> referenced by component and history records ? For referential integrity I
> would like to delete the components and history at the same time.

> I have tried instead of triggers with all sorts of logic, but I just can't
> get it to work.

> Anyone figured this out before ???

> TIA

> Mark        ;-)))

> N.B. Anti-Spam warning - to email me remove the underscore and suffix from
> my email NAME (this will make sense when you see the address)



Sat, 14 Feb 2004 18:57:16 GMT
 Please Help me find the correct newsgroup
Thanks Vyas,

I was out of the office for a couple of days, so didn't get to your reply until today.

I have created cascading foreign keys using the properties page for my table in EM. No joy - I get an error stating that the foreign key constraint will be violated if the 'parent' is deleted.

I then tried writing an instead of trigger (because the cascade did not work) and explicitly deleted the children before deleting the parent. This also didn't work.

OK, so maybe the delete is calling the trigger again and causing problems, so I tried just updating the children to set the foreign key field to null before deleting the parent. Also no joy.

:-(((

Why has SQL Server STILL not caught on to the benefits of a BEFORE trigger ?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Mon, 16 Feb 2004 23:54:49 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Help finding correct ODBC Version to Download

2. Please help me! (Correct Message)

3. Please help correct my stored procedure.

4. Please Help: Need SQL Server ODBC client v3.60.x to correct a date problem

5. I found this Spammer in the Newsgroup, WAS: I found this Link on the Web

6. Please Help: Need SQL Server ODBC client v3.60.x to correct a date problem

7. Please help me! (Correct Message)

8. I found this Spammer in the Newsgroup, WAS: I found this Link on the Web

9. Please, Oh Please Help Find first date plus

10. I found this Spammer in the Newsgroup, WAS: I found this Link on the Web

11. Help Please correct e mail address

12. Place your question in the correct newsgroup :-(


 
Powered by phpBB® Forum Software