Create Mysql Trigger before Insert

Question

i've tried to make an MYSQL Trigger on Adminer, but it doesn't work. Here is my Code:

    CREATE TRIGGER handle100kRows BEFORE INSERT ON `4013834123456`
FOR EACH ROW
BEGIN

    IF (SELECT COUNT(*) FROM `4013834123456`) = "100000" THEN BEGIN
        DELETE FROM 4013834123456 ORDER BY id LIMIT 1
    END
    END IF

END;

Following errors appear:(1064): (1064): Syntax error near '4013834123456 ORDER BY id LIMIT 1 END END IF END' at line 6

Ive just tried it with ; on the END's but then appears more errors... I work with Adminer 4.1.0 an MySQL Database! Thx for help!

UPDATE, now it works, with delimiter:

delimiter |
CREATE TRIGGER handle100kRows BEFORE INSERT ON `4013834123456`
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM `4013834123456`) = "100000" THEN
        DELETE FROM `4013834123456` ORDER BY id LIMIT 1;
    END IF;
END;
delimiter;

juergen d · Accepted Answer · 2015-01-20T08:18:17.203

2

You forgot the escape the table name 4013834123456 with backticks.

Also you have and end too much.

delimiter |
CREATE TRIGGER handle100kRows BEFORE INSERT ON `4013834123456`
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM `4013834123456`) = "100000" THEN
        DELETE FROM `4013834123456` ORDER BY id LIMIT 1;
    END IF;
END
|
delimiter ;

edited Jan 20 '15 at 08:18

answered Jan 20 '15 at 07:45

juergen d

201,996
37
293
362

Ok i escaped it like this: DELETE FROM `4013834123456` ORDER BY id LIMIT 1 -> NOW: (1064): Syntax error near 'END END IF END' at line 7 ( ` not shown ) – Johannes Regner Jan 20 '15 at 07:46
I think semicolon is missing at the end of END & ENDIF – Qazi Jan 20 '15 at 07:51
Now: CREATE TRIGGER handle100kRows BEFORE INSERT ON `4013834123456` FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM `4013834123456`) = "100000" THEN DELETE FROM `4013834123456` ORDER BY id LIMIT 1; END IF; END; appears: (1064): Syntax error near '' at line 5 and (1064): Syntax error near 'END IF' at line 1 – Johannes Regner Jan 20 '15 at 08:01

Create Mysql Trigger before Insert

1 Answers1