Help with a trigger

[Darkmatter5]

I understand pretty much nothing about triggers.  They're syntax is quite confusing.  So i'm asking for someone to help me build this query and help explain to me what it's doing if possible.

 

I need the trigger to run after a record is updated.  I need it to get the current score grade based on what the current grade amount is.  So if a students record is updated from 72 to 85 and the score grades are listed in the following table

 

grademinmax

A90100

B8089

C7079

F069

 

The students grade should now be set to B from C.  I know how to get the grade based on the score, but don't know how to build this trigger.

 

Thanks!

[Brian W]

 

But if you are just looking to have it done when you update a score from your application, then you just need another query and run it after the one for updated the grade. Average the scores and UPDATE the grade, right?

[Darkmatter5]

Yes you are right, but I'm wanting to learn triggers and I'm not getting much from googled tutorials.  There's nothing I've found so far that has clear instructions.  So I created this scenario to inquire as to how it might be accomplished.

 

Thanks for the reply.

[fenway]

First you need to write the query to produce the desired logic... then wrap it in a trigger.

[Darkmatter5]

Okay here's an actual example I've come up with.  See if you can help me.

 

player_characters

character_idexperience

1800

 

levels

levelfloor_expceiling_exp

10500

25011000

310011500

 

Here's my trigger code

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `thealien_aftermath`.`update_level` AFTER UPDATE
    ON `thealien_aftermath`.`player_character`
    FOR EACH ROW BEGIN

DECLARE level INTEGER;
SELECT l.level
INTO level
FROM player_characters AS pc, levels AS l
WHERE pc.character_id=NEW.character_id
AND pc.experience BETWEEN l.floor_exp ANd l.ceiling_exp

UPDATE player_characters AS pc
SET pc.level=level
WHERE pc.character_id=NEW.character_id

    END$$

DELIMITER ;

 

I'm wanting to have the level be updated to player_character.level when the character record is updated.  I'm getting this error:

 

Error Code : 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE player_characters AS pc

SET pc.level=level

WHERE pc.character_id=NEW.char' at line 12

 

[fenway]

Missing semi-colon.