locked
insert trigger RRS feed

  • Question

  • i have the following trigger

    ALTER

     

    TRIGGER [dbo].[trig_insertInvoiceNumber]

    ON

     

    [dbo].[Billing]

    After

     

    insert

    AS

    SET

     

    NOCOUNT ON

     

    DECLARE

     

    @delTicketNo CHAR(15)

    DECLARE

     

    @invoiceNumber CHAR(15)

    DECLARE

     

    @orderNumber CHAR(15)

    SELECT

     

    @invoiceNumber = (SELECT InvoiceNo FROM Inserted)

    SELECT

     

    @delTicketNo = (SELECT DelTicketNo FROM Inserted)

    SELECT

     

    @orderNumber = (SELECT OrderNumber FROM dbo.tSAL_Order where OrderNumber = @delTicketNo)

     

     

    IF (@delTicketNo = @orderNumber)

    BEGIN

     

    UPDATE dbo.tSAL_Order

     

    SET E2InvoiceNumber = @invoiceNumber

     

    where OrderNumber = @delTicketNo

    END

    The @delTicketNo is returning null even though the DelTicketNo column is populated on insert.....if i set the trigger to fire on 'update' it works just fine. ***The problem i have though with this is that i have a process that is run at the end of the month from 3rd party software that updates a column on the same table(for multiple records) i am putting the trigger and if the trigger is set to fire on update. This process is timing out.

     

     
    Wednesday, December 8, 2010 7:08 PM

Answers

  • Hello,

    From logical side your trigger logic is wrong. The virtual table can contain 1-n records, but with your SELECT @var = (SELECT x FROM inserted) you get only one value.
    You should rewrite your trigger; at all it looks like you could solve it with a simple update statement:

    ALTER TRIGGER [dbo].[trig_insertInvoiceNumber]

    ON [dbo].[Billing] After insert

    AS

     

        SET NOCOUNT ON

        UPDATE O

        SET E2InvoiceNumber = I.InvoiceNumber

        FROM inserted AS I

             INNER JOIN

             tSAL_Order AS O

                 ON I.DelTicketNo = O.OrderNumber

     

    END

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Proposed as answer by Razvan Socol Thursday, December 9, 2010 9:30 AM
    • Marked as answer by Kalman Toth Wednesday, December 15, 2010 3:12 PM
    Thursday, December 9, 2010 7:59 AM

All replies

  • Hi Try this one please:

    Question, have you tried insert records manually into table Billing ?

    If not, do it, the problem seems to be at the 3rd party software.

     

    ALTER TRIGGER [dbo].[trig_insertInvoiceNumber]
    ON
    [dbo].[Billing]
    After insert
    AS
    SET NOCOUNT ON
    
    DECLARE @delTicketNo CHAR(15)
    ,		@invoiceNumber CHAR(15)
    ,		@orderNumber CHAR(15)
    
    select * into #i from inserted;
    
    SELECT	@invoiceNumber = InvoiceNo
    ,		@delTicketNo = DelTicketNo
    from	#i
    
    SELECT @orderNumber = OrderNumber 
    FROM	tSAL_Order 
    where	OrderNumber = @delTicketNo
    
    IF (@delTicketNo = @orderNumber)
    BEGIN
    
    	UPDATE dbo.tSAL_Order
    	  SET E2InvoiceNumber = @invoiceNumber
    	where OrderNumber = @delTicketNo
    
    END
    


    karepa
    Thursday, December 9, 2010 12:11 AM
  • Hello,

    From logical side your trigger logic is wrong. The virtual table can contain 1-n records, but with your SELECT @var = (SELECT x FROM inserted) you get only one value.
    You should rewrite your trigger; at all it looks like you could solve it with a simple update statement:

    ALTER TRIGGER [dbo].[trig_insertInvoiceNumber]

    ON [dbo].[Billing] After insert

    AS

     

        SET NOCOUNT ON

        UPDATE O

        SET E2InvoiceNumber = I.InvoiceNumber

        FROM inserted AS I

             INNER JOIN

             tSAL_Order AS O

                 ON I.DelTicketNo = O.OrderNumber

     

    END

     


    Olaf Helper ----------- * cogito ergo sum * errare humanum est * quote erat demonstrandum * Wenn ich denke, ist das ein Fehler und das beweise ich täglich http://olafhelper.over-blog.de
    • Proposed as answer by Razvan Socol Thursday, December 9, 2010 9:30 AM
    • Marked as answer by Kalman Toth Wednesday, December 15, 2010 3:12 PM
    Thursday, December 9, 2010 7:59 AM