Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 26th, 2004, 02:24 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default Triggers

I'm struggling with understanding how a SP ROLLBACK will happen when a trigger fails.

If in the calling SP is wrapped in a transaction and a simple SQL call is made that envokes a trigger - but the trigger fails - will the calling object be aware that the trigger failed or do I have to build logic into the trigger to pass back to the calling SP and catch it to determine if it succeeded or not?

Also; do I have to wrap the Trigger in a BEGIN TRAN and rollback if it fails - or does the calling object take care of that if it rolls back?

I'll try to give a code sample below:

--Sample Calling Object--
Code:
Declare @rowsaffected as int

BEGIN TRAN p_accdt_d
    UPDATE accdt
       SET accdt_del_date     = '2/1/2004'
     WHERE accdt_enfact_id in (6220, 6221)
    -- there is an UPDATE trigger on the accdt_del_date column
    -- the trigger marks some other related records with the current date

    -- 1st question is if an invalid date is passed and the trigger fails
    -- on some tables, but the last update succeeds, therfore my @rowsaffected
    -- will have a value making this transaction think it succeeded

    -- 2nd question - The @rowsaffected below seems to return how many rows in 
    -- the trigger not the UPDATE above

    -- return rowcount of records updated.
    SELECT @rowsaffected = @@rowcount
    Print @rowsaffected

    IF @rowsaffected = 0
       BEGIN
          ROLLBACK TRAN p_accdt_d
       END
    ELSE
       BEGIN
          COMMIT TRAN p_accdt_d
       END
_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200
__________________
_________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200




Similar Threads
Thread Thread Starter Forum Replies Last Post
Triggers? odezzie Classic ASP Databases 1 March 28th, 2007 06:09 PM
Triggers mrookey SQL Server 2000 2 October 24th, 2006 07:11 AM
Triggers? prabodh_mishra Oracle 2 March 30th, 2006 05:51 AM
Triggers shahchi1 SQL Server 2000 1 November 1st, 2004 06:28 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.