How to use if updating on nested column in triggers
For more information, see CREATE TRIGGER (Transact-SQL).
Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger.
You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX.
In DML triggers, the inserted and deleted tables are primarily used to perform the following: If trigger actions depend on the number of rows a data modification effects, use tests (such as an examination of @@ROWCOUNT) for multirow data modifications (an INSERT, DELETE, or UPDATE based on a SELECT statement), and take appropriate actions.
That way you would avoid executing the first (manual) update over the DB.
That trigger above won't be called again, and you would be safe (unless you get into some kind of deadlock; that could be possible but maybe I'm wrong). A better option would be using an INSTEAD OF trigger.
DML trigger statements use two special tables: the deleted table and the inserted tables.
You can control whether AFTER triggers can be nested through the nested triggers server configuration option.