8

I am trying to find out if Magento has a method available that allows to create MySQL triggers and Stored Procedures.

Currently, i am using the core resource DB adapter to insert the raw SQL directly from my setup script, and was wondering if there was in fact a method available for this, similar to the way we add foreign keys, etc.

Does anyone know of anything like this either in EE or CE? A library or custom script perhaps?

5

Ok, so i haven't had much response from this so started digging around myself am able to answer half of the question i asked.

Did a simple "grep" and came across the following class tucked away in the lib folder: lib/Magento/Db/Sql/Trigger.php

Its pretty straight forward and here is what i have come up with that does work (can be placed in your install/ upgrade script):

<?php
/** @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;
$installer->startSetup();

// Trigger
$trigger = new Magento_Db_Sql_Trigger();

// Set time SQL_TIME_BEFORE / SQL_TIME_AFTER
$trigger->setTime($trigger::SQL_TIME_BEFORE);

// Set time SQL_EVENT_INSERT / SQL_EVENT_UPDATE / SQL_EVENT_DELETE
$trigger->setEvent($trigger::SQL_EVENT_INSERT);

// Set target table name
$trigger->setTarget($installer->getTable('fontera_trader/leaderboards_global_tmp'));

// Set Body
$trigger->setBody(
'INSERT INTO '.$installer->getTable('fontera_trader/leaderboards_global').'
    (entity_id, customer_id , credit_value, prize_value, games_played, rank, prev_rank)
    VALUES
    (NEW.entity_id, NEW.customer_id, NEW.credit_value, NEW.prize_value, NEW.games_played, NEW.rank, NEW.prev_rank)
    ON DUPLICATE KEY UPDATE
    customer_id = NEW.customer_id,
    credit_value = NEW.credit_value,
    prize_value = NEW.prize_value,
    games_played = NEW.games_played,
    rank = NEW.rank,
    prev_rank = NEW.prev_rank;

    SET @r = 0;

    UPDATE '.$installer->getTable('fontera_trader/leaderboards_global').'
    SET
    prev_rank = rank,
    rank = @r:= (@r+1)
    ORDER BY
    credit_value
    DESC;
'
);

// Assemble query, returns direct SQL for trigger
$triggerCreateQuery = $trigger->assemble();

// Adapter initiates query
$this->getConnection()->query($triggerCreateQuery);

$installer->endSetup();

I have added comments to give a basic idea of what can be used, else best go check the class out yourself. The body is basically raw SQL but can be compiled using Magento's conventional methods. I have used raw SQL for demonstration purposes.

That out of the way, i am still trying to find a way to implement STORED PROCEDURES without success. Has anyone come across anything like this in Magento that may be tucked away for future use?

2
  • The easiest route is just use a .sql version file with your stored procedure. More details: alanstorm.com/magento_setup_resources – B00MER May 29 '15 at 4:12
  • In addition and to avoid errors, you can specify a trigger name with $trigger->setName('my_trigger_name') and add $this->getConnection()->dropTrigger($trigger->getName()) just before $this->getConnection()->query($triggerCreateQuery); – Kevin Thomas Aug 4 '17 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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