-- Bank Transfer Transaction with Error Handling
BEGIN TRANSACTION;  -- Start transaction block

BEGIN TRY
    -- Step 1: Deduct amount from sender
    UPDATE accounts 
    SET balance = balance - 500
    WHERE account_id = 101 
    AND balance >= 500;  -- Check sufficient funds
    
    IF @@ROWCOUNT = 0
    BEGIN
        RAISERROR('Insufficient funds', 16, 1);
    END;
    
    -- Step 2: Add amount to receiver
    UPDATE accounts 
    SET balance = balance + 500
    WHERE account_id = 202;
    
    -- Step 3: Log the transaction
    INSERT INTO transaction_log (from_account, to_account, amount, timestamp)
    VALUES (101, 202, 500, GETDATE());
    
    COMMIT TRANSACTION;  -- Save all changes permanently
    PRINT 'Transaction completed successfully';
    
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;  -- Undo all changes on error
    PRINT 'Transaction failed: ' + ERROR_MESSAGE();
END CATCH;