Checking for success/failure in TSQL SQL Agent job step

I am relatively new to SQL Agent job creation. I am trying to create a job that will check for disabled triggers and notify an operator. The job step I have created is Transact-SQL. I am checking sys.triggers for disabled triggers. My question is:

How do I indicate whether the job step was a success or failure in the TSQL, so I can trigger the correct response to it?

Can I check the return code from a stored procedure to determine success or failure?

Thanks,

Larry

Just raise an error in the proc like:

if exists (select * from sys.triggers where is_disabled <> 0)
raiserror('disabled triggers found',16,1)

this will cause the job step to fail

|||

Actually, I've tried using the DDL trigger to catch it. However, when you enter the DISABLE TRIGGER..... command, it does not fire the ALTER TRIGGER or ALTER TABLE event and thus can't be detected by a DDL trigger. Currently, my DDL trigger prevents a DROP trigger or an ALTER TABLE DISABLE TRIGGER event from happening. If anyone can tell me how to use the DDL trigger to prevent a plain DISABLE TRIGGER, I'm all ears.

Thanks,

Larry

|||

If, in your database/server, you are concerned with disabled Triggers, you most likely should NOT provide anyone the level of permissions that would allow them to disable Triggers.