November 20, 2017 at 11:12 am
is there a way to track any change to a job via a trigger? the audit does not supply enough information for us
November 20, 2017 at 12:13 pm
myukas - Monday, November 20, 2017 11:12 AMis there a way to track any change to a job via a trigger? the audit does not supply enough information for us
You can create a trigger on the sysjobs table in msdb.
Sue
November 20, 2017 at 12:35 pm
sysjobs does not show everything that the audit shows where is the audit info stored
November 20, 2017 at 12:46 pm
myukas - Monday, November 20, 2017 12:35 PMsysjobs does not show everything that the audit shows where is the audit info stored
I have no idea what that even means or what you are looking for. Can you post the DDL for the trigger on sysjobs?
If you created an audit, the info in stored in whatever you defined as the target or audit destination. What information from what audit are you looking for?
Sue
November 20, 2017 at 12:54 pm
i am working for a client and they want to know when a job has been added deleted or updated. they do not like the audit feature which i personally like it shows a lot of info. they do not like that audit only saves to an external file or event log. they want it to save to a file. when i look at the sysjobs tables i do not see nearly the same information as i do in the audit log. so they want a trigger created to show all that info which i think can not be done so my question is there any system tables where this audit information is stored
November 20, 2017 at 1:29 pm
myukas - Monday, November 20, 2017 12:54 PMi am working for a client and they want to know when a job has been added deleted or updated. they do not like the audit feature which i personally like it shows a lot of info. they do not like that audit only saves to an external file or event log. they want it to save to a file. when i look at the sysjobs tables i do not see nearly the same information as i do in the audit log. so they want a trigger created to show all that info which i think can not be done so my question is there any system tables where this audit information is stored
When you define and run an audit, that information is stored in the target or destination you defined for the audit. It's not stored anywhere else unless you have other audits, traces, extended event sessions capturing that information. That still doesn't say what information you are looking for though.
When you create a trigger, you have access to more than just the inserted and deleted data. You can still capture information such as: SELECT @@SERVERNAME, GETDATE(), SUSER_SNAME(), DB_NAME(), @@SPID
It might be best to try creating a trigger, researching some of how you capture this and other information in a trigger. And then you can store the information similar to what's done with triggers and audit tables. You will then need to write something to export that data to a file.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply