Trigger

  • Hi,

    Can anyone please help my with the following trigger.

    ALTER TRIGGER [dbo].[PAT_2_AJS]

    ON [dbo].[INVOICE_HEADER]

    FOR INSERT

    AS

    BEGIN

    EXECUTE sp_PAT_2_AJS_TEST

    END

    GO

    When I execute the trigger it says command successful and when I execute Just the one line - EXECUTE sp_PAT_2_AJS_TEST it works,

    but when I insert data through the front end the trigger doesn't work.

    When I use the code in the SP instead of execute sp_PAT_2_AJS_TEST it also works..

    Why is this?

  • Define 'doesn't work'?

    What does that procedure do? What's the trigger supposed to do? Why is the trigger not referencing inserted or deleted pseudo-tables at all?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The SP inserts data into a table in another database.

    The trigger should execute this stored procedure as soon as new data is inserted into the table.

  • Ok, and...?

    With the trigger as you have posted it, any insert will fire that trigger and hence will run that procedure. Whether it works as intended is another matter, but I can't tell that without knowing what the procedure does (specifics, not a one-line description) and what it's intended to do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The stored procedure works, because if I execute it outside the trigger, it works.

    Maybe my syntax is wrong for the trigger or something

  • crazy_new (1/28/2014)


    The stored procedure works, because if I execute it outside the trigger, it works.

    Maybe my syntax is wrong for the trigger or something

    apparently, the stored procedure finds the data it's supposed to touch all by itself, since it has no parameters.

    most likely, whatever code is inside that procedure should exist inside the trigger INSTEAD, and it should reference the INSERTED and DELETED virtual tables inside the trigger, as Gail suggested.

    unless you post the actual code for your proc, we cannot help you.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (1/28/2014)


    crazy_new (1/28/2014)


    The stored procedure works, because if I execute it outside the trigger, it works.

    Maybe my syntax is wrong for the trigger or something

    apparently, the stored procedure finds the data it's supposed to touch all by itself, since it has no parameters.

    most likely, whatever code is inside that procedure should exist inside the trigger INSTEAD, and it should reference the INSERTED and DELETED virtual tables inside the trigger, as Gail suggested.

    unless you post the actual code for your proc, we cannot help you.

    +1

    apparently, the stored procedure finds the data it's supposed to touch all by itself, since it has no parameters.

    that is an interesting reaction related to new data.

    You inserted a record, do something.

    Insert a Date Time Stamp in the other table?

  • yes it does find all the data by itself, if I take the code and ut it into the trigger it works...but I need this trigger to execute a couple of stored procedures

  • Can you post the stored proc code please? Without seeing that, there's not a chance in hell any of us are going to be able to guess why the trigger 'doesn't work'.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • BEGIN

    INSERT INTO PAT_AJS.dbo.AJS_Client_Invoices

    SELECT IL.INVOICE_ID, IH.Invocie_DATE,

    IH.Case_ID,IH.Actor_ID,IL.Invoice_Line_Text ,IL.Invoice_Amount,B_L_SEQ_NUMBER,B_L_AMOUNT,BL.WORK_CODE_ID

    FROM INVOICE_LINE IL, INVOICE_HEADER IH, BUDGET_LINE BL

    WHERE IH.Invoice_ID = IL.Invoice_ID

    AND IL.INVOICE_ID = (SELECT MAX(invoice_id)

    FROM INVOICE_HEADER

    WHERE INVOICE_ID > 9990000

    AND INVOICE_ID LIKE '1%')

    AND B_L_INVOICE_NUMBER = IH.INVOICE_ID

    END

  • Ok, so every single time someone inserts one or more rows into InvoiceHeader, you want the one single highest invoice ID to be inserted into Client Invoices, regardless of how many rows were just inserted into InvoiceHeader, regardless of whether the row that is inserted into Client Invoices is in any way related to the ones just inserted into invoiceHeader, as long as it starts with a 1?

    Sure about that?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • looks like bad logic to me.

    your procedure finds the max id that has a matching criteria, so it assumes a single record was ever inserted.

    if i insert multiple records into your invoice header, only the "last" one (assuming an identity column is used) might be inserted.

    worse, i assume the INVOICE_LINE table has a foreign key to the header table, and that data is probably not inserted yet, since the trigger is on that table; in that event, it's probably an already existing max() record that gets re-inserted into the remote table.

    without the full details of how the data is actually getting inserted, this is only an educated guess, but i'd bet lunch on it that the whole process needs a redesign due to teh hierarchy involved.

    as Gail already mentioned, a trigger should be using the virtual tables INSERTED and DELETED to find the records and be able to handle multiple rows being affected, and yours is not even close to using that yet.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Similar to Lowell and Gail, just worded a bit differently.

    Do Invoice Lines exist before the Invoice Header is inserted?

    Is there ever more than 1 Invoice Line per Invoice Header?

    Same question for Budget Line.

    Why would you not join Invoice Header and Invoice line ON Invoice_ID directly?

  • There are multiple line in the Invoice line table with the same ID, that's why I joined the Invoice ID and Invoice header.

    I have to move them across from the one table to the other as soon as they are inserted(a new invoice_header_id is created), so every time a new max id comes in, it will take everything with that same ID and move it to the new table.

    So that's why I only check for the max ID.

    I'm not great with sql yet so that's how I thought it would be best.

  • crazy_new (1/29/2014)


    There are multiple line in the Invoice line table with the same ID, that's why I joined the Invoice ID and Invoice header.

    I have to move them across from the one table to the other as soon as they are inserted(a new invoice_header_id is created), so every time a new max id comes in, it will take everything with that same ID and move it to the new table.

    So that's why I only check for the max ID.

    I'm not great with sql yet so that's how I thought it would be best.

    Rather than use a trigger, my advice would be to populate the data using stored procedures that carry out the insert into the first table, then add the required details into the second set of tables

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply