SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Triggers in SQL Server 7.0 and 2000 - The Common Ground


Triggers in SQL Server 7.0 and 2000 - The Common Ground

Author
Message
Scott Hutchinson
Scott Hutchinson
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 2

The articles states "Generally, developers don't like writing a ton of extra code to add this auditing into their stored procedures and it falls to the DBAs to ensure the auditing is done."

That is the kind of ill-considered statement (and attitude) that drives a wedge between DBAs and developers. A better explanation would be "By putting this code in a trigger, it need not be repeated in every stored procedure and/or dynamic SQL batch that updates the table. Using a trigger also ensures that auding is performed no matter how changes are made (e.g., even by the DBA using Enterprise Manager or ad hoc queries)." Notice how my version does not imply that triggers are only useful because developers are lazy.





K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25222 Visits: 1917
The statement wasn't ill-considered. It's a reflective statement of not only my experience, but also the experience of many DBAs and developers (I was one when I wrote that statement, am one currently along with many other hats, so that "generally" applies to me, too). Consider that this article was first posted in July 2001 and yours is the first negative comment about that statement.

Along those lines, my perspective is triggers are a lot of time not the right answer for auditing. A trigger may not capture all of the information you'd like to capture. For instance, if you're using a service account or a standard SQL Server login from the web server or if you've toggled an application role on, you aren't going to be able to capture the username when the data is modified through the application. Why would you use a shared login? Performance, of course, with resource pooling. Therefore you must pass this information in via a stored procedure. However, if you've suddenly got this requirement to audit "something" (like when an outside auditor comes into an organization), triggers are often the quickest solution to show some results.

Also, the unwillingness to write the extra code isn't necessarily a sign of laziness. It's typically a product of RAD and other pie-in-the-sky concepts that basically boil down to, "You say two weeks, I promised it yesterday, so it had been be done tomorrow." Rarely does the business concern itself with how well auditing is performed. With things like HIPAA and SOX, that attutide is starting to change. But the main perspective is still, "I don't care how you have to do it, get it working and make sure it runs fast." Writing extra code often puts a developer at odds with both of these business-stated directives.

K. Brian Kelley
@‌kbriankelley
tymberwyld
tymberwyld
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1620 Visits: 275
I just need to understand one thing. If you have a DML statement that updates multiple records (whether Insert, Update, or Delete), does the trigger fire multiple times (once per affected record) or once with all records affected in the inserted and deleted virtual tables?

It seems like "INSTEAD OF" Triggers have all affected rows processed at once. I'm trying to verify that "FOR" or "AFTER" Triggers do this as well.



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25222 Visits: 1917

The trigger fires once.



K. Brian Kelley
@‌kbriankelley
sgunase
sgunase
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 20
great discussion...on rollback.
what about the scenario...where
* one trigger does updates other tables
* another trigger does updates to some other tables
* what will happen if there is rollback in one of them that kicks in....will it stop the next trigger from running.....or will the next trigger go on and do its thing.

Because you mentioned the triggers fire in no order...
tymberwyld
tymberwyld
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1620 Visits: 275
This depends and it would have to be tested. Everytime you read the @@ROWCOUNT or @@ERROR variables, SQL Server resets them back to Zero. So in theory, if one trigger read the error and Rolled Back, then the other triggers would continue on (unless there was something else that then caused them to error). You might have to make sure to re-raise the Error before the trigger is finished. Again, you'll need to test.

Update SomeTable Set...
If (@@ERROR = 0)
COMMIT
Else
ROLLBACK


Also, you can specify in what order triggers fire. It's just that most people don't do this because it's always an after though. Obviously and INSTEAD OF trigger would fire before the AFTER triggers. I can't remember the syntax to do this off hand but I know it can be done.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search