We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

problem in trigger

whspider
whspider asked
on
Medium Priority
232 Views
Last Modified: 2012-05-07
hi

am using xampp.....working in php and mysql...

i created the following trigger

CREATE TRIGGER dte
before INSERT ON temp_billing
FOR EACH
ROW
BEGIN
SET NEW.t_dt = new.t_updtime;
END


temp_billing table has the following attributes....

test_id        mediumint
product      varchar(40)
t_dt             datetime
t_updtime    timestamp

the trigger query is working fine when i insert manually through phpmyadmin

but when i insert through php 0000-00-00 00:00:00 is getting stored in t_dt.......


my insert query is


$sql="insert into temp_billing(testid,product,t_dt) values ('$id','$prod','0000-00-00')";
$result=mysql_query($sql);


pls help
Comment
Watch Question

Commented:
CREATE TRIGGER dte
before INSERT ON temp_billing
FOR EACH
ROW
BEGIN
SET NEW.t_dt = NOW();
END

Author

Commented:
will this now() value differ from current time stamp value.....
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Top Expert 2005

Commented:
The reason it works in phpMyAdmin and not through your own script is that phpMyAdmin fills in the values for anything you don't edit, so the query it sends is something like this:

INSERT INTO temp_billing(testid, product, t_dt, t_updtime) VALUES ('$id', '$prod', '0000-00-00', CURRENT_TIMESTAMP)

If you did the same, then your original trigger would work. However, using racek's version instead allows you to be lazy and still have it work like you expect.

Commented:
Mz version make logic hidden in the trigger - it can be another advantage :-)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.