Create Trigger on sys.objects - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Create Trigger on sys.objects

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-09-08 : 05:54:56

Hi have an specific problem on creating triggers

I try to do this:

USE MyDatabase;
GO

IF OBJECT_ID('dbo.[Trigger1]','TR') IS NOT NULL
DROP TRIGGER dbo.[Trigger1];
GO

CREATE TRIGGER dbo.[Trigger1]
ON sys.objects
AFTER UPDATE, INSERT, DELETE
AS
BEGIN

PRINT 'DO SOMETHING'

END

I get the messsage:
Msg 8197, Level 16, State 4, Procedure Trigger1, Line 2
The object 'objects' does not exist or is invalid for this operation.

How could i create an trigger to it?

OR what im trying to do (without hopeful painful manage) is to creates an trigger to each table i have, i have hundred tables. I want to create same trigger on every tables, that is doing my standard procedure everytime one of the tables is updated or has been changed.

Thanks
V

Sachin.Nand

2937 Posts

Posted - 2010-09-08 : 06:07:52

Why you want to create a trigger on sysobjects?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-08 : 06:15:49

You can't create triggers on the system tables. Also note that sys.objects is a view, not a table.

What I usually recommend is a stored procedure that creates the triggers for each table. Then, when you make schema changes, you can run the procedure.
If you want a trigger to fire for ALTER TABLE, look at DDL triggers.

--
Gail Shaw
SQL Server MVP

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-09-08 : 08:26:23

Thank GillaMonster!

That was probably the same solution i could came up to. I guess in that case want some form of a task schedule
that is performed by an timer in sql server.

Some idea/tips how to achieve that?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-08 : 08:33:47

A SQL Agent job.

--
Gail Shaw
SQL Server MVP

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-09-11 : 11:15:45

Thanks GM!, Lucky for me, i found it out that Create Job is perfect for this, so i did.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources