Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Location
    Topeka, Ks
    Posts
    6

    Question Unanswered: Contraint Or Trigger

    I have been doing abit of research, and maybe I shall stumble across the answer yet. But until then I wish to present my trouble to you good people.

    I have two tables, TAB_A, TAB_B.
    When TAB_A.ID is deleted, then the Foreign_key on TAB_B is fired with CASCADE DELETE.
    I have (or someone wants) two CASCADE DELETEs on TAB_B when TAB_A.ID is deleted. When I put this in SQL2000 I get the error: May cause cycles or multiple path.
    Both Contraints do use different Fields, but reference the same TAB_A.ID

    ex.
    Alter table TAB_B TAB_A_ID1_FK FOREIGN KEY(ID1) REFERENCES TAB_A(ID) ON DELETE CASCADE

    Alter table TAB_B TAB_A_ID2_FK FOREIGN KEY(ID2) REFERENCES TAB_A(ID) ON DELETE CASCADE
    I can add either constraint alone and it is fine, when I add the remainder, I get the error. So, I was thinking maybe a Trigger would work? But then how do I know what ID was deleted from TAB_A?

    Thanks for any help. I hope I was descriptive enough.

    Dave

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    when your trigger is fired you will have two temp tables, inserted and deleted. The deleted table will hold all the deleted records.
    Last edited by Paul Young; 06-14-02 at 17:23.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    May 2002
    Location
    Topeka, Ks
    Posts
    6
    Thanks,
    I have discoved the inserted and deleted tables that are created in memory when the triggers are fired.

    Now I have new question I am sorting through. Based off of my earlier example:

    Create Trigger TAB_B_TR
    ON TAB_A
    FOR DELETE
    AS


    if @@ROWCOUNT=0
    return

    DELETE FROM TAB_B WHERE TAB_B.ID1 = DELETED.ID

    When I execute this, it says DELETED is not a recognized column name. Now, if I just type SELECT * FROM DELETED that will execute and run. I have a few theories that I am working on as to why this isn't working...barring syntax.

    Hopefully this will be an easy post for someone and maybe save me ( the newbie) some time.

    Thanks
    David

  4. #4
    Join Date
    May 2002
    Location
    Topeka, Ks
    Posts
    6

    Smile

    I have figured out what I needed. Simple really. I believe this will do what I want:

    Create TRIGGER TABLEA_TABLEB_A_ID_TR
    ON TABLE_A
    FOR DELETE
    AS

    DELETE FROM TABLE_B WHERE TABLE_A_ID = (SELECT ID FROM DELETED)

    (saying that TABLE_B has two columns 1) ID and 2) TABLE_A_ID AND TABLE_A has one Column ID )

    I don't recall the earilier example I used,
    so I am guessing abit on column names

    Is there another way I should be doing this?
    Else, I think this will work.

    Thanks,
    David Barnes
    (trying to move outta the newbie zone)

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    That will work just fine. One of the mistakes people often make with triggers is that they forget that a trigger must work for a single row as well as multiple rows.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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