Create trigger using Yii on migration

Yii 1.1 General Discussion
1

Hi,

Imtrying create a trigger on my migration file, but i only get error.

If i execute the same SQL code in the mysql client it works.

MIGRATION:




<?php


class m110505_183123_create_insert_hidden_auction_bid_trigger extends CDbMigration

{

	

	public function up()

    {

		$this->execute('

		DELIMITER |

		

		CREATE TRIGGER insert_hidden_auction_bid

		AFTER UPDATE ON hidden_auction

		FOR EACH ROW

		BEGIN

			INSERT INTO 

				hidden_auction_bid

			SET

				user_id       = NEW.last_user_id, 

				current_price = NEW.current_price,

				created_at    = NOW();

		END;

		/

		

		DELIMITER ;

		');

    }


    public function down()

    {

		$this->execute('

			DROP TRIGGER IF EXISTS insert_hidden_auction_bid

		');

    }

	

}

ERROR:

What is wrong?

2

I’ve solved the same problem in this way:


        $createTriggerSql = <<< SQL

CREATE

    TRIGGER `products_logic` BEFORE UPDATE ON `catalog_items`

    FOR EACH ROW BEGIN


    IF NEW.item_price <= 0 THEN

        SET NEW.in_sight = 0;

    END IF;

    IF NEW.in_sight > 0 THEN

        SET NEW.available_in_48_hours = 0;

    END IF;

END;

SQL;


		$this->execute('DROP TRIGGER /*!50032 IF EXISTS */ `maintain_products_logic_on_insert`');

		$this->execute('DROP TRIGGER /*!50032 IF EXISTS */ `maintain_products_logic_on_update`');

		$this->execute('DROP TRIGGER /*!50032 IF EXISTS */ `products_logic`');

		$this->execute($createTriggerSql);

i.e. no DELIMITER expression is needed. I just splitted several commands onto several $this->execute(); calls. In your case just getting rid of the DELIMITER expression should work just fine.

3

Ingenious Idea! It totally saved me. Thanks.

4

Nice. I didn’t know one could just append the normal delimiter after each “end”. This got it working for me too. Thank you!