Thanks for providing DDL and sample data, but please test before posting.

What results are you expecting?

John

Yes, because IF @newEnabled <> @oldEnabled will always evaluate to False for an insert or delete,  That's because there will only be an Inserted or a Deleted table and therefore @newEnabled or...

You don't appear to have taken ZZartin's advice about using FOR UPDATE, nor mine (five days ago) about making the trigger work for multiple modifications.  Nor does it look like...

OK, you've posted the code that does work.  If you show us what you've tried that doesn't work for added and deleted jobs, we should be able to help out.

How (or why) do you have a stored procedure that includes CREATE FUNCTION statements?!

I'd start by searching this site for string splitter functions.  The ones in your codes...

Stefan

It works for me.  Note my modifications to your code:
DECLARE @command varchar(1000)
SELECT @command = 'SELECT ''?'', * FROM sys.symmetric_keys'
EXEC sp_MSforeachdb @command

John

As far as I know, those numbers identify the components of the package.  Since the error message provides the respective names of those components (UAT tablename Destination.Inputs[OLE DB Destination Input]...

This isn't very elegant, but it seems to work!

WITH Pivoted AS (
    SELECT
         PAT_ID
    ,    RECORDING_DTM
    ,    CASE
            WHEN RECORD_TYPE = 'BLOOD PRESSURE' THEN...

For schedule changed, see the last line of my first reply.  For job deleted, you need to look for rows with the same job_id that appear in the Deleted virtual...

No "standard" guidelines, no, but here are a few things that I stick to.  There will be exceptions to nearly all of them!

(1) Choose the clustered index key...