Post reply

sqlserver 2005

  • May 15, 2008 at 1:06 am

    #121645

    In my database I would like to create 10 tables(suppose) ,

    in that 1. sutdent details

    2. student marks

    3.student attendence

    4. student feesDetails

    Now I would like to update or delete a record from base table , the effects should be taken in child tables.

    for that how could I write a single query which is effecting on more tables .

    please help me

    Thanks in advance

  • May 15, 2008 at 1:55 am

    #815679

    Hi

    If your tables have a primary and foregin key for the student for each table then you could create a SP or query that would be something like:

    --//////////////////////////////////////////

    declare @StudentID as int

    select @StudentID = 23

    delete from tblStudent where intStudentID = @StudentID

    delete from tblStudentSubjects where intStudentID = @StudentID

    delete from tblStudentDetails where intStudentID = @StudentID

    delete from tblStudentTasks where intStudentID = @StudentID

    The other way is to setup your referencial integrity between the tables and let the delete do a cascading delete

    Thanks

    Kevin

  • May 15, 2008 at 2:48 am

    #815705

    Hi

    just try for Triggers.

  • May 15, 2008 at 6:15 am

    #815800

    Am with Jaypee.Better go for trigger 😀

  • May 15, 2008 at 6:32 am

    #815815

    I would have to disagree with trigeers,

    set up PK->FK references in your database, and create one procedure to do the data manipulation as Kevin suggested.

  • May 16, 2008 at 5:53 am

    #816386

    Triggers are great for maintaining data integrity when it can't be maintained through the normal means. Here, however, you have a very easy way to use standard data constraints. The FK constraint with cascade delete does very nicely.

    I agree with Kevin and steveb on this one.

  • May 16, 2008 at 7:49 am

    #816487

    If we talk about efficiency, we talk about using best practices. Abandoning the use of practice for an alternative method is tantamount to adding extra load.

    With this said, I believe the use of delete cascade on tables with PK - FK relationship is best for situations like this - in my view, it is far easier to get the problem solved this way than the other.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply