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