Generic FK vs. Triggers???


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Generic FK vs. Triggers???

  1. #1
    Andrew Guest

    Generic FK vs. Triggers???


    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

  2. #2
    Daniel Reber Guest

    Re: Generic FK vs. Triggers???

    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



  3. #3
    Andrew Guest

    Re: Generic FK vs. Triggers???


    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
    >
    >



  4. #4
    Daniel Reber Guest

    Re: Generic FK vs. Triggers???

    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
    >
    >





  5. #5
    Craig Hunt Guest

    Re: Generic FK vs. Triggers???


    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

  6. #6
    Vimal Guest

    Re: Generic FK vs. Triggers???


    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
    >>
    >>

    >
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center