Jump to content

Creating Triggers in PhpMyAdmin (Beginner Questions)


davidannis
Go to solution Solved by DavidAM,

Recommended Posts

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?

 

Link to comment
Share on other sites

  • 2 weeks later...
  • Solution

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.