The reason the code
Insert into dbo.try VALUES (1, 'שלום')
works when the database has a Hebrew collation, but does not work when the database has a non-Hebrew collation, but the column has a Hebrew collation is that string literals always have the collation of the database. See https://learn.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver15 and scroll down to the REMARKS section in the paragraph numbered 3.
So if the database collation is not a Hebrew collation, the literal value cannot contain Hebrew characters. So 'שלום' is converted to '????' in the storage for that literal. Then when the literal value is placed the the column with a Hebrew collation, the Hebrew characters are gone and the value is '????'.
The fix is to make the literal an nvarchar literal, that is
Insert into dbo.try VALUES (1, N'שלום')
Tom