I have a general concern about how automatically the DBMS handles transactions.

If you try in SQLServer 6 the following in the console or inside a stored
procedure

BEGIN TRANSACTION
insert into table (2, 'something');
insert into table (1, 'something');
COMMIT;

Suppose the table "table" had only one row => (1,'Hello') and that the
first column in a Primary Key.
As you see the second statement in the transaction will fail because it
breaks a primary key.
The funny part is that as the transaction never does a rollback the first
statement takes place and the table "table" ends up like this:

(1,'Hello')
(2,'something')

I tried the same in MySql using InnoDB and Transactions and the same
happens BUT...

... (and here comes Interbase), in Interbase i did this:
I created a stored procedure

CREATE PROCEDURE ins AS
BEGIN
insert into table values (2, 'qwerty');
insert into table values (1, 'dvorak'); /* here it's gonna fail*/
insert into table values (3, 'it goes on executing');
EXIT;
END ^

Then called it:
execute procedure ins;

and then called:
commit;

As you might already know the table "table" is not nodified, not even the
first insert even though i never called a rollback or checked for error
codes...

Is this how all DBMS's should handle these errors? calling rollback
automatically or making the programmers check for error codes and calling
rollback explicitly?

I started looking into this when a saw all those

IF (@@error)
rollback;

after every statement in the SQLServer stored procedues and thought if they
were necessary.
Any ideas are welcomed.