1

I am trying to create a custom mysql triggers for a custom Magento 2 table by using Setup/UpgradeSchema.php file.

Problem: For the first time triggers are created correctly. But next time when I ran setup:upgrade, my custom triggers are deleted automatically. Even if I comment my setup script after creating triggers and run setup:upgrade, then also custom triggers are deleted automatically.

Following is my setup script for creating triggers. Please let me know if I am doing any mistake in my code.

class UpgradeSchema implements UpgradeSchemaInterface
{
    protected $triggerFactory;

    public function __construct(
        TriggerFactory $triggerFactory
    ) {
        $this->triggerFactory = $triggerFactory;
    }

    public function upgrade(SchemaSetupInterface $setup, ModuleContextInterface $context)
    {
        if (version_compare($context->getVersion(), '1.2.0', '<')) {
            $this->createTriggers($setup, 'INSERT', 'trg_custom_table_after_insert');
            $this->createTriggers($setup, 'UPDATE', 'trg_custom_table_after_update');
            $this->createTriggers($setup, 'DELETE', 'trg_custom_table_after_delete');
        }
    }

    public function createTriggers($setup, $event, $triggerName)
    {
        $installer = $setup;
        $installer->startSetup();

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

        $statement = '';
        switch ($event) {
            case Trigger::EVENT_INSERT:
                $statement = "SET @entity_id = (SELECT `entity_id` FROM `custom_table`
                                WHERE `entity_id` = NEW.`entity_id`);
                                INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) values(@entity_id);";
                break;

            case Trigger::EVENT_UPDATE:
                $statement = "SET @entity_id = (SELECT `entity_id` FROM `custom_table`
                                WHERE `entity_id` = NEW.`entity_id`);
                                IF
                                    (NOT(NEW.`updated_at` <=> OLD.`updated_at`))
                                    THEN
                                        INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) values(@entity_id);
                                END IF;";
                break;

            case Trigger::EVENT_DELETE:
                $statement = "SET @entity_id = (SELECT `entity_id` FROM `custom_table`
                                 WHERE `entity_id` = OLD.`entity_id`);
                                INSERT IGNORE INTO `catalogsearch_fulltext_cl` (`entity_id`) values(@entity_id);";
                break;
            default:
                break;
        }

        $trigger->addStatement($statement);
        $installer->getConnection()->dropTrigger($trigger->getName());
        $installer->getConnection()->createTrigger($trigger);

        $installer->endSetup();
    }
}

Questions:

  1. Does Magento 2 allow to create custom triggers in custom tables ?
  2. How Magento core modules create mysql triggers for its core tables ?
2
  • You need to remove version_compare logic from upgrade Aug 25, 2022 at 18:21
  • @VictorTihonchuk I tried by removing version_compare logic. Its not working. Aug 26, 2022 at 5:08

1 Answer 1

0

I found solution for this. We can use mview.xml file for create mysql triggers in custom table (custom indexer). I wanted to make an entry in catalogsearch_fulltext_cl if any changes happens in my custom table.

etc/mview.xml

<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:Mview/etc/mview.xsd">
    <view id="catalogsearch_fulltext" class="\Magento\CatalogSearch\Model\Indexer\Mview\Action" group="indexer">
        <subscriptions>
            <table name="custom_table_name" entity_column="entity_id" />
        </subscriptions>
    </view>
</config>

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.