Trigger

Michal Čihař edited this page May 16, 2016 · 3 revisions
Clone this wiki locally

Starting with version 5.0, MySQL supports the use of triggers. A trigger is code which is automatically executed in response to certain events. Here is an example of how to use a trigger on a table to insert data into a secondary table.

Objective: When a new member record is added to the members table, add a corresponding record to the member_since table with a current timestamp.

  1. In phpMyAdmin, select the database that you want to work with.
  2. Go to the SQL tab at the top of the page.
  3. In the "Run SQL query/queries on database" form, change the Delimiter to $$. (Located in a small box at the bottom of the form)
  4. Enter your SQL trigger into the main dialog box on the form. The correct syntax is as follows (per a MySQL employee and it worked for me...)

CREATE TRIGGER membership AFTER INSERT ON members FOR EACH ROW BEGIN INSERT INTO member_since (member_id,signup_date) values (NEW.member_id,now()); END;$$

Finally hit "GO" and hopefully you'll have created your new trigger.

for running this command you should have "Super privilege" otherwise you will receive following error :

#1227 - Access denied; you need the SUPER privilege for this operation