How can I audit when a Trigger is disabled??

We are running SQL Server 2000 and are having a problem with database data
being changed. We have a trigger which logs when changes are made and what
the old and new data is. I really need to know if or when someone is
disabling that trigger and changing data. It is possible that the trigger i
s
being disabled and then the data is being changed and I would like that even
logged and then an operator notified by email.
Any ideas?
Thanks!
ToddTodd,
Can you lock down the database a bit more? ALTER TRIGGER requires ddl_admin
or db_owner privileges. Don't give users this level of access and they won't
be able to disable the trigger, just give them the minimum rights to do what
they need to do.
SQL Server 2005 has DDL triggers, but there's nothing in SQL Server 2000
that will allow auditing of this. I'm not actually sure if SQL Server 2005
can do it either.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"MCSA_Todd" wrote:

> We are running SQL Server 2000 and are having a problem with database data
> being changed. We have a trigger which logs when changes are made and wha
t
> the old and new data is. I really need to know if or when someone is
> disabling that trigger and changing data. It is possible that the trigger
is
> being disabled and then the data is being changed and I would like that ev
en
> logged and then an operator notified by email.
> Any ideas?
> Thanks!
> Todd

how can I

How can I get a value from a variable in one trigger to populate a variable
in another trigger.Hi
Persist it to a table (not Temporary).
No global variables in SQL Server.
Regards
Mike
"seeker53" wrote:
> How can I get a value from a variable in one trigger to populate a variable
> in another trigger.|||You can't. Also, variables aren't very much use in a trigger. Don't assign
column values to variables in a trigger because that compels you to use
cursor processing to handle multiple row updates - or to disallow multi-row
updates altogether. Neither option is a good idea.
--
David Portas
SQL Server MVP
--

how can I

How can I get a value from a variable in one trigger to populate a variable
in another trigger.
Hi
Persist it to a table (not Temporary).
No global variables in SQL Server.
Regards
Mike
"seeker53" wrote:

> How can I get a value from a variable in one trigger to populate a variable
> in another trigger.
|||You can't. Also, variables aren't very much use in a trigger. Don't assign
column values to variables in a trigger because that compels you to use
cursor processing to handle multiple row updates - or to disallow multi-row
updates altogether. Neither option is a good idea.
David Portas
SQL Server MVP

how can I

How can I get a value from a variable in one trigger to populate a variable
in another trigger.Hi
Persist it to a table (not Temporary).
No global variables in SQL Server.
Regards
Mike
"seeker53" wrote:

> How can I get a value from a variable in one trigger to populate a variabl
e
> in another trigger.|||You can't. Also, variables aren't very much use in a trigger. Don't assign
column values to variables in a trigger because that compels you to use
cursor processing to handle multiple row updates - or to disallow multi-row
updates altogether. Neither option is a good idea.
David Portas
SQL Server MVP
--

how can a trigger act asynchronously to the event that fired it in sql server 2005?

if service broker is not an option, how can the trigger and sp(s) called by our trigger act asynchronously to the event that fired the trigger in the first place. We are more concerned with the original event being committed than the actions that follow via the trigger.

Having the trigger call a job using sp_start_job would be one option if you don't want to use Service Broker, although Service broker would be a better approach.

-Sue

|||are jobs supported in all editions (including express)? I thought i read something once saying they (might have just been scheduling) are not supported in express.|||

You could also have the trigger just insert a request into a 'queue' of your own and have a SP that periodically wakes up and processes any pending requests. SSB and SQL Agent help this scenario by providing queues and jobs, respectively, but you could build some of this yourself (using WaitFor, etc.).

Don

|||

so service broker does come free and full featured (except for inter instance) with express? how about the sql job feature, except for maybe scheduling?

how can a trigger act asynchronously to the event that fired it in sql server 2005?

if service broker is not an option, how can the trigger and sp(s) called by our trigger act asynchronously to the event that fired the trigger in the first place. We are more concerned with the original event being committed than the actions that follow via the trigger.

Having the trigger call a job using sp_start_job would be one option if you don't want to use Service Broker, although Service broker would be a better approach.

-Sue

|||are jobs supported in all editions (including express)? I thought i read something once saying they (might have just been scheduling) are not supported in express.|||

You could also have the trigger just insert a request into a 'queue' of your own and have a SP that periodically wakes up and processes any pending requests. SSB and SQL Agent help this scenario by providing queues and jobs, respectively, but you could build some of this yourself (using WaitFor, etc.).

Don

|||

so service broker does come free and full featured (except for inter instance) with express? how about the sql job feature, except for maybe scheduling?