Trigger: To fill another Database with using Stored Procedures of the other Database

Hello everyone,

I face currently a problem where I could need some input for searching
the source of the Problem

System: SQL Server 9.0

I fill from Database A with triggers Database B, everything works
fine.
On Database B there is a Stored Procedures that checks the records and
add additional information accordingly, this Stored Procedures is
normally called by the application on "update and insert" in the
according table.

When I try to call this Stored Procedures from the Database A, the
trigger does not work anymore, even if I do a try catch over the whole
trigger, he never reach the Catch and the insert I try to do there to
get the error message.

On both Databases the user, that is taken to execute the trigger is
existent and DB-Owner of both Databases.

If I go and execute the Stored Procedures manually after an insert or
update to Database B everything works fine.

I also already tried to check on Database B if there is an insert or
update from Database A and if, to execute the Stored Procedures, with
the same result, nothing and all happens anymore, neither update on
Database A and also not on Database B.
And also I cant catch the error as the Try/Catch is not working.

Hope I could explain it understandable and maybe someone remembers
already having the same problem.

Thanks & Best regards
Pascal

Re: Trigger: To fill another Database with using Stored Procedures of the other Database

(pascal.baetscher@gmail.com) writes:
> I face currently a problem where I could need some input for searching
> the source of the Problem
>
> System: SQL Server 9.0
>
> I fill from Database A with triggers Database B, everything works
> fine.
> On Database B there is a Stored Procedures that checks the records and
> add additional information accordingly, this Stored Procedures is
> normally called by the application on "update and insert" in the
> according table.
>
> When I try to call this Stored Procedures from the Database A, the
> trigger does not work anymore, even if I do a try catch over the whole
> trigger, he never reach the Catch and the insert I try to do there to
> get the error message.

So there is a an error message? What about posting it?

Overall, if it's possible, I would appreciate if you could post the
code of the stored procedure and the trigger, because I have some
problems following your narrative what is happening.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Trigger: To fill another Database with using Stored Procedures of the other Database

Hello,

Thanks for your answer and I just noticed that I double posted this
message.

First I was not able to catch the errormessage, I started the triggers
over the application and in the expected return value was nothing,
after change the Trigger and lunch with the console, i had the
following error:
Msg 3616, Level 16, State 1
Transaction doomed in trigger. Batch has been aborted.

I already found the problem and was able to solve it, sorry for the
late reply.

And thanks for the input, next time I'll extract necessary parts of
the triggers.