They are different. When you enclose something in single quotes, it becomes a string -- this is true of all keywords in SQL, including NULL.

When you use just NULL, it is the SQL keyword, and it is compatible with all types.

So, these are quite different:

insert into t (col1, col2)
    values ('2021-05-25 11:58:41.000', NULL);

and:

insert into t (col1, col2)
    values ('2021-05-25 11:58:41.000', 'NULL');

'NULL' is a string and only compatible with a string type. You should get a type-conversion error if the column is not a string.

The one caveat is if you are storing the values in a text file for loading into the database. In such a file, strings may or may not be delimited with single quotes, depending on the structure of the file.

As per the comments, I'm going to assume you're getting mixed up between update and insert.

 USE [DB_A] ;
 UPDATE dbo.address
 SET City = 'test',
     State = 'ing'
 WHERE City IS NULL ;

And

 USE [DB_A] ;
 UPDATE dbo.address
 SET City = NULL,
     State = NULL
 WHERE City = 'test' ;

I think you're there otherwise :-)

The most concordant solution would be to change the front-end application so that, in the first place, it inserts Null instead of 0.

If this isn't possible, it might be relevant to explain why as part of the question, to understand exactly what the profile of limitations are on a solution.

As @SergeyA notes in the comments, it may be a possibility to have a zero-keyed entry in the master table which is used to represent a default or absent link whilst maintaining the referential integrity between the tables.

If you must fall back to triggers, in my view it would be worth considering the possibility of using the trigger to enforce the foreign key selectively, than to use the trigger to rewrite the data on the fly. That is, you would disable the built-in foreign key check, then replace it with a trigger that only enforces the foreign key if it is non-zero, and ignores the enforcement if it is zero.

It is very easy to abuse triggers in ways that make subsequent supervision or re-adaptation of the database extremely difficult and error-prone.

Anything that alters the behaviour of basic SQL commands (such as rewriting an insert zero into an insert null, on the fly), or adds side-effects or forms of reactivity which are not limited to the enforcement of constraints, and does it all in a way that must be completely transparent to a front-end module that cannot be changed, are really the kind of things that are done when an application is already heavily sclerotic and terminally-ill.