Trigger (AFTER/BEFORE) In Mysql

Question

I have a trigger which is working fine.

CREATE TRIGGER crm_listings__au 
AFTER UPDATE 
ON crm_listings FOR EACH ROW
    INSERT INTO crm_listings_versions 
        SELECT 
            'update', NULL, NOW(), NULL, d.*
        FROM 
            crm_listings AS d 
        WHERE 
            d.id = NEW.id;

Now I want to keep track of the field column name also. I am thinking I could not do in above query so I changed to below trigger

CREATE TRIGGER crm_listings__au 
BEFORE UPDATE 
ON crm_listings 
FOR EACH ROW
BEGIN
    IF OLD.type != NEW.type
    THEN
        INSERT INTO crm_listings_versions  
            SELECT 
                'update', NULL, NOW(), 'type', d.* 
            FROM 
                crm_listings AS d 
            WHERE 
                d.id = NEW.id;
    END IF;

    IF OLD.price != NEW.price
    THEN
        INSERT INTO crm_listings_versions 
            SELECT  
                'update', NULL, NOW(), 'price', d.* 
            FROM 
                crm_listings AS d
            WHERE 
                d.id = NEW.id;
    END IF;
END;
$$

When I run this code, I get this error:

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 10

UPDATE:

I followed this post on stackoverflow

Answer 1

@kordirko: Can you please explain a little bit?

Please study the documentation: http://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command. The following example shows how to do this for the dorepeat() procedure just shown. The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

---

Simple example - I am using MySql Workbench and have copied and pasted your trigger. First some dummy tables:

create table crm_listings(
   id int,
   type int,
   price int
);

create table crm_listings_versions(
   ttype varchar(100),
   something varchar(100),
   d date,
   something1 varchar(100),
   id int,
   type int,
   price int
);

And now I run your code without DELIMITER

CREATE TRIGGER crm_listings__au BEFORE UPDATE ON crm_listings 

FOR EACH ROW

 BEGIN
    IF OLD.type != NEW.type
    THEN


        INSERT INTO crm_listings_versions SELECT 'update', NULL, NOW(), 'type', d.* FROM crm_listings AS d WHERE d.id = NEW.id;

    END IF;
    IF OLD.price != NEW.price
    THEN


        INSERT INTO crm_listings_versions SELECT 'update', NULL, NOW(), 'price', d.* FROM crm_listings AS d WHERE d.id = NEW.id;

    END IF;
END;
$$

Error Code: 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 10  0.000 sec

Outcome = Error

---

Then the same, but with the DELIMITER command:

DELIMITER $$
CREATE TRIGGER crm_listings__au BEFORE UPDATE ON crm_listings 

FOR EACH ROW

 BEGIN
    IF OLD.type != NEW.type
    THEN


        INSERT INTO crm_listings_versions SELECT 'update', NULL, NOW(), 'type', d.* FROM crm_listings AS d WHERE d.id = NEW.id;

    END IF;
    IF OLD.price != NEW.price
    THEN


        INSERT INTO crm_listings_versions SELECT 'update', NULL, NOW(), 'price', d.* FROM crm_listings AS d WHERE d.id = NEW.id;

    END IF;
END;
$$

DELIMITER ;

0 row(s) affected

Outcome = Success