Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2004
    Posts
    19

    Exclamation Unanswered: Help need on column reference constraint with trigger conflict

    I have two tables with relationship
    Test is parent table with testNum as a primary key and class is the child table witch contain testNum as a forgien key. If I try to update the testNum on test and update the testNum in class test, how I do it?

    Actually I created a update stored procedure on test table and trigger to update the testNum in class table however I’ve got an error as
    “Update statement conflict with column reference constraint ‘fk_class_testNum’ in database”

    How can I solve this problem?
    Please Help.

    **************************************************
    My DDL, stored procedure and trigger are down below

    class table DDL
    create table class(
    classNum char(4) not null primary key,
    className varchar(25) not null,
    classDay char(3) not null,
    classTime char(8) not null,
    testNum char(5) not null
    );


    alter table class
    add constraint fk_class_testnum
    foreign key (testNum)
    references test (testNum)
    ;

    **********************************************
    test table DDL
    create table test(
    testNum char(5) primary key,
    testName varchar(50) not null,
    testDate smalldatetime not null,
    testFee money not null,
    );

    ******************************************
    My update stored procedure is
    create proc sp_update_testNum_test
    @testNum char(5),
    @new_testNum char(5)
    as

    if not exists (select testNum
    from test
    where testNum =@testNum)
    begin
    print 'testNum is not a valid Number'
    return -1
    end

    begin transaction
    update test set testNum = upper(@new_testNum)
    where testNum = upper(@testNum)

    /* Error Check */
    if @@error !=0 or @@rowcount !=1
    begin
    rollback transaction
    print 'Update is failed'
    return -1
    end
    print 'testNum has been updated'
    commit transaction
    return 0
    go

    **************************************************
    My trigger is

    create trigger tri_up_testNum_class
    on test
    for update
    as
    If update (testNum)
    Begin declare @old_testNum char(5), @new_testNum char(5)
    Select @old_testNum =
    (select testNum
    from deleted)
    select @new_testNum =
    (select testNum
    from inserted)
    update class
    set testNum = @new_testNum
    where testNum = @old_testNum
    end

    ************************************************** ***
    execution is
    execute sp_update_testNum_test
    ‘GT109’,’GT209’

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what database is this?

    because, you know, this is why ON UPDATE CASCADE was invented

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Posts
    19

    Exclamation I thought that is what i used

    I thought that I used the update cascade on my trigger in my first message, didn't I?
    That is why i was kind of supprised to have the error.
    I knew, if i use the cascade, i will be able to delete or update the related child table columns.

    I referenced example of cascading update trigger to create my update trigger but i had thaterror. Could you tell me what i did wrong on my code?



    thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, i cannot help you with the code

    all i know is that if you update the primary key value of some row, then the related foreign key value(s) are automatically updated without any trigger or stored proc

    all you have to do is declare ON UPDATE CASCADE when you define the foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Before I offer any technical thoughts, I think that it is a VERY poor design choice to ever let users change PK (Primary Key) values. It is an enormous amout of work to manage this kind of change, and its long term effect is to sabotage the project... I've never seen any application that allowed this that wasn't an ongoing nightmare to maintain.

    Technically, the problem is that Referential Integrity fires before standard triggers in MS-SQL. If you are using SQL 2000, you should be able to use an "INSTEAD OF" trigger. It is still a bad idea, but it will probably work.

    -PatP

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, i have no idea what you're talking about

    ON UPDATE CASCADE is as natural an operation as ON UPDATE DELETE or ON UPDATE RESTRICT

    (and none of these requires a trigger)

    it is "an enormous amount of work to manage this kind of change" ???

    to which my response is: WTF?

    are we living on the same planet? change the PK value, and the related FKs update automatically -- how is this a lot of work?

    i suppose you ensure unique values by doing a SELECT before you INSERT?

    please explain the "enormous" part
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem is that they aren't using CASCADE (check the code they posted).

    Trying to do cascades with either stored procedures or triggers is like teaching a pig to dance on ice. It annoys the pig, and wears you down fast... Even when you get it to work for a bit, it will fail the next time you aren't watching it closely!

    -PatP

  8. #8
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    This is very poetic in a muddy sort of way.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Poetry tends to be muddy. Part of the fun is that you can't get good traction in the stuff!

    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    The problem is that they aren't using CASCADE (check the code they posted).
    well, you see, there's the whole crux, isn't it

    never mind what the code does or does not do, look at what the code was purportedly trying to do --
    Test is parent table with testNum as a primary key and class is the child table witch contain testNum as a forgien key. If I try to update the testNum on test and update the testNum in class test, how I do it?
    that sure sounds like it should be using ON UPDATE CASCADE to me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by r937
    ...that sure sounds like it should be using ON UPDATE CASCADE to me
    I don't know which DBMSs support ON UPDATE CASCADE. I do know that Oracle doesn't, which is a pity. But the OP here isn't using Oracle.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SQL Server 2000 Books Online
    ON UPDATE {CASCADE | NO ACTION}

    Specifies what action takes place to a row in the table created, if that row has a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

    If CASCADE is specified, the row is updated in the referencing table if that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.

    For example, in the Northwind database, the Orders table has a referential relationship with the Customers table: Orders.CustomerID foreign key references the Customers.CustomerID primary key.

    If an UPDATE statement is executed on a row in the Customers table, and an ON UPDATE CASCADE action is specified for Orders.CustomerID, SQL Server checks for one or more dependent rows in the Orders table. If any exist, the dependent rows in the Orders table are updated, as well as the row referenced in the Customers.

    Alternately, if NO ACTION is specified, SQL Server raises an error and rolls back the update action on the Customers row if there is at least one row in the Orders table that references it.

    i have to say something here because this board won't let you reply with just a quote, it says "The message you have entered is too short. Please lengthen your message to at least 10 characters"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2004
    Posts
    19

    Thumbs up thanks guys

    Hi, guys
    Thank you very much for all your input of my question. By the way this all start it because of my ignorance of SQL2000.
    I did not know I could able to put delete / update cascade on my foreign key alter code. After second message I try to find the way to do cascade and that was that simple. After I found how to do it, I was amazed how simple that was. I did not even need triggers for delete and update process which I initially plan to do.


    Today, I came back to my question; I’ve got all these thoughts... sorry I did not respond sooner.

    Thanks for your help guys... sincerely

    Now, I am cruising..

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    never mind what the code does or does not do, look at what the code was purportedly trying to do --that sure sounds like it should be using ON UPDATE CASCADE to me
    That always gets me into trouble (not like I need any help there!). I've learned to use the code that people post instead of what they meant while I'm debugging. That works a lot better for me, since compilers and database engines tend to take their code rather literally!

    Yes, you are correct that a CASCADE will make this easier to code (and it won't be the freight wreck I described to manage). That doesn't mean that I consider it to be a good answer, since the cost of cascades can still be rather ugly at run time.

    I know that you (Rudy) prefer natural keys, but in my opinion any use of CASCADE just begs for a surrogate key!

    -PatP

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I can't agree with either of you, though the constraint clauses are so elegant it's hard to ignore their presence.

    Having to update PK's and subsequently FK's tells me that the app tends to ignore the history. And we all know that forgetting the history will lead to repeating it, and yet, I'd add, without even knowing it, because there is NO RECORD OF IT!!!

    So, if a part number, for example, used to have value of 1, and then HAD to be changed (for whatever bizarre reason) to 2, it means...can't you all see what it will mean? Isn't it a prime example of a design initiative that undermines and probably does not even conform to business requirements?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •