2

I placed trigger statement in installer script but it didn't create in MySQL.

Created File here Custom/Module/Setup/UpgradeSchema.php

<?php

namespace Custom\Module\Setup;

use Magento\Framework\Setup\UpgradeSchemaInterface;
use Magento\Framework\Setup\ModuleContextInterface;
use Magento\Framework\Setup\SchemaSetupInterface;

class UpgradeSchema implements UpgradeSchemaInterface
{
    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        //Your code for upgrade data base
         $installer = $setup;
        $installer->startSetup();


        $installer->run("CREATE TRIGGER `insert_user_trigger` BEFORE INSERT ON `sitealluser` FOR EACH ROW SET NEW.designs_created_at = NOW();");

        $installer->endSetup();

    }
}

How can I achieve that?

2
  • share your code, what you have tried so far Jan 2, 2017 at 12:23
  • Updated code @MurtuzaZabuawala Jan 2, 2017 at 12:35

2 Answers 2

5

We can use \Magento\Framework\DB\Ddl\TriggerFactory to create mysql triggers through setup script: (not test yet)

class UpgradeSchema implements UpgradeSchemaInterface
{
    /**
     * @var \Magento\Framework\DB\Ddl\TriggerFactory
     */
    protected $triggerFactory;

    public function __construct(
        \Magento\Framework\DB\Ddl\TriggerFactory $triggerFactory
    )
    {
        $this->triggerFactory = $triggerFactory;
    }


    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        //Your code for upgrade data base
        $installer = $setup;
        $installer->startSetup();

        $triggerName = 'insert_user_trigger';
        $event = 'UPDATE';

        /** @var \Magento\Framework\DB\Ddl\Trigger $trigger */
        $trigger = $this->triggerFactory->create()
            ->setName($triggerName)
            ->setTime(\Magento\Framework\DB\Ddl\Trigger::TIME_AFTER)
            ->setEvent($event)
            ->setTable($setup->getTable('sitealluser'));

        $trigger->addStatement($this->buildStatement($event));

        $setup->getConnection()->dropTrigger($trigger->getName());
        $setup->getConnection()->createTrigger($trigger);

        $installer->endSetup();

    }


    protected function buildStatement($event, $changelog = null)
    {
        switch ($event) {
            case \Magento\Framework\DB\Ddl\Trigger::EVENT_INSERT:
            case \Magento\Framework\DB\Ddl\Trigger::EVENT_UPDATE:
                $triggerSql = "INSERT ON `sitealluser` FOR EACH ROW SET NEW.designs_created_at = NOW()";
                return $triggerSql;
            default:
                return '';
        }
    }

}

There is a good sample: vendor/magento/framework/Mview/View/Subscription.php.

1
  • 1
    You can use $trigger->addStatement() to add new statement. Jan 2, 2017 at 14:12
2

Tested and working code,

    class UpgradeSchema implements UpgradeSchemaInterface
    {
      protected $triggerFactory;

        public function __construct(
            \Magento\Framework\DB\Ddl\TriggerFactory $triggerFactory
        )
        {
            $this->triggerFactory = $triggerFactory;
        }

        public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
        {
            $installer = $setup;

            $installer->startSetup();
            $trigger = $this->triggerFactory->create()
                ->setName('after_quote_item_option_insert')
                ->setTime(\Magento\Framework\DB\Ddl\Trigger::TIME_BEFORE)
                ->setEvent('INSERT')
                ->setTable($setup->getTable('quote_item_option'));

            $trigger->addStatement("IF NEW.code =  'info_buyRequest' THEN SET NEW.code = CONCAT(  'info_buyRequest_', NOW( ) ) ; END IF ;");

              $setup->getConnection()->dropTrigger($trigger->getName());
              $setup->getConnection()->createTrigger($trigger);

          $installer->endSetup();
        } 
    }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.