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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-05-13 : 23:51:43
|
| Murat writes "Hi,i want to ceate trigger on syscomments which is a system table. I can create trigger on syscomments and it fired when i direct modified (INSERT, UPDATE, DELETE) this table , but it isn t fired when ý use drop proc, create proc or alter proc commands. I want to learn that is it possible." |
|
|
borisk
Starting Member
1 Post |
Posted - 2004-07-05 : 05:26:18
|
| Hi guys, I tried to do so, but I create a trigger on syscomments table, I directly have a message told me I'm not the database owner 'dbo' but I AM !!! I can create triggers on user tables but not on system tables... Is there a workaround for this ? If I can create a trigger on systable maybe I can help you to go further, keep in mind syscomments.[Text] is a column with formula so a change on this table could to be not seen... (I already had this problem with ADO.NET so I suppose it'll be the same case using SQL Server)BoRisK |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-05 : 06:54:47
|
| Can you solve your problem creating a "Delta" on syscomments?i.e. copy the contents of syscomments into a table of your own and then periodically find rows in syscomments which are not in your table (INSERTs), in both tables but different (UPDATEs) and in your table but not syscomments (DELETEs) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-07-05 : 07:00:11
|
| You can't put triggers on system tables - and if you could it would be a good way of corrupting the database.Have a look athttp://www.nigelrivett.net/DMOScriptAllDatabases.htmlIf you want a way of detecting changes to structures.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-05 : 11:59:17
|
| Just put a trace on your database that detects all ddl changes. Have it written to a table. That's fairly simple to put in place.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
triggers on system tables