Trigger failing

Question

Post reply

Trigger failing

Viewing 7 posts - 16 through 21 (of 21 total)

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

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 1

GilaMonster,

I have inserted a row into the transaction table and all it says is :(1 row(s) affected)

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

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.

But how do you know the insert process won't change, or that some support person won't provide a data fix that inserts several rows in one statement?

But if this trigger won't work, do you have any suggestions of what I could do instead of creating a trigger?

I didn't say not to use a trigger (provided it doesn't invalidate your support agreement). I just pointed out that the way you have coded this one, it won't work. There are two things you need to do. First, include a check that the Case_ID being inserted doesn't already exist in BUDGET_LINE. Second, use the Inserted virtual table to insert all inserted rows into BUDGET_LINE. I don't know whether Inserted is available to the stored procedure, so you may have to put the code directly in the trigger definition.

John

Edit - fixed quotes

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 3

John,

There will be more than one case id in the budget_line table because you can have multiple transaction for each case.

And for the second part, will I then select from inserted rather than all the physical tables?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

John Mitchell-245523 (6/12/2014)
I don't know whether Inserted is available to the stored procedure

It's not. Only directly in the trigger.

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

No, only the stuff you would have got from cat_transaction.

John

delizat Right there with Babe Points: 749 More actions · Answer 6

Does the caller have permissions on both databases? Try adding a with execute to the trigger and see if it runs.

CREATE PROCEDURE dbo.usp_Demo

WITH EXECUTE AS 'SqlUserWithPermissionsInBothDBs'

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 7

Hi all,

The insert procedure wont change and they won't provide a fix.

The reason the asked me to get the data back into our DB is because their developers can't.(Don't know why)

Yes I have permissions on both DB's