Mysql trigger on Delete to multiple tables (also error 1235)

Question

I have 3 tables that are called:

character_
learned_skills
skills

"character_" contains certain informations on a list of characters, while the table "skills" contrains a list of skills. the 3rd table is the list of the skills each character has learned.

However I want to able ones a character has been deleted from the character_ table, it also deletes the name of that character and the skills it has learned from the table "learned_skills".

I assume that a trigger is required in this situation. I know that the syntax is:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR REACH ROW tigger_statement;

However I can't figure out how the trigger_statement should look like.

CREATE TRIGGER delete_char_ AFTER DELETE ON character_ FOR REACH ROW trigger_statement

Is it enough with a sigle statement or does it require several statements and or cascade?

What would you do in this situation?

CREATE TABLE character_ ( 
  Name_             varchar (30) NOT NULL,
  Class             varchar (30),
  World_Type        varchar (15),
  Str               integer     ,
  WS                integer     ,               
  BS                integer     ,
  Fel               integer     ,
  Per               integer     ,
  Int_              integer     ,
  Agi               integer     ,
  WP                integer     ,
  Tough             integer     ,  
  PRIMARY KEY (Name_)           ,
  FOREIGN KEY (Class) REFERENCES Class(Class_name),
  FOREIGN KEY (World_Type) REFERENCES World_Type(Name_)     );

  CREATE TABLE Skills (
  SkillName         varchar (30) NOT NULL,
  Type_             varchar (30),
  Characteristic    varchar (30),
  Descriptor        varchar (30),
  PRIMARY KEY (SkillName)   );

  CREATE TABLE Learned_Skills ( 
  Character_Name    varchar (30) NOT NULL,
  Skill_Name        varchar (40) NOT NULL,
  PRIMARY KEY (Character_Name,Skill_Name),
  FOREIGN KEY (Character_Name) REFERENCES character_(Name_),
  FOREIGN KEY (Skill_Name) REFERENCES Talents(TalentName)   );

UPDATE:

So I have had help to understand and make a DELETE trigger, but I have found out that need to create a multiple trigger on two tables, I know that its not possible to create a trigger on two with the same triggertime and event, but is there a way around it? What I need is the following:

DELIMITER //
CREATE TRIGGER delete_char_ AFTER DELETE ON character_
FOR EACH ROW begin
DELETE FROM learned_skills
 WHERE learned_skills.Character_Name = old.Name_;
DELETE FROM learned_talents
 WHERE learned_talents.Character_Name = old.Name_;
END;
//
DELIMITER ;

When I run this code I get error code:

1235, this version of mysql doesn't yet support 'multiple triggers with the samme action time and event for one table

Read [*Trigger Syntax and Examples*](http://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html) to know more on what trigger statement is. — Ravinder Reddy, May 22 '14 at 17:09
Ah, I just noticed your update to your question. You can't `CREATE` another trigger with the same name, you have to get rid of the old one first like so: `DROP TRIGGER delete_char_`, and then you will be able to `CREATE` the new one. — dub stylee, May 22 '14 at 22:48

dub stylee · Accepted Answer · 2014-05-22T17:44:56.930

1

Here is what it would look like:

CREATE TRIGGER delete_char_ AFTER DELETE ON character_
FOR EACH ROW
DELETE FROM learned_skills
    WHERE learned_skills.Character_Name = old.Name_;

You will need to replace <field> with whatever the primary key is of the character_. This will delete anything from the learned_skills table that has the <field> of the character_ being deleted.

edited May 22 '14 at 17:44

answered May 22 '14 at 17:06

dub stylee

3,252
5
38
59

And yes, a single statement will suffice, as the `DELETE` in the trigger statement will delete all rows that match the `WHERE` clause. – dub stylee May 22 '14 at 17:08
Thanks alot @dub stylee, but what if the two fields that are in that table, are primary keys (compound key)? – PuchuKing33 May 22 '14 at 17:08
1

What are the fields in your `learned_skills` table, probably an ID for the `character_` and an ID for the `skill`, right? You don't need to worry about the ID for the `skill`, as you are just deleting the rows that match the ID of the `character` being deleted. If you want more specifics, go ahead and update your question with the specific fields you are wondering about. – dub stylee May 22 '14 at 17:11
the question has been updated and i have tried CREATE TRIGGER delete_char_ AFTER DELETE ON character_ FOR EACH ROW BEGIN DELETE FROM learned_skills WHERE learned_skills.Name_ = old.Name_; END; but get a syntax error in old.Name_ and END it says unexpected end – PuchuKing33 May 22 '14 at 17:20
It looks like you need your WHERE clause to be `WHERE learned_skills.Character_Name = old.Name_;` – dub stylee May 22 '14 at 17:30
You may need to change the delimiter before you create the trigger. Try `DELIMITER //` before you create the trigger and then add `//` after the `;` in the WHERE clause, then `DELIMITER ;` after the end of the trigger. I will update my answer to show this. – dub stylee May 22 '14 at 17:39
@ dub stylee hmm okay, still syntax error at Name_ and END – PuchuKing33 May 22 '14 at 17:42
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/54227/discussion-between-dub-stylee-and-santelices). – dub stylee May 22 '14 at 17:46

Mysql trigger on Delete to multiple tables (also error 1235)

1 Answers1