SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS SQL Server: whats wrong with this code and how do u troubleshoot MS SQLserver

    hello,
    i am working with an update trigger. below.

    CREATE TRIGGER [PO_Agreement_update] ON [dbo].[e_invoice]
    FOR UPDATE
    AS

    if update (status)

    declare @purse varchar(50), @order2invoice varchar (5), @amt varchar (50), @user varchar(50) , @email varchar (50), @orderID varchar (50)

    select @order2invoice = (select invoice_num from inserted)
    select @user = (select usr from inserted)
    select @email = (select email from inserted)
    select @orderID = (select orderID from inserted)


    begin

    -- STEP 1: Update the INVOICE accounting table.
    insert into e_invoice_accounting (usr)
    values (@usr)

    end


    I didnt put the full code above, but only the edited code.

    However, The code fails to run. Basically its an "column level update" trigger

    i have been troubleshooting, and think the error is with the variables, where i put

    select @order2invoice = (select invoice_num from inserted)

    Because when i dont use the varaible "from inserted" the code works fine.

    (pls remb: the above is edited to shorten it)
    rgds
    Afrika


    ***PSST: How do you troubleshoot ASP/MS SQL server errors ? when the error is from the MS SQL server db.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Don't use intermediary variables as

    1) It is unnecessary and inefficient
    2) It will not work when the inserted table contains multiple (or no) rows

    Do like this instead

    Code:
    insert into e_invoice_accounting (usr)
    select usr from inserted


Bookmarks

Posting Permissions

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