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 to know more on what trigger statement is. — Ravinder Reddy
– Ravinder Reddy, Commented May 22, 2014 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
– dub stylee, Commented May 22, 2014 at 22:48

dub stylee · Accepted Answer · 2014-05-22 17:44:56Z

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, 2014 at 17:44

answered May 22, 2014 at 17:06

dub stylee

3,3706 gold badges42 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

dub stylee Over a year ago

And yes, a single statement will suffice, as the DELETE in the trigger statement will delete all rows that match the WHERE clause.

PuchuKing33 Over a year ago

Thanks alot @dub stylee, but what if the two fields that are in that table, are primary keys (compound key)?

dub stylee Over a year ago

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.

PuchuKing33 Over a year ago

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

dub stylee Over a year ago

It looks like you need your WHERE clause to be WHERE learned_skills.Character_Name = old.Name_;

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related