Host wont allow triggers HELP!!!

[Rabastan]

I have a todo list on my website where I have a field to time stamp when the item was created and one for when the item was completed I planned on using this statement to make that happen.

 

CREATE TRIGGER todo_add BEFORE INSERT ON `todo`
FOR EACH ROW SET NEW.date_time = NOW(), NEW.complete_date_time = '0000-00-00 00:00:00';


CREATE TRIGGER todo_complete BEFORE UPDATE ON `todo`
FOR EACH ROW SET NEW.complete_date_time = NOW(), NEW.date_time = OLD.date_time;

 

I have used it before in the past, however is seems this host wont allow "Triggers" in mysql. My question is how can I do this same thing in MySql.

 

Rab

[mikosiko]

depending of your host's Mysql version you will need to be granted with a GRANT TRIGGER (MYSQL 5.1.6 and up) or GRANT SUPER (for previous versions). If your host's Mysql version is prior a 5.1.6 that could be the reason for them to do not allow it.

 

however... you can replace those 2 simple triggers just including the functionality (the values for your fields) directly on your INSERT or UPDATE sentences

[Rabastan]

I dont understand what your saying I should do

 

Rab

[mikosiko]

which part you don't understand?... the GRANT part or the other?

 

anyway... for the other part you can do a simple test....

1) with whatever tool that you are using (phpmyadmin?) run this script:

DROP TABLE IF EXISTS `testdate`;
CREATE TABLE  `testdate` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `other` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 

2) after the test table has been created run this sentence:

INSERT INTO testdate (date_created, other) VALUES (NOW(), 'this is anything');

 

and then check the inserted value in the table with:

SELECT * from testdate;

and check what happens with the value on the field date_updated

 

wait some time... and run this sentence now

UPDATE testdate SET other = 'what happens now';

 

check the values again with the previous SELECT... and look for what happen to the value of your field date_updated

 

more clear now?

[Rabastan]

Darn, didnt work. I was hoping 

 

It Created the original date in the Updated area and Didnt update either.

 

Rab

[mikosiko]

Huh?... don't follow.... the provided example does work... post exactly what you wrote and the results

[Rabastan]

Ok if i use the insert query you wrote it works, it sets both dates the same on insert, and changes updated after update. But if I use PHPmyadmin's insert function it doesnt work

 

Rab

[mikosiko]

Ok if i use the insert query you wrote it works, it sets both dates the same on insert, and changes updated after update.

 

Rab

 

and that is exactly what you were asking for... so the example and solution works.

But if I use PHPmyadmin's insert function it doesnt work

 

Rab

 

because we don't know how are you trying to do the same in PhpMyadmin is not possible to answer why "is not working" for you.... I just tested PhpMyAdmin on my side and it does work perfectly too, therefore most likely you are no doing the right thing...