CODEWITHSUNDEEP
mysql
Harpreet Singh
Harpreet Singh

Reputation: 83

sql syntax error creating trigger

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

Upvotes: 6

Views: 659

Answers (2)

Jaskaran singh Rajal
Jaskaran singh Rajal

Reputation: 2330

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 ;

Upvotes: 12

Mureinik
Mureinik

Reputation: 311393

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...

Upvotes: 0

Related Questions