Generic FK vs. Triggers???

[Andrew]

I have created well-normalized database in sql server and now have a question
for public discussion.

Which is the best in development community to check for PK-FK constraints
Either I can remove generic dri by unchecking one option and create custom
triggers for my table relationships or leave generic dri alone and leave
one of the vital options [such as give one user to delete the whole client
list matching their requested string] behind.


Thnx in Advance



Andy

[Daniel Reber]

I always use dri where possible. I am not sure what you mean by "and leave
one of the vital options [such as give one user to delete the whole client
list matching their requested string] behind."

What are you trying to accomplish?

Daniel Reber, MCP

Andrew <andrei@pars.co.uk> wrote in message news:3ad31e06$1@news.devx.com...

I have created well-normalized database in sql server and now have a
question
for public discussion.

Which is the best in development community to check for PK-FK constraints
Either I can remove generic dri by unchecking one option and create custom
triggers for my table relationships or leave generic dri alone and leave
one of the vital options [such as give one user to delete the whole client
list matching their requested string] behind.


Thnx in Advance



Andy

[Andrew]

Hi Daniel

Ignore dat line ;]
Well in my case, I do have several dependent tables which for instance have
relation with accordance to... for instance order - client relationship!

User has no right to delete the client id and associated fields if he/she
placed an order and that clientid is a FK in the order table! This is called
Cascading Delete/Update/or Insert if im not mistaken! ;]

In this case I do need to disable DRI write Custom Trigger for this kind
of option.



With regards,



Andy



"Daniel Reber" <danielreber@earthlink.net> wrote:
>I always use dri where possible. I am not sure what you mean by "and leave
>one of the vital options [such as give one user to delete the whole client
>list matching their requested string] behind."
>
>What are you trying to accomplish?
>
>Daniel Reber, MCP
>
>Andrew <andrei@pars.co.uk> wrote in message news:3ad31e06$1@news.devx.com...
>
>I have created well-normalized database in sql server and now have a
>question
>for public discussion.
>
>Which is the best in development community to check for PK-FK constraints
>Either I can remove generic dri by unchecking one option and create custom
>triggers for my table relationships or leave generic dri alone and leave
>one of the vital options [such as give one user to delete the whole client
>list matching their requested string] behind.
>
>
>Thnx in Advance
>
>
>
>Andy
>
>

[Daniel Reber]

In your scenario, the client is the one side and the orders are the many.
Is this correct?
If it is, you should not be able to delete the client. You might want to
have an active flag, but I wouldn't delete the client. If you do, reports
on history will be off

HTH

Daniel Reber, MCP


Andrew <andrei@pars.co.uk> wrote in message news:3ad32938$1@news.devx.com...

Hi Daniel

Ignore dat line ;]
Well in my case, I do have several dependent tables which for instance have
relation with accordance to... for instance order - client relationship!

User has no right to delete the client id and associated fields if he/she
placed an order and that clientid is a FK in the order table! This is called
Cascading Delete/Update/or Insert if im not mistaken! ;]

In this case I do need to disable DRI write Custom Trigger for this kind
of option.



With regards,



Andy



"Daniel Reber" <danielreber@earthlink.net> wrote:
>I always use dri where possible. I am not sure what you mean by "and
leave
>one of the vital options [such as give one user to delete the whole client
>list matching their requested string] behind."
>
>What are you trying to accomplish?
>
>Daniel Reber, MCP
>
>Andrew <andrei@pars.co.uk> wrote in message
news:3ad31e06$1@news.devx.com...
>
>I have created well-normalized database in sql server and now have a
>question
>for public discussion.
>
>Which is the best in development community to check for PK-FK constraints
>Either I can remove generic dri by unchecking one option and create custom
>triggers for my table relationships or leave generic dri alone and leave
>one of the vital options [such as give one user to delete the whole client
>list matching their requested string] behind.
>
>
>Thnx in Advance
>
>
>
>Andy
>
>

[Craig Hunt]

Hi Andrew.

If I understand correctly, you want to know how to delete a record from the
one side of a one to many relationship when you have a foreign key constraint
enforcing the relationship.

In SQL server, you cannot use triggers to perform cascading deletes on tables
with foreign keys because SQL Server applies foreign key constraints before
it executes triggers. Since deleting the record on the one side of the relationship
would leave orphaned records, the constraint wins out, and the delete trigger
never fires.

In SQL server, perform cascading deletes on tables with foreign key constraints
with a stored procedure. Delete all child records first. Then, delete the
parent record.

HTH,

Craig Hunt, MCSE, MCDBA
AISA Logic, Inc.
http://aisalogic.com

[Vimal]

Hi andy,

You can accomplish what you want using procedures. This way you don't have
to disable the dri.
If you decide to use triggers then you have to disable the dri, in which
case you have to code RI in your trigger. Also, this approach can lead to
locking issues.

vimal t
MCP

"Daniel Reber" <danielreber@earthlink.net> wrote:
>In your scenario, the client is the one side and the orders are the many.
>Is this correct?
>If it is, you should not be able to delete the client. You might want to
>have an active flag, but I wouldn't delete the client. If you do, reports
>on history will be off
>
>HTH
>
>Daniel Reber, MCP
>
>
>Andrew <andrei@pars.co.uk> wrote in message news:3ad32938$1@news.devx.com...
>
>Hi Daniel
>
>Ignore dat line ;]
>Well in my case, I do have several dependent tables which for instance have
>relation with accordance to... for instance order - client relationship!
>
>User has no right to delete the client id and associated fields if he/she
>placed an order and that clientid is a FK in the order table! This is called
>Cascading Delete/Update/or Insert if im not mistaken! ;]
>
>In this case I do need to disable DRI write Custom Trigger for this kind
>of option.
>
>
>
>With regards,
>
>
>
>Andy
>
>
>
>"Daniel Reber" <danielreber@earthlink.net> wrote:
>>I always use dri where possible. I am not sure what you mean by "and
>leave
>>one of the vital options [such as give one user to delete the whole client
>>list matching their requested string] behind."
>>
>>What are you trying to accomplish?
>>
>>Daniel Reber, MCP
>>
>>Andrew <andrei@pars.co.uk> wrote in message
>news:3ad31e06$1@news.devx.com...
>>
>>I have created well-normalized database in sql server and now have a
>>question
>>for public discussion.
>>
>>Which is the best in development community to check for PK-FK constraints
>>Either I can remove generic dri by unchecking one option and create custom
>>triggers for my table relationships or leave generic dri alone and leave
>>one of the vital options [such as give one user to delete the whole client
>>list matching their requested string] behind.
>>
>>
>>Thnx in Advance
>>
>>
>>
>>Andy
>>
>>
>
>
>