Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Posts
    53

    Unanswered: triggers in sybase - noob question

    I have tables:
    COUNTRY
    id_country
    country


    CITY
    id_city
    id_country
    city


    1 country can have many cities.

    I would like to create trigger that after update column 'id_country' in COUNTRY table it will be update column 'id_country' in CITY table:

    Code:
    create trigger trg1 
    after update of id_country on country
    referencing old as old_name new as new_name
    for each row
    begin
    update city set city.id_country = new_name.id_country
    where city.id_country = old_name.id_country
    end;
    When I make:
    update country set id_country where id_country = 1
    I have an error:
    HTML Code:
    "Could not execute statement. 
    Primary key for row in table country is referenced by foreign key my_fk1 in table city
    SQLCODE = -198
    ......."
    Why ? I can't use triggers when I have foreign key between tables ?

  2. #2
    Join Date
    Aug 2008
    Posts
    12
    It is a foreign key. So if you change the primary key in table a, all instances of the foreign key in table b are also updated.

    You don't need to create a trigger for that

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258

    "Updating" a Primary Key

    Quote Originally Posted by powernicholas
    It is a foreign key. So if you change the primary key in table [parent], all instances of the foreign key in table [child] are also updated. You don't need to create a trigger for that
    That is not correct. Neither SQL nor Sybase has a "cascading" update.

    Quote Originally Posted by chomik
    I would like to create trigger that after update column 'id_country' in COUNTRY table it will be update column 'id_country' in CITY table:
    Understanding the Problem

    1 You do not need a trigger. But you do need to think things out precisely.
    2 In order for you to understand your problem, forget about the trigger and think about the meaning/effect of "updating" country.id_country. It is not any old coulmn, it is a PK. Which happens to have [correct] dependencies, such as supporting an FK in city.
    3 When you "update" a PK, you are logically deleting the row and inserting a new row (with the new PK). If the row has no FK dependencies, fine, it works. If it does, it [correctly] gives you that error. It has nothing to do with the trigger. You are attempting to delete a country.PK which has city.rows, and it is [correctly] stopping you.

    So Now What

    First, there is something very suspect in "updating" a PK in a Relational database. If the PK is not a stable value, then why use it as a PK. Nowadays, people get lazy with identifying good strong natural Relational keys, and whack an Identity column in instead. But then even that has to be treated with a bit of respect, as the stable PK. The concept of stable PKs that are unstable is a contradiction.

    In your example, you can change the country name, date of manufacture/birth, etc., but it is not reasonable to change the id_country. Rhodesia becomes Zimbabwe, but it is still id 9876. So what. Zimbabwe contains all the cities that Rhodesia used to have, correctly; old Rhodesia no longer has any cities, correctly. That id does not and should not, bear any relation to any other id.

    There is no logical value (only psychological) in having PKs (Identity or otherwise) which are an unbroken sequence of numbers (or chars). These "holes" do not need to be "patched", by moving everything following it (and all their dependencies); they are not holes.

    Second, if you really need to do that (which I do not accept), the way to go about it is a stored proc:
    - insert country ... new_id_country
    - in reasonable batches (eg. maximum 500):
    --- update city set id_country = new_id_country where id_country = old_id_country
    - delete country where id_country = old_id_country
    Now, you can do that in the trigger, but a proc is infinitely better.

    BTW, when you are using Declarative Referential Integrity, which is implemented as FKs, you really do not need triggers as well, they are "either/or".
    Last edited by Derek Asirvadem; 04-08-09 at 04:26.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  4. #4
    Join Date
    Dec 2008
    Posts
    53
    Ok, thx - I had to make 'on update cascade' or something like that without trigger.

Posting Permissions

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