Click here to monitor SSC
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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6820 Visits: 1917
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/triggers_1.asp

K. Brian Kelley
@‌kbriankelley
roygelerman
roygelerman
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 20
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.



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

Group: Moderators
Points: 6820 Visits: 1917
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
@‌kbriankelley
Rodknee
Rodknee
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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??



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

Group: Moderators
Points: 6820 Visits: 1917
What was the error you received?

K. Brian Kelley
bk@warpdrivedesign.org

K. Brian Kelley
@‌kbriankelley
Rodknee
Rodknee
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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.



vinniec74
vinniec74
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
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



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

Group: Moderators
Points: 6820 Visits: 1917
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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6820 Visits: 1917
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
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3698 Visits: 3120
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
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