Powered by eUKhost®

Announcement

Collapse
No announcement yet.

MySQL Trigger creation from phpMyAdmin

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL Trigger creation from phpMyAdmin

    MySQL Trigger creation from phpMyAdmin

    Many times, after migrating to a new server "Trigger" in MySQL stops working or can not be created.

    Let us find the reason behind this.

    1) "Trigger" requires MySQL version 5.0. or higher to work. So verify you have MySQL 5.0 or higher version running on the server.

    2) Only root user has privilege to create a "Trigger" as super privilege is required.

    3) One can login in the phpMyAdmin from WHM as root user to create a "Trigger". Creating "Trigger" from cPanel > phpMyAdmin will result in error as

    #1227 - Access denied; you need the SUPER privilege for this operation
    4) If you find problem with "Trigger" which was working fine on old server for the same database, then its time to check the above mentioned points and also the delimiter value on old and new server in phpMyAdmin. Setting delimiter value same as on the old server will solve the problem.

    Example:

    Original query:

    create trigger triggername after insert on test.check
    for each row
    begin
    insert into rows values(5);
    end;

    This may result in error because of the delimiter value in the phpMyAdmin. You will have to change value of delimiter to some unused characters, suppose it is $$ then the above query will look like


    create trigger trigname after insert on test.check
    for each row
    begin
    insert into rows values(5);
    end;
    $$


    Hurray.. the query will be executed without any errors now.

    To check if the above query if executed successfully, fire a query

    SHOW TRIGGERS

    Hope this works for you guys too ! :smile:
    Last edited by Kieran; 24-10-2011, 04:04. Reason: update ..
ban-img
Working...
X