I have a little more time today, corrected version below. 
The cte is to (1) make it easier to use in a function and (2) to make it easier...

As usual with age, leap year birthdays are the deciding factor.

If someone was born on Feb 29, 2004, how old are they on Feb 28, 2018?  In your...

No.  In fact, generally they are a bit faster since you don't have to decide within the trigger which action -- DELETE / INSERT / UPDATE -- has occurred.

Does is really matter if the numbers are sequential or is everyone just used to that?

If it doesn't really matter, you might just use an identity column and...

I prefer this alternative.  Not for performance reasons, mostly that it allows easier customization of the suffix chars to be assigned.  Sometimes certain chars, such as I and O, are...

Oops, correction:

UPDATE [EC_StoreAttributes]

  SET AttributeValue = CONVERT(varchar(2), GETDATE(), 12) -- 112)112) + /* 12 rather than 112 */
   SUBSTRING(AttributeValue, 3, LEN(AttributeValue)...

Based on your last comments, this looks like the only way that might really help.  Your keys columns are exceptionally non-selective, which makes it more difficult to get best performance.

UPDATE t
SET response_sol_id = x.[solicitation_id]
FROM #temp_stp t
 CROSS apply (SELECT P.solicitation_id
   FROM [SOLICIT].[gtm_doc_prod] P
   INNER JOIN [SOLICIT].[gtm_doc]...

Again, you need to cluster the table on those columns then.  You can adjust nonclus indexes all day and it most often won't do you much good.

Yes, there is.  It's called "SNAPSHOT ISOLATION".  That's almost exactly like Oracle: point-in-time for SELECTs, version rows stored to handle long-running queries, readers don't block writers, writers don't block readers,...

Hard to tell without selectivity / row count info, as Chris noted.

But, if the partition and dataareaid are how you typically query this table, and guessing that PARTITION...

UPDATE [EC_StoreAttributes]

    SET AttributeValue = CONVERT(varchar(2), GETDATE(), 112) +
        SUBSTRING(AttributeValue, 3, LEN(AttributeValue) - 4) +
       ...