Checking Triggers....

Is there a way to check if triggers have been enabled or disabled?
Already found it
select objectproperty (object_id('triggernamegoeshere'),
'ExecIsTriggerDisabled')
A result of 0 = enabled.
"nivek" <eckart_612@.hotmail.com> wrote in message
news:UemdnQD7XMXDlCLcRVn-sQ@.centurytel.net...
> Is there a way to check if triggers have been enabled or disabled?
>
|||SELECT OBJECTPROPERTY(OBJECT_ID('TriggerName'), 'ExecIsTriggerDisabled')
Will return 1 if the trigger is disabled, 0 if it's enabled, NULL if you
enter an invalid name.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"nivek" <eckart_612@.hotmail.com> wrote in message
news:UemdnQD7XMXDlCLcRVn-sQ@.centurytel.net...
> Is there a way to check if triggers have been enabled or disabled?
>
|||SELECT name, CASE OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') WHEN 1 THEN
'Disabled' ELSE 'Enabled' END AS status
FROM sysobjects
WHERE xtype = 'TR'
Jacco Schalkwijk
SQL Server MVP
"nivek" <eckart_612@.hotmail.com> wrote in message
news:UemdnQD7XMXDlCLcRVn-sQ@.centurytel.net...
> Is there a way to check if triggers have been enabled or disabled?
>

Checking Triggers....

Is there a way to check if triggers have been enabled or disabled?Already found it
select objectproperty (object_id('triggernamegoeshere'),
'ExecIsTriggerDisabled')
A result of 0 = enabled.
"nivek" <eckart_612@.hotmail.com> wrote in message
news:UemdnQD7XMXDlCLcRVn-sQ@.centurytel.net...
> Is there a way to check if triggers have been enabled or disabled?
>|||SELECT OBJECTPROPERTY(OBJECT_ID('TriggerName'), 'ExecIsTriggerDisabled')
Will return 1 if the trigger is disabled, 0 if it's enabled, NULL if you
enter an invalid name.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"nivek" <eckart_612@.hotmail.com> wrote in message
news:UemdnQD7XMXDlCLcRVn-sQ@.centurytel.net...
> Is there a way to check if triggers have been enabled or disabled?
>|||SELECT name, CASE OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') WHEN 1 THEN
'Disabled' ELSE 'Enabled' END AS status
FROM sysobjects
WHERE xtype = 'TR'
--
Jacco Schalkwijk
SQL Server MVP
"nivek" <eckart_612@.hotmail.com> wrote in message
news:UemdnQD7XMXDlCLcRVn-sQ@.centurytel.net...
> Is there a way to check if triggers have been enabled or disabled?
>

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.

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.

Checking for stored procedure return code in SQL Agent job

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

I would think that it could be a bit more 'robust' to have a DDL trigger that would set off your alert process upon an ALTER TRIGGER event...

Otherwise, use RAISERROR when a row with disabled trigger if found.

checking dates with triggers or constraints

Hi,

I have a table that contains various data with a start and end date. When a user enters the information I need to check that the end date is older than the start date.

What is the easiest way to do this - using a trigger?

And is the code below correct ?

CREATE TRIGGER checkdate
ON Prodn_Problem
FOR INSERT,UPDATE
AS
DECLARE @.start_date datetime
SELECT @.start_date = (SELECT startdate FROM inserted)
DECLARE @.end_date datetime
SELECT @.end_date = (SELECT enddate FROM inserted)
BEGIN
IF(@.end_date < @.start_date)
ROLLBACK
END

Thanks.By using a trigger, you check the data when it's already inserted or updated in the table. When you use a check constraint you can verify before the information is entered. Another method is to create a stored procedure which does the checking an let you users enter information through this stored procedure.