Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Unanswered: Stored Procedure Problem with triger

    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
    Last edited by helkayal; 02-14-05 at 09:54.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Post the code for your stored procedure.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2005
    Posts
    2

    the code for the stord procedure

    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. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would start by doing some error handling


    And you've got a trigger generating an xml file?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Dec 2004
    Location
    Sweden
    Posts
    74
    Perhaps I'm totally out of line here because I must admit that I didn't have time to study your post as much as I'd like to, but could your problem be that you're doing a single insert with many rows and that this only triggers your procedure once?
    If this is the case, you should probably loop the inserted table your trigger in order to do the procedurecall once per post in the virtual inserted-table.
    If I were you, I'd try to avoid it tho, since it looks like the procedure logic could be incorporated into the trigger instead, thus eliminating atleast one step.
    The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •