MySQL Forums
Forum List  »  Triggers

Re: How to alter a trigger atomicly?
Posted by: Sorin Stoiana
Date: April 06, 2007 03:03AM

The problem was altering the trigger atomicly.

I've tried the following:

1. Lock the table, drop trigger, create trigger with new content, release lock.
LOCK TABLES `test` WRITE;
DROP TRIGGER test_trig;

At this point, the thread just hangs. See bug report http://bugs.mysql.com/bug.php?id=23713 .

2. Calling a stored procedure from the trigger, and altering the stored procedure. This method has problems with locking the `test` table, because you can't hold a WRITE lock on mysql.proc along with locks on other tables.

3. GET_LOCK() function seems to have some problems too. I haven't quite put my finger on what the problem is, because of the complexity of the app I'm working on right now. However, implementing a locking scheme with GET_LOCK() might work for you.

4. Right now, I'm messing around with IPC semaphores in PHP ...


Can anyone suggest a sollution? My app has a constant stream writes on the `test` table, and I have to update the trigger on the fly, without missing any INSERT/UPDATE/DELETE on the table.

Options: ReplyQuote


Subject
Views
Written By
Posted
15109
March 19, 2007 09:53PM
Re: How to alter a trigger atomicly?
5736
April 06, 2007 03:03AM
4610
April 19, 2007 02:56AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.