0

I am working with a system (I am not the developer) that uses a half dozen databases, all on the same Sybase ASE 15.7 server.

The two important databases I need to access are TRANSACTIONS and CONFIGURATION. Both are updated in real-time, and only in real-time can I capture the data I want. Here's a super-simplified example of my trigger:

use TRANSACTIONS
go
CREATE TRIGGER myTrigger
ON UserLog
FOR INSERT
AS 
BEGIN
--
-- do stuff here within the TRANSACTION database, no problems.
-- .......

-- Now, try to access the CONFIGURATION database, and boom, it fails
INSERT INTO myLog (somedata)
SELECT userData 
FROM CONFIGURATION.dbo.UserConfiguration
WHERE ...blah...

END

My trigger always fails when a table is referenced from outside of the TRANSACTIONS database, not at any other time.

BTW, I'm logged in as systems admin when I create the trigger.

As noted in the Sybase documentation, triggers are part of transactions and if a serious error occurs in a trigger, the transaction is rolled back. This is exactly what I see occurring.

I suspect that the error is due to one of three possible causes:

  1. security grants for tables in the 'other' database some kind
  2. security-limitation configured at the time the database was built
  3. a Sybase restriction that cannot be overcome

Regarding #1, I've granted SELECT to PUBLIC and 'dbo' on the UserConfiguration table. DBO is the user who is creating the UserLog in TRANSACTIONS. But I'm not sure that my grant is applying correctly since every user is scoped to a specific database, not a global user.

Regarding #2, I can't figure out if there are any possible limitations that could be configured in, and not overcome.

Regarding #3, Sybase documentation states that my trigger SHOULD be able to reference tables in other databases.

Lastly, since I cannot see the error being raised or trap it, I have almost nothing to go on to tackle this problem. I don't want to insert my own records into the monitored table because it's not my table. I've hunted around for some kind of error log but I don't see any such thing. Is there one? I've researched trying to set the equivalent of TRY-CATCH blocks, blocks that would prevent an error from bubbling up, but Sybase appears to not have such controls.

Does anyone have any ideas how I might progress forward here?

My trigger is going to populate a table of my own in real-time and only needs to SELECT from the 'inserted' object, and tables in two databases.

Robert H. Bourdeau
  • 131
  • 6
  • Look at sp_addmessage in comination with raiserror to set your own errors to catch. http://stackoverflow.com/questions/23631224/sybase-is-it-possible-to-set-error/23631781#23631781 – Mike Gardner Jul 13 '15 at 19:34
  • Cool idea Michael. I'll have to contemplate that to see how it might move me forward. My code is not the caller that causes the trigger to fire, it's a vendor's application. My hope is to generate some additional ancillary data as a result of the vendor application performing an insert, and to store that data into a table I control. I can install the trigger , but as I mentioned, an error is raised in my code that only occurs when executed as a trigger. And when that error is raised, the transaction is rolled back and no trace remains. I'd like to PREVENT the rollback and identify the error. – Robert H. Bourdeau Jul 13 '15 at 19:47

0 Answers0