September 24, 2001 at 12:23 pm
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?
September 24, 2001 at 4:14 pm
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
January 7, 2002 at 9:51 am
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
January 7, 2002 at 10:00 am
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