aspose file tools*
The moose likes JDBC and the fly likes automatically delete or insert Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "automatically delete or insert" Watch "automatically delete or insert" New topic
Author

automatically delete or insert

jonathan Greens
Ranch Hand

Joined: Apr 07, 2004
Posts: 139
in sql, suppose i have two tables a and b, is it possible to make the deletion of a row in table a triggers the deletion of a corresponding row in b? same for insertion. is this related to foreign key constraint?
thanks
Masoud Kalali
Author
Ranch Hand

Joined: Jul 08, 2004
Posts: 531

a good solution for this job , in case that your database support Triggers is to use before Insert and after Delete trigers.

you may set these triggers to be fired when you insert a row into that main table to insert the same row into the secondary table .

also on deletition of a row from table one you can delete the same row from table two.

you can not use a foreign key for this case , foreign key usage is difference .


Masoud Kalali
Software Engineer - My Weblog - GlassFish Security
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by jonathan Greens:
in sql, suppose i have two tables a and b, is it possible to make the deletion of a row in table a triggers the deletion of a corresponding row in b? same for insertion. is this related to foreign key constraint?
thanks


Triggers are one way to do this; they're more flexible than foreign key constraints, an "on delete" trigger on table a can do whatever you're able to program on table b.

Foreign key constraints can also do automatic deletion, for example if table b has a FK reference to table a and the refered to row in a is deleted, normally the database will return an error and prevent the deletion from being committed. However, if the foreign key constraint was established with cascade deletion, then instead of throwing the error on a delete in a, the refering row(s) in b will be deleted.

Here's how such a foreign key constraint is set in MySQL, most other DBs that support foriegn keys do it similarly, I think that's how it's specified in the SQL standard...
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by jonathan Greens:
in sql, suppose i have two tables a and b, is it possible to make the deletion of a row in table a triggers the deletion of a corresponding row in b? same for insertion. is this related to foreign key constraint?
thanks


Oh, foreign keys can't do an insert though; for that, you have to use a trigger (if your database supports them, not all do).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: automatically delete or insert