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:
Its pretty straight forward and here is what i have come up with that does work (can be placed in your install/ upgrade script):
/** @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;
$trigger = new Magento_Db_Sql_Trigger();
// Set time SQL_TIME_BEFORE / SQL_TIME_AFTER
// Set time SQL_EVENT_INSERT / SQL_EVENT_UPDATE / SQL_EVENT_DELETE
// Set target table name
// Set Body
'INSERT INTO '.$installer->getTable('fontera_trader/leaderboards_global').'
(entity_id, customer_id , credit_value, prize_value, games_played, rank, prev_rank)
(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;
prev_rank = rank,
rank = @r:= (@r+1)
// Assemble query, returns direct SQL for trigger
$triggerCreateQuery = $trigger->assemble();
// Adapter initiates query
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?