I am currently using SQL server transactions to ensure consistent updation of data across multiple tables. My application involves decrementing a value from a row from table A and incrementing the value to a row in table B (Similar to credit/debit in banking scenario). I know that transactions can ensure consistency. But do I need to do a double check by using triggers to check for updation rules to ensure data consistency?
There should be no reason to use triggers to double check. If you are using transactions from code, or using stored procedures as your data access layer, and you have coded correctly, then there should be no problems.