I am trying to update certain fields in the table of one database (database A) when changes are made to a different database's table (database B).
Presently I am trying to create a trigger on the table in database B that will start a SQL Agent job with my desired T-SQL language to do the required changes in database A. But Im getting the error "@job_name ('sync_juris_matter_update_with_rwdb') does not exist". Ive double checked the job name spelling, even copied and pasted the job name into the trigger.
Here is the trigger Im running:
CREATE TRIGGER [Sync Matter updates with RWdb] ON [dbo].[Matter_Log]
FOR INSERT, UPDATE, DELETE
EXEC msdb.dbo.sp_start_job @job_name = 'Sync_Juris_matter_updates_with_RWdb'
Ive also tried copying the 'sp_start_job' and 'sp_verify_job_identifiers' SPs into the database A's SP repository and then modifying the trigger's EXEC statement to:
EXEC sp_start_job @job_name = 'Sync_Juris_matter_updates_with_RWdb'
but still get the same error. Can anyone lend a hand on what Im doing wrong?
FYI, I tried creating a trigger in the table of database B to directly update the table in database A. However, database B is the database of a 3rd party vendor's application and has it's own username account with an unknown password. This username account is tied to the dbo server login. Therefore I got a permission error when the trigger would run because database B uses dbo with the sa username account and there is apparently no way to specify alternate credentials to use in T-SQL.