transactions vs stored procedures

[McLaren]

Hi,

I have one question:

I am programming with PHP, CodeIgniter. But it may suit for other tools as well. I need to delete something from the table and I want to be sure that there will be deleted row from another table. In short - delete to rows, each from different table.

One way is to use transaction. With CodeIgniter I can to this:

$this->db->trans_start();

delete row from table A
delete row from table B

$this->db->trans_complete();

Another way is to create a stored procedure which deletes these to rows and call it from PHP.

I guess the achieved result is the same. So which way is better? Or is there some differences?

Edit: I saw one difference

DELIMITER $$
--
-- Procedūros
--
CREATE DEFINER=`ylakiai_darzelis`@`localhost` PROCEDURE `del_info_page`(IN eil INT)
BEGIN
	DELETE FROM info_psl WHERE eil_nr = eil;
	DELETE FROM virsutinis_meniu WHERE eile = eil;
	UPDATE virsutinis_meniu SET eile = eile - 1 WHERE eile > eil;
	UPDATE info_psl SET eil_nr = eil_nr - 1 WHERE eil_nr > eil;
END$$

DELIMITER ;

This code is phpMyAdmin export.

I need to remove this

DEFINER=`ylakiai_darzelis`@`localhost`

to work on my computer. Its not that comfortable.

[McLaren]

Now I started thinking: does stored procedures rollback the data if they stop executing :/ At first when I didn't know about transactions, I thought I can get that effect with stored procedure. At least we need only one query to run few statements.

Its very unlikely probably that some of the statements will fail in procedure. But possible, right? But I need to be sure they all are executed or none of them, because otherwise it may beak the system.

Edit:

found that it doesn't rollback. http://www.dbforums.com/mysql/1640914-transaction-stored-procedure.html you need to add some addidional code for it to rollbback. So I guess I will use transactions, it will be simpler in my small applications.