does not insert data to table by trigger
Dear Sirs,
i'm use MySQL V 5.0.45
and create trigger for insert table2 when the table1 was insert new row by
the script create table1 and table2 was show below
---------------------------------------------------------------------
CREATE TABLE `test`.`payment` (
`invno` varchar(20) collate utf8_unicode_ci NOT NULL,
`taxtableamt` decimal(12,2) default NULL,
`nontaxtableamt` decimal(12,2) default NULL,
`whtamt` decimal(12,2) default NULL,
`invoiceamt` decimal(12,2) default NULL,
`paydate` datetime default NULL,
PRIMARY KEY (`invno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `test`.`paymentbak` (
`invno` varchar(20) collate utf8_unicode_ci NOT NULL,
`taxtableamt` decimal(12,2) default NULL,
`nontaxtableamt` decimal(12,2) default NULL,
`whtamt` decimal(12,2) default NULL,
`invoiceamt` decimal(12,2) default NULL,
`paydate` datetime default NULL,
PRIMARY KEY (`invno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
---------------------------------------------------------------------
the testdata is
----------------------------------------------------------------
insert payment values ('UO0902-1145', 49160.00, 31500.00, 1474.80, 82626.40, '2009-04-03 00:00:00'),
('UO0902-1147', 61425.00, 24100.00, 1842.75, 87982.00, '2009-04-03 00:00:00');
------------------------------------------------------------------
and then create trigger by
---------------------------------------------
delimiter |
create trigger shdinst before insert on payment
for each row begin
insert into paymentbak values (`invno`= new.`invno`,
`taxtableamt` = new.`taxtableamt`,
`nontaxtableamt`= new.`nontaxtableamt`,
`whtamt`=new.`whtamt`,
`invoiceamt`=new.`invoiceamt`,
`paydate`= new.`paydate`);
end
|
delimiter ;
-----------------------------------------------
and when insert payment table by
-------------------------------------------------------
insert table payment values ('UO0902-1146', 450.00, 100.00, 13.50, 536.50, '2009-04-30 00:00:00')
-----------------------------------------------------------
but the result was new data inserted into payment only the trigger not work or my coding was happen incorrect.
pls advise
thank you so much
Edited 2 time(s). Last edit at 04/27/2009 11:05PM by mcdonal boomblast.