How to code Cascade update and delete on SQL 7.0

  • How can you implement Cascade update and delete in SQL 7.0? We are currently trying to use triggers to make this possible. Any suggestions?

  • Its pretty straight forward coding. You need two triggers, one for updates and one for deletes. Delete would look something like this:

    Create trigger d_tablename on tablename for delete as

    --cascade delete

    Delete from tableb where foreignkey in (select primarykey from deleted)

    Updates maybe something like this:

    Create trigger u_tablename on tablename for update as

    --cascade update

    update b set b.somefield = a.somefield, b.someotherfield=a.someotherfield from inserted a inner join tableb b on a.primarykey=b.foreignkey

    It's not magic, just have to code each one and test, gets to be a lot of fun following the relationships at times.


  • Currently, I am working on a project where I need to cascades deletes in SQL 7.0. I have a table called disc and another called disc_notes. I am trying to create a trigger that cascades the delete of an entry in disc so that it also deletes all the entries in disc_notes that refer to the entry in disc. There is a foreign key relationship set up between these to tables based on fields called disc_cnum and disc_num. From your example, I tried this:

    CREATE TRIGGER disc_delete

    ON disc


    AS DELETE FROM disc_notes WHERE disc_cnum, disc_num IN

    (SELECT disc_cnum, disc_num

    FROM disc);

    And I got this error:

    Error 107: Line 4: Incorrect Syntax near ‘,’.

    I was wondering if someone could take a look at this and offer me some advice. Thanks.

    "There is no normal life, Wyatt. There is just life"

  • This:

    WHERE disc_cnum, disc_num IN

    IS not valid. You have to do

    where disc_cnum in ( select...)

    OR disc_num in (select ...)

    The OR could be AND depending on your logic.

    Steve Jones

Viewing 4 posts - 1 through 3 (of 3 total)

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