[snig08]

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.

[andrewsw]

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

[andrewsw]

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

[snig08]

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.

[andrewsw]

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

[snig08]

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

[andy_pleasants]

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

[snig08]

andy_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