Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Triggers in SQL Server 7.0 and 2000 - The Common Ground Expand / Collapse
Author
Message
Posted Friday, January 21, 2005 6:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2008 10:53 AM
Points: 292, 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.




Post #157107
Posted Saturday, January 22, 2005 11:49 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #157120
Posted Tuesday, June 26, 2007 7:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
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.



Post #376645
Posted Tuesday, June 26, 2007 3:29 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876

The trigger fires once.

 



K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #376797
Posted Friday, March 14, 2008 6:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 11:45 AM
Points: 1, Visits: 14
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...
Post #469350
Posted Saturday, March 15, 2008 8:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
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.



Post #469828
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse