mysql triggers syntax error -



mysql triggers syntax error -

i having problem creating mysql trigger. trigger uses replace, when row replaced, reads row , saves info in table. if new row inserted tracking table, info not saved in other table, row didn't exist. need save tracking row if exists.

drop trigger if exists savetracking; create trigger savetracking before replace on 'orderstest.tracking' each row begin declare orderid integer; if new.invno != '' set orderid = (select orderid order_header invno = new.invno); insert comments (id, date_time, type, comments) values (orderid, sysdate(), \"o\", \"previous tracking: usps - old.trackno\"); endif; end;

this error get:

#1064 - have error in sql syntax; check manual corresponds mysql server version right syntax utilize near 'replace on 'orderstest.tracking' each row begin declare orderid integer' @ line 1

i using phpmyadmin seek , add together trigger.

mysql version software version: 5.0.95-rs

thanks,

steve

--- - table construction table `tracking` -- create table if not exists `tracking` ( `id` int(11) not null auto_increment, `invno` bigint(20) not null default '0', `carrier` varchar(5) not null default '', `trackno` varchar(50) not null default '', primary key (`id`), key `invno` (`invno`) ) engine=myisam default charset=latin1 auto_increment=3033 ; -- -- table construction table `comments` -- create table if not exists `comments` ( `comment_id` int(11) not null auto_increment, `id` int(11) not null default '0', `date_time` datetime not null default '0000-00-00 00:00:00', `type` char(1) not null default '', `comments` mediumtext not null, primary key (`comment_id`), key `id` (`id`) ) engine=myisam default charset=latin1 auto_increment=10426 ; -- -- table construction table `order_header` -- create table if not exists `order_header` ( `our_orderid` int(11) not null auto_increment, `orderid` varchar(20) not null default '0', `orderdatetime` datetime not null default '0000-00-00 00:00:00', `custid` int(11) not null default '0', `source` char(1) not null default '', `comments` mediumtext not null, `invno` bigint(20) not null default '0', `infoid` varchar(15) not null default '0', `remote_host` varchar(50) not null default '', `remote_addr` varchar(50) not null default '', `items` int(11) not null default '0', `paytype` varchar(20) not null default '', `cc_name` varchar(50) not null default '', `cc_num` tinyblob not null, `cc_valid` tinyblob not null, `cc_expire` varchar(10) not null default '', `avs_address` varchar(20) not null default '', `avs_zip` varchar(5) not null default '', `shipping` varchar(30) not null default '', `order_status` char(1) not null default '', `batch_no` int(11) not null default '0', `time_process` datetime not null default '0000-00-00 00:00:00', `fraud_flag` char(1) not null default '', `referrer` varchar(255) not null default '', `stats` char(1) not null default 'n', `country_code` char(2) not null default '', `zipzone` tinyint(4) not null default '0', `ship_zip` varchar(5) not null default '', `bank_name` varchar(50) not null default '', `bank_country_name` varchar(50) not null default '', primary key (`our_orderid`), key `order_status` (`order_status`), key `orderdatetime` (`orderdatetime`), key `invno` (`invno`), key `remote_host` (`remote_host`), key `custid` (`custid`), key `infoid` (`infoid`) ) engine=myisam default charset=latin1 auto_increment=320081 ;

i have changed trigger this.

drop trigger if exists savetracking; create trigger savetracking before insert on orderstest.tracking each row begin declare orderid integer; declare otrackno integer; set otrackno = (select trackno tracking invno = new.invno); if otrackno != '' set orderid = (select orderid order_header invno = new.invno); insert comments (id, date_time, type, comments) values (orderid, sysdate(), 'o', 'previous tracking: usps - otrackno'); delete trackno invno = new.invno; endif;

end;

the error

1064 - have error in sql syntax; check manual corresponds mysql server version right syntax utilize near '' @ line 4

there no replace triggers. have selection of insert, update, or delete.

if execute replace, cause both delete , insert triggers on table run.

also can't quote table name doing.

on 'orderstest.tracking'

should be

on `orderstest`.`tracking`

and should larn how utilize delimiter before seek define triggers contain compound statements.

mysql syntax triggers

Comments

Post a Comment

Popular posts from this blog

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* -

xslt - DocBook 5 to PDF transform failing with error: "fo:flow" is missing child elements. Required content model: marker* - i've inherited doc publishing process takes docbook , builds html , pdf output, using apache fop both. project had been started, never completed. lots of tweaking, i've able of doc sets build (20+), except handful, , fail severe code 1 of these 2 conditions: "fo:flow" missing kid elements. required content model: marker* "fo:block" not valid kid of "fo:root" the xml output comes out of docbook , docbook-fop checks out well-formed. the fop error in 1 book, "endecagloss", example, points end of string: <fo:flow flow-name="xsl-region-body" start-indent="4pc" end-indent="0pt"/> here’s log output in case: [java] severe: javax.xml.transform.transformerexception: file:/scratch/publishing/hudson/jobs/build-main-endeca-documentation/workspace/serverdoc/w...
Read more

mediawiki - How do I insert tables inside infoboxes on Wikia pages? -

mediawiki - How do I insert tables inside infoboxes on Wikia pages? - i want insert tables in infoboxes. example, have proteinbox i've developed using infobox template (http://health-and-medicine.wikia.com/wiki/template:proteinbox), , fasta field big fit comfortably in proteinbox. hence collapsible table within box. this tried: {{infobox | row 1 = {| class = "mw-collapsible mw-collapsed wikitable" |- | <!-- table content --> |} }} your problem is, putting nested table within template. pipe characters ( | ) in table syntax collides pipe usage in templates. the commonly used hack around create template called template:! , containing pipe character, , utilize when need set tables, parser functions or other stuff using pipe characters, within templates. table (with every | replaced {{!}} ): {{{!}} {{!}}- {{!}} a1 {{!}} b1 {{!}}- {{!}} a2 {{!}} b2 {{!}}} ...the equivalent of {| |- | a1 | b1 |- | a2 | b2 |} furthermore, hav...
Read more

Local Service User Logged into Windows -

Local Service User Logged into Windows - i created windows service tracks app server user connected through load balancer. it retrieves user logged windows on machine, cross references active directory (ad) business relationship homecoming first name, lastly name , e-mail (for notification through website created project) there 1 scheme on network users remote system, vista system, reason returning values "local service" logged machine, it's weird , security guys impossible "local service" business relationship logged windows. here how user logged windows managementscope ms = new managementscope("\\\\.\\root\\cimv2"); objectquery query = new objectquery("select * win32_computersystem"); managementobjectsearcher searcher = new managementobjectsearcher(ms, query); foreach (managementobject mo in searcher.get()) { _username = mo["username"].tostring(); } // remove domain part username string[] usernameparts = _...
Read more