June 12, 2014 at 3:02 am
Hi all,
I have a trigger on insert to fire a stored procedure.
As soon as a new transaction comes in from the front end, I want if to fire the trigger to move data to another DB.
If I execute the stored procedure manually or execute the code in it manually, it does exactly what it needs to, but as soon as I create the trigger on the table, the data doesn't pull from the front end app anymore.
I.E when I create the trigger and do a transaction, it doesn't store the transaction in the DB until I disable the trigger.
Can anyone please give me some advise.
Here is my trigger.
CREATE TRIGGER [dbo].[tr_Export]
ON
[dbo].[cat_transaction]
FOR INSERT
AS
exec dbo.sp_Export
GO
June 12, 2014 at 3:05 am
What's the definition of the procedure?
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
June 12, 2014 at 3:08 am
It works perfectly if I execute it manually.
USE eqcas
GO
DECLARE @LineNumber SMALLINT,
@CaseID SMALLINT,
@ChargeingID SMALLINT
SET @CaseID = (SELECT
CASE_ID
FROM
cat_transaction
WHERE
id = (SELECT
MAX(id)
FROM
cat_transaction
)
)
SET @LineNumber = (SELECT
MAX(B_L_SEQ_NUMBER)
FROM
Patricia.dbo.BUDGET_LINE
WHERE
CASE_ID = 121
)
SET @LineNumber= @LineNumber + 1
INSERT INTO Patricia.dbo.BUDGET_LINE
SELECT
@LineNumber,
ctt.WorkCodeID,
ctde.[pagecount],
null,
cv.name,
null,
null,
null,
ct.Case_ID,
@ChargeingID,
null,
null,
null,
null,
null,
null,
null,
null,
null,
0,
null,
null,
null,
0,
null,
null,
null,
null,
cv.name,
null,
null,
null,
null,
null,
'CH',
null,
null,
ct.creation,
ct.id
FROM
cat_transaction ct
JOIN
cat_validation cv ON cv.id = ct.chargeid
JOIN
cat_trxtype ctt ON ctt.trxtype = ct.trxtype
JOIN
cas_trx_doc_ext ctde ONctde.x_id = ct.id
WHERE
ct.id = (SELECT
MAX(id)
FROM
cat_transaction
)
June 12, 2014 at 3:29 am
crazy_new (6/12/2014)
it doesn't store the transaction in the DB until I disable the trigger.
Do you mean that the insert into cat_transaction doesn't happen? If so, it looks like there's some error in the trigger and the transaction is being rolled back. Do you see any error messages?
John
June 12, 2014 at 3:34 am
Yeah it doesn't insert into the transaction table.
There are no errors, it just doesn't insert it.
And if I disable the trigger and do another transaction, then it writes the "lost" transaction and the new transaction to the db.
June 12, 2014 at 3:41 am
The only way that could happen is if there's a rollback occurring.
If you run an insert into cat_transaction from a management studio query window? What exactly do you see in the messages?
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
June 12, 2014 at 3:43 am
And if I disable the trigger and do another transaction, then it writes the "lost" transaction and the new transaction to the db.
Are you doing your insert as part of an explicit transaction?
John
Edit - fixed quote
June 12, 2014 at 4:06 am
GilaMonster,
It says one row affected, so it does work right.
When I look in the other DB I see the record has been inserted.
John Mitchell,
I don't know how they import the data from the front end, this is a new system.
I just know they use a third party application to import it. (This is another companie's billing system)
June 12, 2014 at 4:13 am
crazy_new (6/12/2014)
GilaMonster,It says one row affected, so it does work right.
It shouldn't say one row affected. It should have more messages than that from the trigger's execution. That was with the trigger enabled?
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
June 12, 2014 at 4:16 am
It says one row affected when I execute the SP, or when I script out the transaction and just execute the code.
If I enable the trigger the transaction doesn't go through.
June 12, 2014 at 4:20 am
I don't know how they import the data from the front end, this is a new system.
I just know they use a third party application to import it. (This is another companie's billing system)
This is fairly important to know. I would advise using a trace or other techniques to find out exactly what's going on, or raise a support ticket with the third party to find out exactly how their import process works. If you have created the trigger yourself, they may tell you that it invalidates your support agreement.
By the way, that trigger is never going to work properly. If you insert more than one row at a time, only one of them will go into the second database. And if you run the stored procedure outside the trigger, you're going to get the same row inserted into the second database again and again.
John
June 12, 2014 at 4:22 am
It says one row affected when I execute the SP, or when I script out the transaction and just execute the code.
If I enable the trigger the transaction doesn't go through.
Could it be the front end app or the app they are using to import the data that "clashes" with the trigger?
June 12, 2014 at 4:24 am
Please enable the trigger and run a single-row insert into cat_transactions from Management Studio and post the exact, complete content of the messages pane afterwards.
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
June 12, 2014 at 4:26 am
John,
The reason I created this trigger is because the data will only be inserted one at a time, so if there are two records being inserted then the trigger will fire twice.
As far as the agreement with them, they could figure out how to get the data back into our system so they asked me to have a look at this.
But if this trigger won't work, do you have any suggestions of what I could do instead of creating a trigger?
June 12, 2014 at 4:30 am
OK GilaMonster I will try, don't know if it will work because there are other tables that will also need to get data in for each transaction
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply