General behaviour
When executing orders on a relational database, it is possible to include them in a transaction. Thus, if an error occurs during the transaction, it will be possible to Rollback all the orders executed since the last Commit.
When creating the SQL Operation, in the Properties tab :
- clic on Sql Transaction Name to activate it and set a name (eg : TRANSAC1)
- clic on Sql Transaction Type to activate it and select NOCOMMIT
Set the same configuration for all SQL Operations contained in the transaction.
If the transaction ends without any error, an automatic Commit is done by the runtime, on this transaction.
Restriction
With some technologies, there is a restriction : only DML orders can be rollbacked. DDL orders have an automatic (implicit) Commit.
Case 1 : DDL orders have an implicit Commit. Example with an Oracle database
The same behaviour was observed with HSQL, Postgres, MySQL, H2 and SybaseIQ databases.
Case 2: DDL orders don't have implicit commit. Example of a MSSqlServer database :
