0

I am trying to add a trigger to one of my tables. Depending on a enum-value of the newly inserted row it should execute a different insert-query. I am using phpMyAdmin and am trying to execute my script in the SQL-Tab but it always get an error which tells me absolutely nothing...

Here's my SQL Script that i try to execute in the SQL Tab of phpMyAdmin:

CREATE TRIGGER insert_after_new_kpi_trig
    AFTER INSERT ON KPI
    FOR EACH ROW

    IF NEW.kpi_type = 3 THEN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projects;
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams;
    ELSE IF NEW.kpi_type = 1 THEN
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams;
    ELSE IF NEW.kpi_type = 2 THEN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projects;
    END IF;

And this is the error I'm getting:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

So can someone tell me if something is wrong with my script or do I have to use the string-value of the enum for the comparison instead of the index that I'm currently using? Thank you for your help.

Edit: Maybe I should also mention what the enum is its the following

enum('TEAM', 'PROJECT', 'BOTH')

So if I use 3 in the if condition it should be equal to BOTH

1
  • @Akina I tried to put the BEGIN after FOR EACH ROW and END after END IF; in but it still gives me the same error. And I'm not entirely sure if this is also needed in phpMyAdmin because it worked with another trigger with multiple inserts just without an if-condition. – WasteD Nov 29 '19 at 7:21
1

I had to use delimiter to make it work but because delimiter is a mysql-console command it didn't work by putting it in my script.

phpMyAdmin has a different way to set a delimiter. Below of the script-input there is another input to set a delimiter so at then and my script looked like this:

CREATE TRIGGER insert_after_new_kpi_trig
    AFTER INSERT ON KPI
    FOR EACH ROW
    BEGIN
    IF NEW.kpi_type = 3 THEN BEGIN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projects p;
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams t;
        END;
    ELSEIF NEW.kpi_type = 1 THEN
        INSERT INTO TeamKPIData (team_id, kpi_id, value) SELECT t.id, NEW.id, 0 FROM Teams t;
    ELSEIF NEW.kpi_type = 2 THEN
        INSERT INTO ProjectKPIData (project_id, kpi_id, value) SELECT p.id, NEW.id, 0 FROM Projectsp;
    END IF;
    END $$ --Thats the delimiter I set.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.