Suppose you want to add column to some table before it gets save in Magento. Example, Magento doesn’t save regular price of product when an order is placed, it only saves the selling price. So if your product have some special price in it, then Magento only saves it’s special price when an order is placed, so there is no track of regular price of that product in order item table. Same it goes to invoice item and shipment item. When creating invoice and shipment, Magento doesn’t have any track on the regular price of invoiced item’s and shipment item’s regular price.
So here I show you how you will add a column “product_mrp” in each of 3 tables and update the information without firing any query!
First of all, make an installer script in your module that will alter these three tables and add column “product_mrp” or any of your choice.

<?php
$installer = $this;
$installer->startSetup();
$installer->run(“
    ALTER TABLE sales_flat_order_item ADD COLUMN product_mrp DECIMAL(12,4) NULL;

    ALTER TABLE sales_flat_invoice_item ADD COLUMN product_mrp DECIMAL(12,4) NULL;

    ALTER TABLE sales_flat_shipment_item ADD COLUMN product_mrp DECIMAL(12,4) NULL;
“);
$installer->endSetup();
?>

After this is executed, you will find your columns added at the end of your tables.
Now we will catch the event before order is placed, before invoice is created and before shipment is saved.
config.xml

               
                   
                        singleton

Namespace_Module_Model_Observer saveProductMrpInOrder singleton Namespace_Module_Model_Observer saveProductMrpInInvoice singleton Namespace_Module_Model_Observer saveProductMrpInShipment
Now comes the Observer part that will add our column data before data is actually saved in table.
Observer.php

public function saveProductMrpInOrder(Varien_Event_Observer $observer) {
            $order = $observer->getEvent()->getOrder();
            foreach($order->getAllItems() as $item) {
                $price = Mage::getModel(‘catalog/product’)->load($item->getId())->getPrice();
                $item->setProductMrp($price);
            }
          return $this;
        }
        public function saveProductMrpInInvoice(Varien_Event_Observer $observer) {
            $invoice = $observer->getEvent()->getInvoice();
            foreach($invoice->getAllItems() as $item) {
                $price = Mage::getModel(‘catalog/product’)->load($item->getProductId())->getPrice();
                $item->setProductMrp($price);
            }
          return $this;
        }
        public function saveProductMrpInShipment(Varien_Event_Observer $observer)
    {
            $shipment = $observer->getEvent()->getShipment();
            foreach($shipment->getAllItems() as $item) {
                $product = Mage::getModel(‘catalog/product’)->load($item->getProductId());
                $price = $product->getPrice();
                $item->product_mrp = $price;
            }
        }

Now clear the cache, and place order and create it’s invoice and shipment. You will find the regular price of each of your products in all these three useful tables and hence you can track the original price information even if your product’s price has been changed afterwards.