Use this statement to establish a savepoint within the current transaction.
SAVE TRANSACTION savepoint-name
Establish a savepoint within the current transaction. The savepoint-name is an identifier that can be used in a ROLLBACK TRANSACTION statement. All savepoints are automatically released when a transaction ends. See Savepoints within transactions.
SQL/2003 Vendor extension.
The following example displays five rows with values 10, 20, and so on. The effect of the DELETE, but not the prior INSERTs or UPDATE, is undone by the ROLLBACK TRANSACTION statement.
BEGIN SELECT row_num INTO #tmp FROM sa_rowgenerator( 1, 5 ) UPDATE #tmp SET row_num=row_num*10 SAVE TRANSACTION before_delete DELETE FROM #tmp WHERE row_num >= 3 ROLLBACK TRANSACTION before_delete SELECT * FROM #tmp END