sql syntax error creating trigger

Question

I want to create trigger and I have written this query but this does not execute. Please check my query

CREATE
    TRIGGER 'blog_after_insert' AFTER INSERT 
    ON 'blog' 
    FOR EACH ROW BEGIN

        IF NEW.deleted THEN
            SET @changetype = 'DELETE';
        ELSE
            SET @changetype = 'NEW';
        END IF;

        INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);

I am getting this error

#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 ''blog_after_insert' AFTER INSERT 
    ON 'blog' 
    FOR EACH ROW BEGIN

        IF NEW.del' at line 2

Just remove the quotes.

SMA
– SMA

2015-02-01 15:28:01 +00:00
Commented Feb 1, 2015 at 15:28 — SMA
– SMA, Commented Feb 1, 2015 at 15:28
Only use single quotes for string and date constants.

Gordon Linoff
– Gordon Linoff

2015-02-01 15:33:13 +00:00
Commented Feb 1, 2015 at 15:33 — Gordon Linoff
– Gordon Linoff, Commented Feb 1, 2015 at 15:33

Jaskaran singh Rajal · Accepted Answer · 2015-02-02 15:58:16Z

12

Please run this query

DELIMITER $$
CREATE
    TRIGGER blog_after_insert  AFTER INSERT 
    ON blog 
    FOR EACH ROW BEGIN

        IF NEW.deleted THEN
            SET @changetype = "DELETE";
        ELSE
            SET @changetype = "NEW";
        END IF;

        INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);

    END$$
DELIMITER ;

answered Feb 2, 2015 at 15:58

Jaskaran singh Rajal

2,3302 gold badges19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2015-02-01 19:24:31Z

0

Single quotes (') denote string literals - object names, like triggers and tables should use forward quotes, or no quotes at all:

CREATE
    TRIGGER blog_after_insert AFTER INSERT -- here
    ON blog -- and here
    FOR EACH ROW BEGIN
    -- rest of code...

answered Feb 1, 2015 at 19:24

Mureinik

316k54 gold badges400 silver badges405 bronze badges

2 Comments

Harpreet Singh Over a year ago

i have made the changes which you have suggested me, but still getting error.. please look into my query where i am doing mistake ----- CREATE TRIGGER blog_after_insert AFTER INSERT -- here ON blog -- and here FOR EACH ROW BEGIN IF NEW.deleted THEN SET @ changetype = 'DELETE'; ELSE SET @ changetype = 'NEW'; END IF; INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @ changetype);

Harpreet Singh Over a year ago

i have put spaces in special characters @ because stack overflow consider it as a user..

Collectives™ on Stack Overflow

sql syntax error creating trigger

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related