January 20, 2014 at 6:17 am
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?
January 20, 2014 at 6:21 am
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
January 20, 2014 at 6:27 am
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.
January 20, 2014 at 6:53 am
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
January 28, 2014 at 6:45 am
The stored procedure works, because if I execute it outside the trigger, it works.
Maybe my syntax is wrong for the trigger or something
January 28, 2014 at 7:25 am
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
January 28, 2014 at 8:28 am
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?
January 28, 2014 at 11:38 pm
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
January 29, 2014 at 12:49 am
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
January 29, 2014 at 12:55 am
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
January 29, 2014 at 1:46 am
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
January 29, 2014 at 5:54 am
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
January 29, 2014 at 6:14 am
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?
January 29, 2014 at 6:24 am
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.
January 29, 2014 at 6:31 am
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