Triggers in SQL Server 7.0 and 2000 - The Common Ground
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/triggers_1.asp
K. Brian Kelley
@kbriankelley -
The article on Triggers did not mention that the Trigger is fired only one timer per DML statement - Insert, Update, or Delete. The SQL Data Manipulation Statement may affect more than one row and this was not mentioned. An example, or at least a warning should be included. The Delete trigger example showing a rollback tran if the proffessor had tenure would not work if we are deleting more than one row, and the last row processed by the trigger did not have tenure.
There needs to be an example of handling Identity columns and Insert triggers. Triggers sometimes mess up the value returned by @@Identity.
There should be an example of a trigger to track all changes to a database table. It was mentioned in the article, but there was no example.
-
quote:
The article on Triggers did not mention that the Trigger is fired only one timer per DML statement - Insert, Update, or Delete. The SQL Data Manipulation Statement may affect more than one row and this was not mentioned. An example, or at least a warning should be included. The Delete trigger example showing a rollback tran if the proffessor had tenure would not work if we are deleting more than one row, and the last row processed by the trigger did not have tenure.I should have mentioned something about the DML statement possibly affecting multiple rows. The DELETE example did assume a single delete as well. One would hope that the university isn't going through a massive purge!
quote:
There needs to be an example of handling Identity columns and Insert triggers. Triggers sometimes mess up the value returned by @@Identity.My purpose was a brief overview, so a topic such as this wasn't covered. But thanks for pointing it out, because it is important. You are exactly right. The @@Identity is going to be the value of the last insert to a row with an identity column, and this may not be the original insert at all.
quote:
There should be an example of a trigger to track all changes to a database table. It was mentioned in the article, but there was no example.Here's how we could track all three taking from the initial example using DELETE:
/* For Insert */
CREATE TRIGGER trig_insert_Employees
ON Employees
FOR INSERT
AS
INSERT EmployeesHistory
SELECT EmployeeID, FirstName, LastName, 'Inserted' Operation
FROM inserted
/* For Delete */
CREATE TRIGGER trig_delete_Employees
ON Employees
FOR DELETE
AS
INSERT EmployeesHistory
SELECT EmployeeID, FirstName, LastName, 'Deleted' Operation
FROM deleted
/* For Update */
/* Records old record and new record */
CREATE TRIGGER trig_update_Employees
ON Employees
FOR UPDATE
AS
INSERT EmployeesHistory
SELECT EmployeeID, FirstName, LastName, 'Deleted - Update' Operation
FROM deleted
INSERT EmployeesHistory
SELECT EmployeeID, FirstName, LastName, 'Inserted - Update' Operation
FROM inserted
K. Brian Kelley
K. Brian Kelley
@kbriankelley -
Some questions I have with Triggers are:
When I create an Insert, Update, or Delete trigger. which appends the info to an existing Log table, I receive an error once use my objects on MTS...
Is there a process that I missed out in order for them to work??
-
-
Thanks for asking. I have removed the triggers whilst using MTS. I will only be able to put this back by next week as our application is going live at a client today and the weekend, to be ready by Monday for the Users...When I get the error I will post it up here.. thanks.
-
I'm doubtful about the 'transaction' part of the article... the insert/update/delete statements AND the trigger are all part of the same transaction, so they'll be both rolled back in case a ROLLBACK TRAN is used inside the trigger.
No mention about the importance of keeping trigger code very focused (check the updated column(s), use the 'IF @@ROWCOUNT = 0 RETURN' statement, so on).
Triggers are extremely helpful in many different contexts, but many times they are just poorly written, hurting performance especially on pre-2000 versions...
Lorenzo
-
Agreed, in that triggers can be poorly written and really harm performance. Techniques such as checking to see if a row was updated or verifying if any rows were modified are good ideas.
However, as far as the transactional aspect is concerned, it's in SQL Server 7 and 2000. From Books Online:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_06_4qcz.asp
specifically:
quote:
A trigger always operates as if there were an outstanding transaction in effect when the trigger is executed. This is definitely true if the statement firing the trigger is in an implicit or explicit transaction. It is also true in autocommit mode. When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.This means that any time a BEGIN TRANSACTION statement is issued in the trigger, it is actually beginning a nested transaction. Because a nested BEGIN TRANSACTION statement is ignored when rolling back nested transactions, ROLLBACK TRANSACTION issued in the trigger always rolls back past any BEGIN TRANSACTION statements issued by the trigger itself. ROLLBACK rolls back to the outermost BEGIN TRANSACTION.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
It would have basically the same effect as a cursor in a stored procedure. The difference is, the trigger fires every time the specified database operation (INSERT, UPDATE, or DELETE) is performed on the table. So if you have a lot of operations against a given table, that trigger is going to fire a lot of times. That's typically why anything complex should be avoided in triggers if possible.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
I think it is worth mentioning that the maximum number of nested triggers, or stored procedures for that matter, is 32.
-
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.
-
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 -
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.
-
The trigger fires once.
K. Brian Kelley
@kbriankelley -
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...
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply