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.