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?
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?
.sql version file with your stored procedure. More details: alanstorm.com/magento_setup_resources $trigger->setName('my_trigger_name') and add $this->getConnection()->dropTrigger($trigger->getName()) just before $this->getConnection()->query($triggerCreateQuery);