DDL trigger

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mecn - 09 Jul 2008 14:28 GMT

Hi,

Is there something like sql2005 ddl triggers taht I could create in sql2000.
Basically, I need to have trigger on database level to trace for anyone who
change sql objects(table schema change, SP mpdification...)

Thanks,

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 09 Jul 2008 14:36 GMT

In 2000 you could run a server-side trace that watches for specific DDL
events. Or look into any number of 3rd party auditing products (which do
something similar for you).

OR, take away those privileges from users. You should be able to control
who can make schema modifications in your systems, and establish a process
to document them, use source control, the whole works. It's a little late
to just find out about the changes after the fact...

On 7/9/08 9:28 AM, in article OlwDsec4IHA.3368@TK2MSFTNGP04.phx.gbl, "Mecn"
<mecn@yahoo.com> wrote:

> Hi,
>
[quoted text clipped - 3 lines]
>
> Thanks,

Reply to this Message

vinu - 09 Jul 2008 14:40 GMT

Mecn

Simple answer is NO, sql 2k doesn't have DDL triggers.
You can create a trigger on sysobjects system table to tack changes. But
trigger on a system table is not recomended.

vinu

> Hi,
>
[quoted text clipped - 4 lines]
>
> Thanks,

Reply to this Message

Mecn - 09 Jul 2008 15:19 GMT

Thanks all.

> Mecn
>
[quoted text clipped - 12 lines]
>>
>> Thanks,

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 09 Jul 2008 16:48 GMT

> You can create a trigger on sysobjects system table to tack changes. But
> trigger on a system table is not recomended.

Have you actually tried this? What kind of workaround did you use? I get
Msg 229, CREATE TRIGGER permission denied on object 'sysobjects'...

A

Reply to this Message

vinu - 09 Jul 2008 17:07 GMT

Aaron

Let me correct it, it sould not be sysobjects, what i ment was syscolumns

to create a trigger on syscolumns, do this

On the server setting tab( server property), click the 'Allow modification
to made directly to the system catalogs'

then on the database where you need to create the trigger, e.g let sy DBA
database, change the xtype column value sysobjects table to'U'

update sysobjects set xtype='U' where id=3

now create the trigger

create trigger testtr on dbo.syscolumns
for update
as

print 'test'

vinu

>> You can create a trigger on sysobjects system table to tack changes. But
>> trigger on a system table is not recomended.
[quoted text clipped - 3 lines]
>
> A

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 09 Jul 2008 17:45 GMT

> Let me correct it, it sould not be sysobjects, what i ment was syscolumns

OK, if that is the only type of change you are concerned about.

> On the server setting tab( server property), click the 'Allow modification
> to made directly to the system catalogs'
[quoted text clipped - 11 lines]
>
> print 'test'

As you said, definitely not recommended. Also consider the consequences of
applying a service pack, hotfix, or upgrading to SQL Server 2005 or beyond.

Reply to this Message

Mecn - 09 Jul 2008 18:31 GMT

Thanks all,

Last question, if I add update trigger on syscolumns table, trigger will
fire if SP modified?

Thanks

>> Let me correct it, it sould not be sysobjects, what i ment was
>> syscolumns
[quoted text clipped - 22 lines]
> applying a service pack, hotfix, or upgrading to SQL Server 2005 or
> beyond.

Reply to this Message

vinu thomas - 09 Jul 2008 21:30 GMT

stored procedures are stored in SYSCOMMENTS table. Also Aaron has pointed out
why you should not do this.

> Thanks all,
>
[quoted text clipped - 29 lines]
> > applying a service pack, hotfix, or upgrading to SQL Server 2005 or
> > beyond.

Reply to this Message