How to code Cascade update and delete on SQL 7.0

• jfoudy

  Valued Member

  Points: 74

  More actions

  September 24, 2001 at 12:23 pm

  #77540

  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?

• Andy Warren

  SSC Guru

  Points: 119902

  More actions

  September 24, 2001 at 4:14 pm

  #418436

  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.

  Andy

  Andy
  Connect with me on LinkedIn

• UDredpoint

  SSC Veteran

  Points: 294

  More actions

  January 7, 2002 at 9:51 am

  #421810

  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

  FOR DELETE

  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"
  ~Tombstone

• Steve Jones - SSC Editor

  SSC Guru

  Points: 737981

  More actions

  January 7, 2002 at 10:00 am

  #421813

  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

  steve@dkranch.net