Solved

MYSQL trigger

Posted on 2014-03-25
13
366 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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 500 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 500 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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