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 25 total)
You must be logged in to reply to this topic. Login to reply