Creating Triggers in PhpMyAdmin (Beginner Questions)

[davidannis]

I have a table leitner_vcard_boxes. Each row represents a flashcard. I want to be able to efficiently sort the cards by the frequency with which the user got them correct.  Each row has a column right_count and wrong_count and a percent_correct which I calculate in php as right_count/(right_count+wrong_count) I want to replace the manual calculation with a trigger since I believe that would be more efficient than reading the row, incrementing the right or wrong counter, calculating the new percentage correct and updating the row. The trigger I plan to use is:

CREATE TRIGGER upd_right_percent AFTER UPDATE ON leitner_vcard_boxes
FOR EACH ROW
BEGIN

    UPDATE leitner_vcard_boxes SET right_percent=right_count/(right_count+wrong_count);

END;

My questions are:

1. Is the trigger going to give me the desired outcome?

2. If I export the leitner_vcard_boxes table from my development server's phpMyAdmin and replace (import) the table on the test server will the trigger move with it? Do I need to move the trigger separately?

3. How do I list the triggers in the database? Can I see them in phpMyAdmin someplace and I am just missing it?

4. What else should somebody who has not used triggers before consider?

 

[DavidAM]

Assuming we are talking about MYSQL:

 

1. No - Unless something has changed recently, you can NOT update a table in the table's trigger. You could use a BEFORE UPDATE trigger to

SET NEW.right_percent = NEW.right_count/(NEW.right_count + NEW.wrong_count);

However, see Caveat below

 

2. Triggers are not exported with the data unless you explicitly ask to have them exported. I don't use phpMyAdmin much, I generally use the command line. You have to explicitly tell mysqldump to include the triggers.

 

3. I don't think phpMyAdmin provides a way to see them.. From the mysql command line, the command is

SHOW TRIGGERS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

You could run that command in a (phpMyAdmin) query window.

 

 

Caveat

You really should NOT store a calculated field in the database. Calculate the percentage when you SELECT the data.

 

SELECT ... right_count, wrong_count, right_count + wrong_count AS Total_Count, 
right_count / (right_count + wrong_count) AS Right_Percent
...

Also, be sure your updates are autonomous. That is, don't do this:

# BAD CODE
# Select from table and store in local variables
$right = $row['right_count'];
$wrong = $row['wrong_count'];

# Update local variables
if ($user_is_right) $right += 1;
else  $wrong += 1;

# Update the database
UPDATE table SET right_count = $right, wrong_count = $wrong ...

This would overwrite any changes by another user between the SELECT and UPDATE by this user.

Instead:

 

$add_right = 0;
$add_wrong = 0;

if ($user_is_right) $add_right = 1;
else $add_wrong = 1;

UPDATE table SET right_count = right_count + $add_right, wrong_count = wrong_count + $add_wrong ...

This would increase whatever value is NOW in the database, regardless of how many users updated since this user started.

[davidannis]

Thank you. That answer is extremely helpful.