Re: [PHP-DB] Re: backup of database record before update
Leo G. Divinagracia III wrote: Stephen Sunderlin wrote: Neil, Have you had success with triggers. I couldn't get one to work and then saw discussion on the board here that MYSQL triggers were not so reliable and still somewhat problematic so I archive through the application. Just curious. i have used a couple simple triggers on a 5.0.20nt box. one thing i read recently is triggers and stored procedures do use more system resources though... Why depend on different MySql infrastructures, while you can easily implement a perfect fit for your needs in a PHP routine? ... -- Kind regards, hochprior -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: backup of database record before update
At 09:46 01/11/2007, you wrote: Message-ID: [EMAIL PROTECTED] Date: Thu, 1 Nov 2007 10:45:57 +0100 Leo G. Divinagracia III wrote: Stephen Sunderlin wrote: Neil, Have you had success with triggers. I couldn't get one to work and then saw discussion ... ...snip... ... on a 5.0.20nt box. one thing i read recently is triggers and stored procedures do use more system resources though... Why depend on different MySql infrastructures, while you can easily implement a perfect fit for your needs in a PHP routine? Because it's the job of the database to implement data access, referential integrity and storage. It's the job of the PHP code to manage business and application logic, and some data sanitisation. Finally, it's the job of the templating engine / output layer to manage actual display of content. I tend to try to move as much as possible to the Database, because it's often running on a separate machine or cluster. That frees up resources on the web servers to get and send data as quickly as possible, rather than have it spending all day constructing SQL strings and managing stuff the great engineers at MySQL have already invented to make my life simpler ;-) TBH it *oughtta* be much faster than having PHP do that, since (a) you're saving the roundtrip of the query to the DB from the web server and (b) the trigger query will probably end up quite rapidly in MySQL query cache on the DB in most cases. Cheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: backup of database record before update
Neil Smith [MVP, Digital media] wrote: At 09:46 01/11/2007, you wrote: Message-ID: [EMAIL PROTECTED] Date: Thu, 1 Nov 2007 10:45:57 +0100 Leo G. Divinagracia III wrote: Stephen Sunderlin wrote: Neil, Have you had success with triggers. I couldn't get one to work and then saw discussion ... ...snip... ... on a 5.0.20nt box. one thing i read recently is triggers and stored procedures do use more system resources though... Why depend on different MySql infrastructures, while you can easily implement a perfect fit for your needs in a PHP routine? Because it's the job of the database to implement data access, referential integrity and storage. It's the job of the PHP code to manage business and application logic, and some data sanitisation. Finally, it's the job of the templating engine / output layer to manage actual display of content. I tend to try to move as much as possible to the Database, because it's often running on a separate machine or cluster. That frees up resources on the web servers to get and send data as quickly as possible, rather than have it spending all day constructing SQL strings and managing stuff the great engineers at MySQL have already invented to make my life simpler ;-) TBH it *oughtta* be much faster than having PHP do that, since (a) you're saving the roundtrip of the query to the DB from the web server and (b) the trigger query will probably end up quite rapidly in MySQL query cache on the DB in most cases. And still you would have to rely on one more infrastructure. Just do it the way you like, and I´ll do it my way... ;-) -- Kind regards, hochprior -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: backup of database record before update
Stephen Sunderlin wrote: Neil, Have you had success with triggers. I couldn't get one to work and then saw discussion on the board here that MYSQL triggers were not so reliable and still somewhat problematic so I archive through the application. Just curious. i have used a couple simple triggers on a 5.0.20nt box. one thing i read recently is triggers and stored procedures do use more system resources though... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Re: backup of database record before update
Neil, Have you had success with triggers. I couldn't get one to work and then saw discussion on the board here that MYSQL triggers were not so reliable and still somewhat problematic so I archive through the application. Just curious. -Original Message- From: Neil Smith [MVP, Digital media] [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 30, 2007 3:16 PM To: php-db@lists.php.net Cc: Ronald Wiplinger Subject: [PHP-DB] Re: backup of database record before update At 06:57 28/10/2007, you wrote: Message-ID: [EMAIL PROTECTED] Date: Sun, 28 Oct 2007 14:56:12 +0800 From: Ronald Wiplinger [EMAIL PROTECTED] I need some hints how to do that. Maybe there is a template or an easy function available. I have a MySQL database with some tables. Everytime a table record changes, I want also put the old record to a history database. The history table and the original only differs that the key of the original will be just a field in the backup, while the new key is now the UNIX time stamp. How can I do that easy? Easy is to set a MySQL5 trigger which does the Insert on update : http://dev.mysql.com/doc/refman/5.0/en/triggers.html eg (from the manual page) : DELIMITER | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END; | HTHCheers - Neil -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
