Triggers in SQL Server 7.0 and 2000 - What's New

  • 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

  • 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

  • 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

    bk@warpdrivedesign.org

    http://www.sqlservercentral.com/columnists/bkelley/

    Edited by - bkelley on 09/10/2001 08:42:31 AM

    K. Brian Kelley
    @kbriankelley

  • 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 ?

  • The "AFTER" and "INSTEAD OF" syntax is new in SQL2000, they won't work on SQL7.

    Jay


    Jay Madren

  • 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

  • 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

  • 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

  • do you know how to interpret the 'resource' column from the sp_lock output?

  • Hi--

    In order to use cascading referential integrity, do I have to rewrite my tables/keys?

    Thanks,

    Luiz

  • 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 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply