7 Replies - 2197 Views - Last Post: 27 January 2013 - 06:44 AM

#1 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Deleting a row when a row from another table is deleted.

Posted 23 January 2013 - 04:29 PM

I'm now adding some referential integrity to my website/database. I currently have a login table with username and password. the username on this table is a foreign key from my members table. in my members table I have Username and other information. I think I might need to use a trigger to accomplish what I need. Here is my trigger so far:
CREATE TRIGGER br1.deletelogin
BEFORE DELETE ON br1.members FOR EACH ROW
BEGIN
INNER JOIN members ON login.Username = members.Member_Names;
DELETE FROM login WHERE login.Username = OLD.members.MEMBER_NAMES;
END

what am I doing wrong, need some help.
Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Deleting a row when a row from another table is deleted.

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2877
  • View blog
  • Posts: 9,548
  • Joined: 12-December 12

Re: Deleting a row when a row from another table is deleted.

Posted 23 January 2013 - 06:47 PM

Probably should be

CREATE TRIGGER br1.deletelogin
BEFORE DELETE ON br1.members FOR EACH ROW
BEGIN
DELETE FROM login 
INNER JOIN members ON login.Username = members.Member_Names 
WHERE login.Username = OLD.members.MEMBER_NAMES;
END


TBH I think that you should revise your understanding of SQL statements, and the order that the various clauses need to appear in, before going further with MySql.

Also, the field name MEMBER_NAMES is confusing:

Username = MEMBER_NAMES just looks wrong. I wouldn't capitalise it either - it's distracting within a SQL statement.

This post has been edited by andrewsw: 23 January 2013 - 06:51 PM

Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2877
  • View blog
  • Posts: 9,548
  • Joined: 12-December 12

Re: Deleting a row when a row from another table is deleted.

Posted 23 January 2013 - 06:59 PM

I found this for SELECT:

Quote

You specify your select command in the following order:
1. The columns you want to choose (SELECT)
2. Where you want to send the results (INTO)
3. The tables that contain the data (FROM)
4. How you want to connect those tables (JOIN, ON)
5. Which individual rows you want to choose (WHERE)
6. Bunching of individual rows together (GROUP)
7. Which bunches you want to choose (HAVING)
8. How to sort the records (ORDER)
9. How many output records you want (LIMIT)


Perhaps we can make a phrase out of: S I F J O W G H O L
See IF J(ON) Will Get His Own Lunch?

This post has been edited by andrewsw: 23 January 2013 - 07:31 PM

Was This Post Helpful? 0
  • +
  • -

#4 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Re: Deleting a row when a row from another table is deleted.

Posted 24 January 2013 - 01:33 PM

I accidentally held down the shift when trying member_names haha Also I just tried your code but it still won't work. Today I did find a way to set up a trigger using this code
CREATE TRIGGER deletelogin AFTER DELETE ON members FOR EACH ROW BEGIN DELETE FROM login WHERE Username IN(SELECT name FROM members WHERE login.Username=members.Member_names); END

This does work but it doesn't delete the right row from the login table. I think this could be something to do with this code:
WHERE Username IN(SELECT name FROM members WHERE login.Username=members.Member_names)

My other great finding was that I know how to delete two rows of the same value from two different tables which is this code
DELETE FROM login WHERE Username IN(SELECT Member_names FROM members WHERE login.Username=members.Member_names)

I'm just struggling to put this together.
Thanks for all your replies.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 2877
  • View blog
  • Posts: 9,548
  • Joined: 12-December 12

Re: Deleting a row when a row from another table is deleted.

Posted 24 January 2013 - 03:36 PM

I no longer follow. Your recent SELECT statement identifies a field called name rather than member_name.

I would also be using numbers rather than text: UserID, MemberID.

And using FOR EACH shouldn't be required(?). (It seems that members has repeating occurrences of the Username? Your post title indicated there was only one row.)

You've also dropped the reference to the OLD.value(?).

This post has been edited by andrewsw: 24 January 2013 - 03:37 PM

Was This Post Helpful? 0
  • +
  • -

#6 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Re: Deleting a row when a row from another table is deleted.

Posted 25 January 2013 - 11:34 AM

Could somebody show how to create a trigger to delete a value where it is equal to the same value of another table after/before its deleted. Thanks
Was This Post Helpful? 0
  • +
  • -

#7 andy_pleasants  Icon User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 122
  • Joined: 08-July 10

Re: Deleting a row when a row from another table is deleted.

Posted 27 January 2013 - 06:05 AM

Hi guys

I think the best way to achieve this is by setting the
ON DELETE
option on the foreign key constraint to
CASCADE
.

This will automatically delete all the referencing rows when the parent row is deleted - no need for extra programming or triggers
Was This Post Helpful? 1
  • +
  • -

#8 snig08  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 22-January 13

Re: Deleting a row when a row from another table is deleted.

Posted 27 January 2013 - 06:44 AM

View Postandy_pleasants, on 27 January 2013 - 06:05 AM, said:

Hi guys

I think the best way to achieve this is by setting the
ON DELETE
option on the foreign key constraint to
CASCADE
.

This will automatically delete all the referencing rows when the parent row is deleted - no need for extra programming or triggers

right I did manage to fix it with a complex trigger but that one deletes the one row from another table. I will looking the on delete cascade code and use that instead if its what I think it is. Thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1