Solved

MYSQL trigger

Posted on 2014-03-25
13
369 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

734 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