September 2, 2001 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/triggersinsqlserver7.0and2000-what'snew.asp
K. Brian Kelley
@kbriankelley
September 6, 2001 at 3:08 pm
Are the two Identity functions connection specific? In other words, if another user/process adds a record between the time you add a record and the time you call the identify function, do you still get your id or the other user's id?
Jay Madren
September 10, 2001 at 8:42 am
It depends on which function you use. If you use IDENT_CURRENT('<table name>'), then it'll return the identity value based on the last insert. If you use SCOPE_IDENTITY(), it'll return the value based on the current scope. If you need the value based on your insert, SCOPE_IDENTITY() is the way to go.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
Edited by - bkelley on 09/10/2001 08:42:31 AM
K. Brian Kelley
@kbriankelley
August 21, 2002 at 1:05 pm
How come no one references the lack of information on 'after tigger'. The syntax does not work just as it is in the online books, also in this posting?
Or do I have a problem using v2000 client against a v7 DB ?
August 21, 2002 at 1:20 pm
The "AFTER" and "INSTEAD OF" syntax is new in SQL2000, they won't work on SQL7.
Jay
Jay Madren
August 21, 2002 at 1:37 pm
jmadren is right.
INSTEAD OF triggers are brand new to SQL Server 2000. Since SQL Server didn't have Cascading referential integrity and it didn't have triggers which fired before a data change operation took place, this meant building home grown solutions to handle record changes that would affect foreign key relationships.
The triggers in SQL Server 7 fire after the data operation. In SQL Server 2000, these are now called AFTER triggers. However, if you don't specify INSTEAD OF or AFTER trigger, it'll default to AFTER.
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
August 22, 2002 at 8:05 am
Yeah, I just installed on-line books for V7 and sure enough there are no goodies for triggers.
So, my problem since to be that I wrote a trigger for a table that is 'piggy-backing' to with an application. So randomly (I think) the trigger works just fine, but other times I get a locking (block-by) by a session from the application.
Would it be 'logically' correct to put a waitfor delay in the first statement of the trigger hoping that the other app. triggers and constraints have all been resolved?
Any other inputs would be greatly appreciated!
Thanks a bunch, Leo
August 22, 2002 at 8:15 am
You don't want to put on a WAITFOR or anything of that sort. The issue is the locking and blocking. Find out what's causing them and work to resolve them while ensuring your data integrity. The WAITFOR would just be a smokescreen.
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
August 22, 2002 at 10:07 am
do you know how to interpret the 'resource' column from the sp_lock output?
August 11, 2004 at 1:38 pm
Hi--
In order to use cascading referential integrity, do I have to rewrite my tables/keys?
Thanks,
Luiz
June 26, 2005 at 7:59 pm
I found another significant difference in trigger behaviour, which is not strictly due to different trigger implementation, but the way transactions are counted.
If @@trancount is used to find out if the calling transaction is part of a larger trigger-initiated cascade transaction then one has to be aware that SQL Svr 7 counts transactions differently to SQL Svr 2000.
Cheers,
Win
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply