After Insert Trigger

server: Server version: 5.0.38-Ubuntu
error: MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'SET NEW.TSHP = 0' at line 5

What's the problem with this trigger?


CREATE TRIGGER shipumw01 BEFORE INSERT ON umw_ship_sol
FOR EACH ROW
BEGIN
IF NEW.L0 <> 0 THEN
SET NEW.TSHP = 0;
SET NEW.LSHP = 0;
SET NEW.NSHP = 0;
SET NEW.BO = NEW.OQTY;
SET NEW.ST = 'NS';
END IF;
END

Thank you all

Bre-x wrote:
> server: Server version: 5.0.38-Ubuntu
> error: MySQL said:
> #1064 - You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'SET NEW.TSHP = 0' at line 5
>
> What's the problem with this trigger?
>
>
> CREATE TRIGGER shipumw01 BEFORE INSERT ON umw_ship_sol
> FOR EACH ROW
> BEGIN
> IF NEW.L0 <> 0 THEN
> SET NEW.TSHP = 0;
> SET NEW.LSHP = 0;
> SET NEW.NSHP = 0;
> SET NEW.BO = NEW.OQTY;
> SET NEW.ST = 'NS';
> END IF;
> END
>
> Thank you all
well, can you post the table definition?

On Wed, 2 Jan 2008 14:23:05 -0800 (PST), Bre-x
<cholotron@hotmail.com> wrote:

>server: Server version: 5.0.38-Ubuntu
>error: MySQL said:
>#1064 - You have an error in your SQL syntax; check the manual that
>corresponds to your MySQL server version for the right syntax to use
>near 'SET NEW.TSHP = 0' at line 5
>
>What's the problem with this trigger?
>
>
>CREATE TRIGGER shipumw01 BEFORE INSERT ON umw_ship_sol
>FOR EACH ROW
>BEGIN
>IF NEW.L0 <> 0 THEN
> SET NEW.TSHP = 0;
> SET NEW.LSHP = 0;
> SET NEW.NSHP = 0;
> SET NEW.BO = NEW.OQTY;
> SET NEW.ST = 'NS';
>END IF;
>END
>
>Thank you all

You have to define a new DELIMITER to be able to use
semicolons is you trigger definition.
See the example on page:
http://dev.mysql.com/doc/refman/5.0/...e-trigger.html

HTH
--
( Kees
)
c[_] FUD was first defined by Gene Amdahl after he left IBM to
found his own company, Amdahl Corp.: "FUD is the fear,
uncertainty, and doubt that IBM sales people instill in the
minds of potential customers who might be considering Amdahl
products." ( http://en.wikipedia.org/wiki/Fud ) (#171)

> > Thank you all
>
> well, can you post the table definition?- Hide quoted text -
>
> - Show quoted text -

Here is my table definition

CREATE TABLE `umw_ship_sol` (
`LID` int(11) NOT NULL auto_increment,
`SID` int(11) NOT NULL default '0',
`L0` int(11) default '0',
`L1` int(11) default NULL,
`L2` varchar(3) default NULL,
`L3` varchar(3) default NULL,
`PCODE` varchar(10) default NULL,
`PDESC` varchar(30) default NULL,
`OQTY` double default NULL,
`BO` double default NULL,
`TSHP` double default NULL,
`LSHP` double default NULL,
`NSHP` double default NULL,
`ST` varchar(2) default NULL,
PRIMARY KEY USING BTREE (`LID`,`SID`),
KEY `SID` (`SID`),
CONSTRAINT `umw_ship_sol` FOREIGN KEY (`SID`) REFERENCES
`umw_ship_so` (`SID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

This one work

delimiter //
CREATE TRIGGER shipumw01 BEFORE INSERT ON umw_ship_sol
FOR EACH ROW
BEGIN
IF NEW.L0 <> 0 THEN
SET NEW.TSHP =0;
SET NEW.LSHP =0;
SET NEW.NSHP =0;
SET NEW.BO = NEW.OQTY;
SET NEW.ST = 'NS';
END IF;
END; //
delimiter ;

Thank you all for you help

Bre-x