MySQL / PHPMyAdmin Trigger Syntax

November 12, 2008

Ok this was extremely annoying but figure it out (again ) this time I am documenting what I did, to help me ( cause I know I’ll forget how I did it ) and for anyone else having the same issue.

Trying to insert a trigger through the phpmyadmin web interface.

  1. Ensure your SQL on it’s own is correct
  2. Ensure your MySQL trigger syntax is correct
  3. change the delimiter ( in the text field) to something like % ( see diagram )
  4. put the new delimiter at the end of the entire statement
  5. Pray for mercy !

Here is a diagram to really simplify it.

So nice to see that success message

So nice to see that success message

Advertisements

7 Responses to “MySQL / PHPMyAdmin Trigger Syntax”

  1. skysocket Says:

    can you also show the create table of your database. I really need to understand this mysql trigger. Can I actually mysql trigger in the php code?

  2. ultimatum Says:

    Thank you for the trigger guide, I was looking for this all day.

  3. Nate Says:

    Thanks!!!

    I was missing the different delimiter type.

  4. arushi Says:

    hey,
    i want to use trigger to calculate the sum of values in two different columns as soon as the value in second column is inserted.
    for example, here’s a table ‘subject’ with fields id, mtt1,mtt2 and total.
    as soon as the marks in mtt2 are inserted( i.e. table is updated) , i want the sum of mtt1 and mtt2 to be calculated and inserted in total.
    i wrote the following code via trigger:
    CREATE TRIGGER update_sum AFTER UPDATE ON subject
    FOR EACH ROW
    BEGIN
    UPDATE subject SET total = NEW.mtt1 + NEW.mtt2 where id=NEW.id;
    END;$$

    it gives me an error that “#1442 – Can’t update table ‘subject’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger. ”

    can u please help me ..

  5. diptendu Says:

    it is not working

    code:

    CREATE trigger trig1 AFTER INSERT ON student_details
    FOR each
    ROW
    BEGIN
    INSERT INTO login
    VALUES (
    : new.student_details.student_id , : new.student_details.username , : new.student_details.password
    );

    END ;

    %

  6. Satya Says:

    Nice Trigger syntax error description. Long time back i got chance again to write trigger and use this. Writing not issue for me but when running on the server getting same error. After Google search found your articles. Nice Post thanks:)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: