Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| 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
  #1 (permalink)  
Old February 13th, 2005, 07:17 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default stored procedure problem with trigger

I write a stored prosedure that inserts into 3 tables, the first table have triger on it (generate xml file from the table) . when the procedure execute the first inserted successfuly and the trigger fires and generate the file but the secone and third insert didn't executed.
Note : if the triger removed the 3 insert statment in the stored proceure executed successfully.
I need to have this trigger and to execute the stored procedure.
Any one can help me with this problem
  #2 (permalink)  
Old February 14th, 2005, 10:21 AM
Friend of Wrox
 
Join Date: Dec 2004
Location: Chennai, Tamil nadu, India.
Posts: 307
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Vadivel Send a message via Yahoo to Vadivel
Default

Can you post the code?

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com
  #3 (permalink)  
Old February 15th, 2005, 11:36 AM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

CREATE PROCEDURE dbo.AddMember(@Username nvarchar(50),
@Password nvarchar(50),
@MemberName nvarchar(50),
@Gender int,
@BirthDate char(100),
@CountryID int,
@CityID int,
@Profession char(50),
@JobTitle char(50),
@CompanyName char(50),
@Email nvarchar(50),
@Phone char(50),
@Mobile char(50),
@Address char(50),
@MemberPhoto char(50),
@SecurityQuestion int,
@SecurityAnswer nvarchar(255),
@HearAboutUs char(50),
@Subscribed char(1))

AS

SET NOCOUNT ON

If Len(@BirthDate) = 0
set @BirthDate = NULL

If Len(@Profession) = 0
set @Profession = NULL

If Len(@JobTitle) = 0
set @JobTitle = NULL

If Len(@CompanyName) = 0
set @CompanyName = NULL

If Len(@Phone) = 0
set @Phone = NULL

If Len(@Mobile) = 0
set @Mobile = NULL

If Len(@Address) = 0
set @Address = NULL

If Len(@MemberPhoto) = 0
set @MemberPhoto = NULL

If Len(@HearAboutUs) = 0
set @HearAboutUs = NULL

If Len(@Subscribed) = 0
set @Subscribed = 0

INSERT INTO dbo.Members
(Username, Password, MemberName, Gender, BirthDate, CountryID, CityID, Profession, JobTitle, CompanyName, Email, Phone, Mobile, Address,
MemberPhoto, SecurityQuestion, SecurityAnswer, HearAboutUs)
VALUES (@Username, @Password, @MemberName, @Gender, @BirthDate, @CountryID, @CityID, @Profession, @JobTitle, @CompanyName, @Email,
@Phone, @Mobile, @Address, @MemberPhoto, @SecurityQuestion, @SecurityAnswer, @HearAboutUs)

if (@Subscribed=1)
begin
declare @MemberID int
set @MemberID = (select @@identity as MemberID)
exec JoinElsayaratMailinglist @MemberName ,@Gender ,@BirthDate ,@CountryID ,@CityID ,@Profession ,@JobTitle ,@CompanyName ,@EMail ,@Phone ,@Mobile ,@Address, @Subscribed, @MemberID
end
GO
  #4 (permalink)  
Old February 18th, 2005, 01:59 AM
Authorized User
 
Join Date: Feb 2005
Location: Pune, Maharashtra, India.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I guess you have used another insert statament in your trigger and that causes the change in @@identity value and as a result correct value is not assigned MemberId.

NOTE: The @@Identity retrives identity column value from the table on which last INSERT statement fired.

Cheers,
Pooja Falor
Pune, India
  #5 (permalink)  
Old February 22nd, 2005, 12:21 AM
Authorized User
 
Join Date: Feb 2005
Location: Sydney, NSW, Australia.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If what Pooja says is correct then use @@scope_identity

  #6 (permalink)  
Old February 22nd, 2005, 12:59 AM
Friend of Wrox
 
Join Date: Dec 2004
Location: Chennai, Tamil nadu, India.
Posts: 307
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Vadivel Send a message via Yahoo to Vadivel
Default

Just to add on: vadivel.blogspot.com/2004/01/fetching-identity-value.html

Best Regards
Vadivel

MVP ASP/ASP.NET
http://vadivel.thinkingms.com


Similar Threads
Thread Thread Starter Forum Replies Last Post
stored procedure problem keyvanjan ASP.NET 2.0 Professional 0 September 19th, 2007 06:28 AM
Stored procedure problem akumarp2p Reporting Services 1 May 29th, 2007 01:25 AM
Creating stored procedure with trigger (HOWTO ..) AzlanAziz SQL Language 0 January 16th, 2007 04:34 AM
Problem in stored procedure hkec SQL Language 1 October 6th, 2006 02:29 PM
Stored Procedure Problem brettdavis4 ASP.NET 1.0 and 1.1 Basics 7 November 3rd, 2003 09:46 PM





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