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
Solved

MYSQL trigger

Posted on 2014-03-25
13
364 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 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