November 12, 2007 at 9:38 pm
Comments posted to this topic are about the item Trigger Trivia
November 13, 2007 at 12:48 am
Good synopsis of uses, Andy!
--Jeff Moden
Change is inevitable... Change for the better is not.
November 13, 2007 at 3:50 am
Short but very informative. Thanks!
November 13, 2007 at 4:46 am
By chance the disable trigger hint came just when I needed it. Thanks for the nice synopsis you won't find somewhere else.
November 13, 2007 at 5:16 am
if you need all trigers disabled, you can specify 'all' instead of triggername. i found it useful
November 13, 2007 at 5:16 am
Great article, Andy - thanks. Can I just ask a question about Tip#4?
If you want to really make sure no resultsets occur you can use spconfigure to set disallow results from triggers to off.
Is this a typo and did you mean to say "on"? Also, if you do this, you'll get an error message every time a trigger tries to return a result set and so you'd need to add some error-handling code to your trigger and/or application.
John
November 13, 2007 at 5:38 am
John, you're right, it should be ON. Calling it a typo is generous!
November 13, 2007 at 5:39 am
Dragos, I don't think I knew that about ALL, thats a nice shortcut.
November 13, 2007 at 6:24 am
Wow!
The tip about DDL and CLR triggers was great!
It's too bad we don't see more articles like this!
Short, Sweet, and useful!
November 13, 2007 at 6:43 am
Andy, that's the one i used most times... if not always. glad if it helps
November 13, 2007 at 6:54 am
Very nice job. Short, sweet and to the point.
November 13, 2007 at 7:47 am
Outstanding, Andy. Keep 'em coming.
November 13, 2007 at 7:49 am
Regarding "Tip #6 - Triggers execute with the permissions of the user", this is not completely correct. Given the example with tables Contact and ContactHistory and a trigger on that Contact table that references the ContactHistory table, the security rules for SQL Server 2000 are:
If the ContactHistory table has the same owner as the Contact table, then the user does not need rights to the ContactHistory table.
If the ContactHistory table has a different owner than the Contact table, then the user does need rights to the ContactHistory table.
For SQL Server 2005, substitute "Schema" for "Owner".
SQL = Scarcely Qualifies as a Language
November 13, 2007 at 9:08 am
Tip #9 opened my eyes. Triggers on views!!! I never thought to check if such a thing was possible. I have some apps that directly modify data and I was pondering how I could impliment a better schema without trashing the existing app. Now I can sneak in my new framework and my crummy application will be none the wiser!
November 13, 2007 at 10:41 am
It's important to fix #6 about permissions - that does sound misleading, especially that more often than not folks will be updating table in the same schema.
The one about "selective updates using hostname" has a security issue about it - the host name can be set as a part of connection string. E.g. this is not the attribute one can rely on.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy