March 25, 2002 at 1:46 pm
In SQLServer7 I'm trying to create a trigger in a system table.
I'm giving all the permissions on this table to the pulic and guest.
SQL Server still doesn't allow me to save the trigger because of the permission problems.
I also trying to set a foreign key for the field in the system table and cannot do it either.
Could anybody help me with these problems?
Alex
March 26, 2002 at 5:28 am
Exactly what I you trying to do. I am afraid doing anything to some of the system tables like this could potentially blow you processing times out of the water and weaken your server. Explain and I will try to understand the merrit before I try to see if what I think will work will.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 26, 2002 at 7:40 am
James,
Thank you for your response.
This is the old problem with the commenting fields in SQLServer7.
I created a table 'Comments' with 4 fields in it (table_name, field_id, field_name & comment) and wrote few lines of code in Query Analyzer to populate the values of the User tables into my new table. Here is the code:
INSERT INTO Comments (table_name, field_id, field_name)
SELECT
sysobjects.name,
syscolumns.colid,
syscolumns.name
FROM sysobjects
LEFT OUTER JOIN
syscolumns
ONsysobjects.id = syscolumns.id
LEFT OUTER JOIN
Comments
ONsysobjects.name = Comments.table_name
WHEREsysobjects.xtype = 'U'
AND sysobjects.name NOT IN (Select Comments.table_name from Comments)
ANDsyscolumns.colid NOT IN (Select Comments.field_id from Comments)
Now I'm trying to set triggers or foreign keys in sysobjects and syscolumnns tables.
Truly, I don't see another way to update, insert or delete the records in my 'Comment' table.
Thank you in advance.
Alex
March 26, 2002 at 8:12 am
Do you need to? There were some issues in v6.5 that caused triggers set on system tables to sometimes not fire. In v7, I think these either carried over or you cannot do this.
Either way, why do you need to update your comments table at object creation time? You can do this left outer join when people query for the comments. If they enter a comment, then insert a new row in your table.
Steve Jones
April 1, 2002 at 6:27 am
I agree with Steve on the trigger on system tables. I don't think they will fire and suggest you look at what he stated.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
November 1, 2004 at 3:58 pm
Is it possible to add triggers to system tables in 2k?
November 2, 2004 at 12:38 am
Yes and no.
While it is technically possible to create a trigger on some system tables of msdb, it is not possible to create trigger on tables in master. And it is not necessary, imho. Also, those triggers are not guaranteed to fire. Playing aroung with the system tables is one of the best and easiest ways to fubar SQL Server.
At best, you're running an unsupported system, which might be not the best start when you' need to open a case with PSS
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 2, 2004 at 1:05 am
NO, IT'S NOT POSSIBLE:
According to SQL Server BOL: "Note Because SQL Server does not support user-defined triggers on system tables, it is recommended that no user-defined triggers be created on system tables".
YES, IT'S POSSIBLE:
Of course, you can try (NOT RECOMMENDED and NOT SUPPORTED!!!) and for some system table (I know at least two) your trigger will work (fire) ...
_______________________________________________
welcome to TechTarget's SQL Server forum: http://myitforum.techtarget.com/forums/tt.asp?appid=73
Alexzander N. Nepomnjashiy
Technical editor for Wrox Press:
"SQL Server 2000 Fast Answers"
Technical editor for Apress:
"Honeypot for Windows"
"SQL Server Yukon Revealed"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply