Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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