Page 1 of 1

Triggers aren't easily configurable.

Posted: Tue Jul 24, 2007 2:52 pm
by Todd_Z
This can be best expressed through an example:

I have a table called galleries, and a table called view_galleries.

galleries has fields such as: user_id, etc.

In view_galleries, I want to show the user's actual name, and, for example, the number of files associated with this gallery.

In doing so, I can do rather quick sorts/queries, etc, without complicated joins, and executing queries that take forever.

Anyways, currently, I have a trigger on each table that will update its respective "view" table. The triggers are getting very ugly / difficult to maintain.

I would like to execute a snippet of php code to do the work for me, however it looks that triggering a php script from mysql triggers would involve a C function, which doesn't look like fun to implement.

My Initial Idea:
Every time something is done to a table, for example, DELETE, UPDATE, a trigger is put into a "triggers" table.
The triggers table would store the table, the event, and the id of the row.
A cronjob would then run every X minutes to deal with the triggers.

This solution works... but it won't instantaneously update the view tables. Is there a solution to deal with this issue?

Thanks

Posted: Tue Jul 24, 2007 4:54 pm
by feyd
What needs to be done that's specific to PHP that the database won't do for you?

Posted: Tue Jul 24, 2007 5:40 pm
by Todd_Z
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


The following is an example of the triggers I made for the tables, and as you can see... its painfully difficult to maintain / update.

[syntax="sql"]

TRUNCATE dt_advertisement|
INSERT INTO dt_advertisement SELECT ( SELECT address FROM dt_property WHERE id = property_id ) AS property, 1 AS count, prope
rty_id, (select id from gallery where gallery.property_id = property_id limit 1) AS gallery_id, now() AS last_modified, ( SEL
ECT full_name FROM user WHERE id = advertisement.user_id ) AS user, user_id, ( SELECT full_name FROM user WHERE id = ( SELECT
 user_id FROM user WHERE user.id = advertisement.user_id ) ) AS manager, ( SELECT title FROM ad_folder WHERE id = (SELECT ad_
folder_id FROM ad_background WHERE id = ad_background_id ) ) AS folder, id FROM advertisement WHERE status = 'Active'|

DROP TRIGGER advertisement_insert|
CREATE TRIGGER advertisement_insert AFTER INSERT ON advertisement
FOR EACH ROW BEGIN
INSERT INTO dt_advertisement SELECT ( SELECT address FROM dt_property WHERE id = property_id ) AS property, 1 AS count, prope
rty_id, (select id from gallery where gallery.property_id = property_id limit 1) AS gallery_id, now() AS last_modified, ( SEL
ECT full_name FROM user WHERE id = advertisement.user_id ) AS user, user_id, ( SELECT full_name FROM user WHERE id = ( SELECT
 user_id FROM user WHERE user.id = advertisement.user_id ) ) AS manager, ( SELECT title FROM ad_folder WHERE id = (SELECT ad_
folder_id FROM ad_background WHERE id = ad_background_id ) ) AS folder, id FROM advertisement WHERE id = NEW.id;
END;|

DROP TRIGGER advertisement_update|
CREATE TRIGGER advertisement_update AFTER UPDATE ON advertisement
FOR EACH ROW BEGIN
delete FROM dt_advertisement WHERE dt_advertisement.id = NEW.id;
IF NEW.status = 'Active' THEN
INSERT INTO dt_advertisement SELECT ( SELECT address FROM dt_property WHERE id = property_id ) AS property, 1 AS count, prope
rty_id, (select id from gallery where gallery.property_id = property_id limit 1) AS gallery_id, now() AS last_modified, ( SEL
ECT full_name FROM user WHERE id = advertisement.user_id ) AS user, user_id, ( SELECT full_name FROM user WHERE id = ( SELECT
 user_id FROM user WHERE user.id = advertisement.user_id ) ) AS manager, ( SELECT title FROM ad_folder WHERE id = (SELECT ad_
folder_id FROM ad_background WHERE id = ad_background_id ) ) AS folder, id FROM advertisement WHERE id = NEW.id;
END IF;
END;|

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Tue Jul 24, 2007 6:02 pm
by programmingjeff
Forgive me, I'm not sure I fully understand your scenario.
I would like to execute a snippet of php code to do the work for me, however it looks that triggering a php script from mysql triggers would involve a C function, which doesn't look like fun to implement.
Is the entire application PHP-based? If so, wouldn't the PHP script be the one that initially modifies the database (which then fires the trigger)? In that case, you could just include your code right after the query statement in the PHP.

Maybe there's something I'm missing?

Posted: Tue Jul 24, 2007 6:12 pm
by Todd_Z
Theoretically, you are correct.

However, I would like to keep these two parts of the system abstracted.

When I have a dedicated server for the views as opposed to the actual data, then I don't want to have to change all the junk in the code. I can just alter the other script that syncs the views table.


My Temporary (possibly not temporary) Solution:

Created a daemon that runs all the time, sleeping every 10 seconds that checks the trigger table for entries, and deals with them accordingly. I then have a cronjob running every 15 mins to make sure the cronjob is running, if not, it starts it.

It works, we'll see if i come up with something better.