Syntax Error on Create Trigger

Question

CREATE TRIGGER new_trx_tmp_on_transaction_update AFTER UPDATE ON transaction
FOR EACH ROW
BEGIN
 IF NEW.trx_status = 1 AND ( NEW.cat_id = 'CASHBACK' || NEW.cat_id = 'REFUND') THEN
 INSERT INTO trx_tmp( `id`, `trx_id`, `user_id`, `trx_merchant_trxid`, `trx_amount`, `amount_left`, `cat_id`, `created_at`, `updated_at` ) VALUES ( NULL, NEW.id, NEW.userid, NEW.trx_merchant_trxid, NEW.trx_amount, NEW.trx_amount, NEW.cat_id, NOW(), NOW() );
 END IF;
END

I can't find where I'm doing wrong:

[ERROR in query 1] 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 5

Answer 1

You just need to change the command delimiter that the client is looking for in order to split commands:

delimiter $$
CREATE TRIGGER new_trx_tmp_on_transaction_update AFTER UPDATE ON transaction
FOR EACH ROW
BEGIN
 IF NEW.trx_status = 1 AND ( NEW.cat_id = 'CASHBACK' || NEW.cat_id = 'REFUND') THEN
 INSERT INTO trx_tmp( `id`, `trx_id`, `user_id`, `trx_merchant_trxid`, `trx_amount`, `amount_left`, `cat_id`, `created_at`, `updated_at` ) VALUES ( NULL, NEW.id, NEW.userid, NEW.trx_merchant_trxid, NEW.trx_amount, NEW.trx_amount, NEW.cat_id, NOW(), NOW() );
 END IF;
END
$$

The default is a semi-colon, hence it complaining at the end of the INSERT line when you try and create your trigger.

It's all explained in the documentation here.

• Akina: [ERROR in query 1] means that the author creates trigger via some client which differs from standard command-line mysql.exe, and it may not support delimiter changing command...