SQL Server Trigger When New Value  Old Value
There are a few things that need to be fixed in your trigger:


The WHERE condition of c2.NAME &lt;&gt; I.NAME will always be false since the inserted pseudo-table is the current definition of those same rows in the real table (at least in an AFTER Trigger). This one line was preventing anything else from happening.
If it weren't for issue #1, you would be seeing "odd" behavior regarding what rows in the contact table were being updated by this Trigger. The issue here is that you have two independent references to the same table in the main UPDATE clause and the FROM clause. You have UPDATE contact (notice there is no table alias) and then FROM contact c2 (notice the c2 alias, making this a second reference to the same table. You can either remove the c2 alias from the FROM clause (such that both references are just contact), OR, what I recommend for readability is to keep the alias and use it in place of the table name in the UPDATE clause, such that it reads: UPDATE c2 ....
Finally, when comparing strings to determine if there are any changes, you really need to use a binary collation (one ending in _BIN2 if available, else one ending in _BIN). This will prevent any "insensitive" options (such as case and/or accent) from masking changes, and it will also prevent string normalization from masking any changes.


Updated code:

ALTER TRIGGER [dbo].[ERROR_CLEAR]
ON [dbo].[contact]
AFTER UPDATE
AS BEGIN
  SET NOCOUNT ON;

  IF (UPDATE (NAME))
  BEGIN
    UPDATE con
    SET    con.ERROR = NULL
    FROM   contact con
    INNER JOIN DELETED del
            ON del.ID = con.ID
    WHERE  ins.NAME &lt;&gt; del.NAME COLLATE Latin1_General_100_BIN2
  END;
END;