Custom mysql triggers deleted automatically in Magento 2.4.2

Question

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 ?

Answer 1

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>