Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

problem in trigger

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
0
whspider
Asked:
whspider
  • 3
1 Solution
 
racekCommented:
CREATE TRIGGER dte
before INSERT ON temp_billing
FOR EACH
ROW
BEGIN
SET NEW.t_dt = NOW();
END
0
 
whspiderAuthor Commented:
will this now() value differ from current time stamp value.....
0
 
racekCommented:
no, because t_updtime  is  timestamp. If you want to be sure, use

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

END
0
 
snoyes_jwCommented:
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.
0
 
racekCommented:
Mz version make logic hidden in the trigger - it can be another advantage :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now