Triggers aren't easily configurable.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Triggers aren't easily configurable.

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What needs to be done that's specific to PHP that the database won't do for you?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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]
programmingjeff
Forum Commoner
Posts: 26
Joined: Fri Jan 05, 2007 10:56 am

Post 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?
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post 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.
Post Reply