Skip to Content
avatar image
Former Member

CREATE TRIGGER without disconnecting users

I'd like to alter a Trigger on SQL Anywhere 16. Unfortunately there are users on my DB all the time and I would like to avoid kicking them off the server just for changing the Trigger.

Sybase Central does not save changes. It says that the table is locked. If I try to alter the trigger via Interactive SQL the command runs forever(until i cancelled execution)

Is there a way to make changes to the Trigger without disconnecting all the users? Can I force an exclusive lock?

A friend (using MSSQL) told me to do it via a transaction and to delete/recreate the trigger but I don't know how to do it on SQL anywhere.

Thanks for your support.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 17, 2017 at 12:39 PM

    Man, I had just written a long answer and submitted it, and now it seems gone...

    FWIW, here's a similar question from the working SQL Anywhere Forum, dealing with ALTER TABLE – and I'm quite sure an ALTER TRIGGER requires the same exclusive schema lock on the according table. So the answers there should fit for you, as well.

    Shared Schema Locks Preventing Alter Table

    ,

    Well, ALTER TRIGGER seems to require an exclusive schema lock on the according table, like ALTER TABLE does.

    Therefore, you cannot alter a trigger on a table while any other transaction is using that table, i.e. there must not be any other open transaction accessing that table - even for a SELECT with isolation level 0 (read uncommitted). Until then, the connection doing the ALTER TRIGGER will be blocked.

    ----

    Note, ALTER TRIGGER does an automatic commit, so there's no use in trying to put that in a separate transaction, the automatic commit will end the current transaction by design. (See, you could use the LOCK TABLE ... IN EXCLUSIVE MODE statement to block access to that table by other transactions, however, that will only suffice if the ALTER TRIGGER would be successful, too, so I don't think this will help you further.

    ----

    That being said, you do not necessarily need to disconnect users but you need to make sure there's a chance that the ALTER TRIGGER connection will be able to get its required lock, even when that will take considerable time.

    Add comment
    10|10000 characters needed characters exceeded