Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MYSQL trigger

Posted on 2014-03-25
13
Medium Priority
?
371 Views
Last Modified: 2014-03-27
Dear all,

I have this trigger to run but it doens't works on workbench and Toad for MySQL, can't see why.

use Insight_20101202
;

  DROP TRIGGER IF EXISTS BC_BEFORE_DELETE_TRIGGER; 
CREATE TRIGGER BC_BEFORE_DELETE_TRIGGER BEFORE DELETE ON Insight_20101202.BC FOR EACH ROW
BEGIN INSERT INTO Insight_20101202_DataAudit.BC SET TriggerAction="BEFORE", ActionDone="DELETE",
Action_AuditDate=now(), ActionBy_Audit=USER(), SR=OLD.SR, MA=OLD.MA, CreatedDate=OLD.CreatedDate, LastSubmit=OLD.LastSubmit , 
LastApprove= OLD.LastApprove
 END;

Open in new window


The error said:

Syntax error near “Index=OLD.Index, InsightSR=OLD.InsightSR, OtherID=OLD.OtherID”

Open in new window


The rest of the trigger I created !
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
13 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 39952733
but this one is working fine :

DROP TRIGGER IF EXISTS Amap2_BEFORE_DELETE_TRIGGER; CREATE TRIGGER Amap2_BEFORE_DELETE_TRIGGER BEFORE DELETE ON Insight_20101202.Amap2 FOR EACH ROW
BEGIN 
INSERT INTO Insight_20101202_DataAudit.Amap2 SET 
TriggerAction="BEFORE",
ActionDone="DELETE",
Action_AuditDate=now(),
ActionBy_Audit=USER() , row_count=OLD.row_count, GSMP_ID=OLD.GSMP_ID; END ;  

Open in new window


just can't see why !
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39955303
Index is a reserved keyword, please put `Index` instead
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39955356
single quote you mean ? as I generate this one using dynamic SQL.

I also get around the same error in here:

DROP TRIGGER IF EXISTS tblCCHTax_Temp_BEFORE_DELETE_TRIGGER; CREATE TRIGGER tblCCHTax_Temp_BEFORE_DELETE_TRIGGER BEFORE DELETE ON Insight_20101202.tblCCHTax_Temp FOR EACH ROW
BEGIN 
INSERT INTO Insight_20101202_DataAudit.tblCCHTax_Temp SET 
TriggerAction="BEFORE",
ActionDone="DELETE",
Action_AuditDate=now(),
ActionBy_Audit=USER() , ID=OLD.ID, InvoiceNo=OLD.InvoiceNo, CCH_CUSTNUM=OLD.CCH_CUSTNUM, InvoiceHeaderID=OLD.InvoiceHeaderID, SR=OLD.SR, 
BillHeaderID=OLD.BillHeaderID, BillAddr=OLD.BillAddr, Usage=OLD.Usage, Charge=OLD.Charge, PaidType=OLD.PaidType, Site1Addr=OLD.Site1Addr, 
Site2Addr=OLD.Site2Addr, ProductID=OLD.ProductID, ProductDesc=OLD.ProductDesc, CCHGroup=OLD.CCHGroup, ServiceType=OLD.ServiceType, ServiceID=OLD.ServiceID, 
CustomerName=OLD.CustomerName, ContractCcy=OLD.ContractCcy; END ;

Open in new window


error is :

error near 'Usage=OLD.Usage, Charge=OLD.Charge, PaidType=OLD.PaidType, Site1Addr=OLD.Site1Ad' at line 8

Open in new window

0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 1

Author Comment

by:marrowyung
ID: 39955363
any other reserved key word for this kind of thing?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39955375
this one too doesn't work:

DROP TRIGGER IF EXISTS Amap_BEFORE_DELETE_TRIGGER; 
CREATE TRIGGER Amap_BEFORE_DELETE_TRIGGER BEFORE DELETE ON Insight_20101202.Amap FOR EACH ROW
BEGIN 
INSERT INTO Insight_20101202_DataAudit.Amap SET 
TriggerAction="BEFORE",
ActionDone="DELETE",
Action_AuditDate=now(),
ActionBy_Audit=USER() , 'Index'=OLD.'Index', InsightSR=OLD.InsightSR, OtherID=OLD.OtherID; END ;  

Open in new window


error:

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 ''Index'=OLD.'Index', InsightSR=OLD.InsightSR, OtherID=OLD.OtherID; END' at line 7

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39955377
but I agree that without the index:

DROP TRIGGER IF EXISTS Amap_BEFORE_DELETE_TRIGGER; 
CREATE TRIGGER Amap_BEFORE_DELETE_TRIGGER BEFORE DELETE ON Insight_20101202.Amap FOR EACH ROW
BEGIN 
INSERT INTO Insight_20101202_DataAudit.Amap SET 
TriggerAction="BEFORE",
ActionDone="DELETE",
Action_AuditDate=now(),
ActionBy_Audit=USER() ,InsightSR=OLD.InsightSR, OtherID=OLD.OtherID; END ;  

Open in new window


it works.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39955389
the second one, it seems the  Usage=OLD.Usage has problem, why ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39955525
usage is also a reserved keyword, see here:
http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html
so, apply the backticks also there
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39955527
and reading above question: no, it's NOT a single quote ('), but backticks (`) which are used in mysql for special names
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39955577
`Index`=OLD.`Index`, 

Open in new window


?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39955588
yes, exactly, and also:
 `Usage`=OLD.`Usage` 

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39955615
`loop`=OLD.`loop` too I found out!

so we decide to add ` for all field to avoid detecting anything stupid like that.

so if we add ` all any field in the trigger, it don't break the logic and it don't stop the trigger to record vaule ?
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39960661
very good !
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question