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

The only thing that comes to mind is that you changed 4th column from `null` to `'type'` - maybe the 4th column isn't a `varchar` column? — radoh, Mar 20 '16 at 08:46
You forgot to prepend `DELIMITER $$` to the `create trigger` command. Also don't forgot to append `DELIMITER ;` after this command. — krokodilko, Mar 20 '16 at 08:54
@radoh:Its string,here is code how i did .... ADD action_field VARCHAR(8) NOT NULL DEFAULT 'id' AFTER dt_datetime, — DOE, Mar 20 '16 at 09:19
Hm ok, I guess that isn't the problem then. I wonder though - seeing it is defined as `NOT NULL`, how could your previous trigger work, since you were inserting `NULL`...? — radoh, Mar 20 '16 at 09:21

score 0 · Accepted Answer · answered Mar 20 '16 at 09:43

@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

Oh,yes i found like ... delimiter // – DOE Mar 20 '16 at 09:48 — DOE, Mar 20 '16 at 09:48

Trigger (AFTER/BEFORE) In Mysql

1 Answers1