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 Sunday, July 29, 2001 12:00 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/triggers_1.asp

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 #662
Posted Monday, July 30, 2001 10:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 27, 2013 7:13 AM
Points: 79, Visits: 19
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.




Post #20384
Posted Tuesday, July 31, 2001 2:21 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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
bk@warpdrivedesign.org


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 #20385
Posted Thursday, August 2, 2001 5:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 13, 2002 12:00 AM
Points: 2, Visits: 1
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??






Post #20386
Posted Thursday, August 2, 2001 2:31 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
What was the error you received?

K. Brian Kelley
bk@warpdrivedesign.org


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 #20387
Posted Friday, August 3, 2001 3:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 13, 2002 12:00 AM
Points: 2, Visits: 1
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.


Post #20388
Posted Sunday, December 1, 2002 6:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 5, 2009 3:04 AM
Points: 10, Visits: 2
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




Post #20389
Posted Sunday, December 1, 2002 7:21 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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, 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 #20390
Posted Friday, February 28, 2003 9:19 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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, 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 #20392
Posted Friday, January 21, 2005 2:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 2,889, Visits: 1,779
I think it is worth mentioning that the maximum number of nested triggers, or stored procedures for that matter, is 32.

LinkedIn Profile
Newbie on www.simple-talk.com
Post #156891
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse