BEGIN TRANSACTION;
DELETE FROM DW_RX_V.RX_FLL_FCT
WHERE rx_nbr = IN_RX_NBR
AND rx_fll_dte = CAST(IN_RX_FLL_DTE AS TIMESTAMP(6) FORMAT 'MMDDYYYY')
AND rfl_tie_brk_nbr = IN_RFL_TIE_BKR;
IF :ACTIVITY_COUNT = 0
THEN
ROLLBACK;
SIGNAL ERROR_HANDLER;
END IF;
DELETE from DW_RX_V.RX_FLL_DET
WHERE rx_nbr = IN_RX_NBR
AND rx_fll_dte = CAST(IN_RX_FLL_DTE AS TIMESTAMP(6) FORMAT 'MMDDYYYY')
AND rfl_tie_brk_nbr = IN_RFL_TIE_BKR;
IF :ACTIVITY_COUNT = 0
THEN
ROLLBACK;
SIGNAL ERROR_HANDLER;
END IF;
END TRANSACTION;
I just tried using the stored procedure block above, upon running, it still not able to rollback the transaction upon failure.
Does Teradata really have a rollback?
So what I want is, everytime there is no record found on the second DML statement, then rollback everything what is deleted from the start.