MySQL Forums
Forum List  »  Triggers

Re: Triggers Control
Posted by: Roland Bouman
Date: August 09, 2005 02:45PM

Ok, I understand. First let me outline the view solution, then I'll explain why I think this is the better solution, and maybe it'll cheer you up :-).

Lets assume the case for just your USERS table and your four applications to make it tangible. To simplify things, I'll assume the tables are indeed similar for the four applications. So:

create table app1.users(
id int unsigned not null primary key
, name varchar(16) not null unique
...other columns
..create table statements for app2 and app3 go here
create table app4.users(
id int unsigned not null primary key
, name varchar(16) not null unique
...other columns

this would be your current situation.
Now, first we'd create a new database that will be shared between these apps:

create database app_shared;

And we create the users table there:

create table app_shared.users(
id int unsigned not null primary key
, name varchar(16) not null unique
...other columns

Now, you would have to organize a one time load to fill this table with the data from the users tables in app1 to app4. You know best what users in what apps correspond to each other, so this is up to you.

Ok, once we've made sure all the data is copied, we substitute the table for app1. (You'd have to put that application down for just a little while):

drop table app1.users;

create or replace view app1.users (
, name
select id
, name
from app_shared.users

From the perspective of the application, the view app1.users is virtually indistinguishable from the table app1.users we had earlier on.
Of course, you should always examine your application to ensure that this is indeed the case, but to tell you the truth, this is about as good as it gets.
That's because the name of the view is identical to the name we had for the table; the same goes for the columns, and their positions. (The only way that i can think of that could break an application is when the application would have some lines that specifically test that there is a 'BASE TABLE' called app1.users. You should ensure that your application does not do that, but i think that that's highly unlikely. If it did, it's probably not very well written).
We should note that this is an updatable view. This means we can issue not just SELECT's against it, but also INSERTs, UPDATEs and DELETEs. It's and updatable view because there's a straightforward relationship between a row in the view and a row in the table on which the view is based: each row in the view always corrsponds to just one row in the base table. Also, the view does not employ (it doesnt need to) any fancy aggregates, unions or joins. This makes it an updatable view.

Now, this recipe would be repeated for all your remaining tables appX.users. In the end, the fun of it all is that there's really only *ONE* instance of the data: it resides in the app_shared.users table, and that's really the only location where the data resides. The appX.users views are just that: a peephole looking at that one table.

I recommend you review the following parts of the manual if you decide to look into this solution:

Now, here's why I think this solution is so much better than the trigger solution.

When you do it with triggers, you would have to write a trigger for each application table, and for each event INSERT, UPDATE, DELETE. That's 3 triggers per table, so were at a total of 4*3 = 12 triggers. For the view solution, you need to create one view to substitute each application table, that's 4 views. And I admit, you would have to create one 'master' USERS table shared by these applications, so if we count that too, the score's now 12 CREATE TRIGGER statements versus 4 CREATE VIEW statements and 1 CREATE TABLE statement.

Suppose you would do it with triggers, each trigger would contain at least one statement to synchronize the data in *EACH* of the other tables. So, the INSERT trigger for USERS table 1 would INSERT data in USERS tables 2,3 and 4, the trigger for USERS table 2 would INSERT data in USERS table 1,3 and 4, and so on and so forth. Now this may not be a very difficult task, it is quite tedious. The view solution has no such problem at all.

Imagine a new application, app5, is built, that also needs a users table. In the trigger solution, you would of course have to write three more triggers for that new table...and you'd have to rewrite *ALL* of the 12 existing triggers, and include the appropriate operation for the USERS table in the new application. This is actually a bit due to the fact that MySQL supports only one type of trigger per event per table, but it seems to be your case. Instead of making applications independant, you are making them dependant upon each other. This one extra application being built requires we modify the databases underlying all 4 other applications, taking them down at least one moment to modify the trigger definitions.

Of course, you would still have to code around your initial problem, that is, preventing a caroussel like sequence of operations invoked by each other's operations being triggered. This problem is non-existant for the view solution, because the view solution is completely stateless and independant upon such events.

Now, I hope it cheered you up. Basically, I'm telling you that you would save work and lots of time building your solution with views, and I would not at all be surprised if that solution proves to be easier to manage in the future as well.

So, good luck, I hope it helps.

Edited 1 time(s). Last edit at 08/10/2005 02:12AM by Roland Bouman.

Options: ReplyQuote

Written By
August 09, 2005 06:36AM
August 09, 2005 09:37AM
August 09, 2005 09:56AM
August 09, 2005 01:08PM
August 09, 2005 01:14PM
August 09, 2005 01:16PM
August 09, 2005 01:20PM
August 09, 2005 01:27PM
August 09, 2005 01:40PM
August 09, 2005 01:49PM
August 09, 2005 01:58PM
Re: Triggers Control
August 09, 2005 02:45PM
August 10, 2005 02:13AM
August 09, 2005 03:09PM
August 09, 2005 07:44PM

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.