MYSQL trigger

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 !
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes, exactly, and also:
 `Usage`=OLD.`Usage` 

Open in new window

0
 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Index is a reserved keyword, please put `Index` instead
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
any other reserved key word for this kind of thing?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
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
 
marrowyungSenior Technical architecture (Data)Author Commented:
the second one, it seems the  Usage=OLD.Usage has problem, why ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
and reading above question: no, it's NOT a single quote ('), but backticks (`) which are used in mysql for special names
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
`Index`=OLD.`Index`, 

Open in new window


?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
`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
 
marrowyungSenior Technical architecture (Data)Author Commented:
very good !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.