MYSQL Error when creating a trigger

Question

I have the following MySQL trigger query :

CREATE
TRIGGER `after_insert_stock` AFTER INSERT 
ON `stock` 
FOR EACH ROW BEGIN

    IF NEW.deleted THEN
        SET @changetype = 'DELETE';
    ELSE
        SET @changetype = 'NEW';
    END IF;
            INSERT INTO stock_audit (stock_id, commodity_name,commodity_id,delivery_no,supplier_name,batch_no,expiry_date,units_per_pack,no_of_packs,total_quantity,buying_price,selling_price,remarks,available_quantity,user_id,changetype) VALUES (NEW.stock_id,NEW.commodity_name,NEW.commodity_id,NEW.delivery_no,NEW.supplier_name,NEW.batch_no,NEW.expiry_date,NEW.units_per_pack,NEW.total_quantity,NEW.buying_price,NEW.selling_price,NEW.remarks,NEW.available_quantity,NEW.user_id, @changetype);

END$$

When I run the Query I get the following MySQL error : Error

SQL query:

CREATE TRIGGER `after_insert_stock` AFTER INSERT ON `stock_audit`
FOR EACH
ROW BEGIN
IF NEW.deleted
THEN
SET @changetype = 'DELETE';

MySQL said: Documentation

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 7

Please can some one help solving the problem?

P5Coder please view the following : SET @changetype = 'DELETE'; — H Dindi
– H Dindi, Commented Nov 25, 2013 at 10:00
@user689017. is deleted a column in your table? conceptually is a little weird to have a changetype of DELETE in a INSERT trigger. :p — Filipe Silva
– Filipe Silva, Commented Nov 25, 2013 at 10:47

Alma Do · Accepted Answer · 2013-11-25 10:00:41Z

1

You've missed DELIMITER declaration:

DELIMITER $$
CREATE
TRIGGER `after_insert_stock` AFTER INSERT 
ON `stock` 
FOR EACH ROW BEGIN

    IF NEW.deleted THEN
        SET @changetype = 'DELETE';
    ELSE
        SET @changetype = 'NEW';
    END IF;
            INSERT INTO stock_audit (stock_id, commodity_name,commodity_id,delivery_no,supplier_name,batch_no,expiry_date,units_per_pack,no_of_packs,total_quantity,buying_price,selling_price,remarks,available_quantity,user_id,changetype) VALUES (NEW.stock_id,NEW.commodity_name,NEW.commodity_id,NEW.delivery_no,NEW.supplier_name,NEW.batch_no,NEW.expiry_date,NEW.units_per_pack,NEW.total_quantity,NEW.buying_price,NEW.selling_price,NEW.remarks,NEW.available_quantity,NEW.user_id, @changetype);

END$$

answered Nov 25, 2013 at 10:00

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

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

2 Comments

H Dindi Over a year ago

After adding the DELIMITER $$ , I get the following 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 'ON stockFOR EACH ROW BEGIN IF NEW.deleted THEN SET @changetype = 'DELETE';ELSE' at line 1

Alma Do Over a year ago

In my case, all is working fine (i.e. I'm getting error "Table 'db.stock' doesn't exist" and, thus, syntax is correct). Can you post fiddle?

n_ilievski · Accepted Answer · 2013-11-25 10:13:23Z

Try this:


  DELIMITER $$
    CREATE TRIGGER after_insert_stock AFTER INSERT ON stock FOR EACH ROW BEGIN 
    DECLARE changetype varchar;
    IF NEW.deleted THEN
        SET changetype := 'DELETE';
    ELSE
        SET changetype := 'NEW';
    END IF;
    INSERT INTO stock_audit (stock_id, commodity_name,commodity_id,delivery_no,supplier_name,batch_no,expiry_date,units_per_pack,no_of_packs,total_quantity,buying_price,selling_price,remarks,available_quantity,user_id,changetype) VALUES (NEW.stock_id,NEW.commodity_name,NEW.commodity_id,NEW.delivery_no,NEW.supplier_name,NEW.batch_no,NEW.expiry_date,NEW.units_per_pack,NEW.total_quantity,NEW.buying_price,NEW.selling_price,NEW.remarks,NEW.available_quantity,NEW.user_id, :changetype);
  END$$

Devart · Accepted Answer · 2013-11-25 11:03:43Z

0

Try this simplified query -

CREATE TRIGGER `after_insert_stock`
AFTER INSERT 
ON `stock` 
FOR EACH ROW
INSERT INTO
  stock_audit (stock_id, commodity_name,commodity_id,delivery_no,supplier_name,batch_no,expiry_date,units_per_pack,no_of_packs,total_quantity,buying_price,selling_price,remarks,available_quantity,user_id,changetype)
VALUES
  (NEW.stock_id,NEW.commodity_name,NEW.commodity_id,NEW.delivery_no,NEW.supplier_name,NEW.batch_no,NEW.expiry_date,NEW.units_per_pack,NEW.total_quantity,NEW.buying_price,NEW.selling_price,NEW.remarks,NEW.available_quantity,NEW.user_id, IF(NEW.deleted, 'DELETE', 'NEW'));

answered Nov 25, 2013 at 11:03

Devart

120k23 gold badges172 silver badges189 bronze badges

Collectives™ on Stack Overflow

MYSQL Error when creating a trigger

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related