Trigger after INSERT in phpmyadmin

Question

I have a site that handles CSV uploads to a database. The uploads work but I've had problems in the past with triggers working from phpmyadmin.

The CSV files could have 5 to 20 rows/records, but no matter what they have 230 columns. The upload first puts all of this into a staging table of the same size. Upon upload and INSERT into database, I have several QA checks which I manually apply at the moment. IF I paste these into phpmyadmin, they work and take place as they should, but I need to create a trigger that will preform these checks as soon as the upload is complete.

I'm very new to SQL but the idea is: Upon upload, if there are 20 rows in the CSV, all 20 rows are iterated and INSERTED into staging, and once the insert is complete I want this whole trigger to happen on my 'staging' table.

Here is my trigger code, but PHPmyadmin is giving me a syntax error:

CREATE TRIGGER QAtrigger
AFTER INSERT ON `staging`
FOR EACH ROW
BEGIN

UPDATE staging 
SET  `miuFlag` =1 
WHERE  `lowSideMIUNumArriv` =    `lowSideMIUNumDepart` 
AND  `miu` =  "NO";

UPDATE staging 
SET  `miuFlag` =1 
WHERE  `lowSideMIUNumArriv` !=    `lowSideMIUNumDepart` 
AND  `miu` =  "YES";

UPDATE staging 
SET  `miuFlag` =0
WHERE  `lowSideMIUNumArriv` =    `lowSideMIUNumDepart` 
AND  `miu` =  "YES";

UPDATE staging 
SET  `miuFlag` =0 
WHERE  `lowSideMIUNumArriv` !=    `lowSideMIUNumDepart` 
AND  `miu` =  "No";

 UPDATE staging 
SET  `testPortFlag` =1 
WHERE  `testPort` =   "Present" 
AND  `testPortInstalled` =  "NO";

UPDATE staging 
SET  `testPortFlag` =1 
WHERE  `testPort` =   "Installed" 
AND  `testPortInstalled` =  "YES";


UPDATE staging 
SET  `workOrderFlag` =0 
WHERE `workOrderType2` = "ONSITE SURVEY TEST AND REPAIR"
AND  `completeMeter` = "NO"
AND  `ume` =  "NO"
AND  `discChamber` =  "NO"
AND  `turbineChamber` =  "NO"
AND `automaticValve`  =  "NO"
AND  `strainer` =  "NO"
AND  `lowRegister` =  "NO"
AND  `highRegister` =  "NO"
AND  `miu` =  "NO"
AND  `antennae` =  "NO"
AND  `calibrationVane` =  "NO"
AND  `meterLeakRepaired` =  "NO";

UPDATE staging 
SET  `workOrderFlag` =1
WHERE `workOrderType2` = "ONSITE SURVEY TEST AND REPAIR"
AND  `completeMeter` = "YES"
OR  `ume` =  "YES"
OR  `discChamber` =  "YES"
OR  `turbineChamber` =  "YES"
OR `automaticValve`  =  "YES"
OR  `strainer` =  "YES"
OR  `lowRegister` =  "YES"
OR  `highRegister` =  "YES"
OR  `miu` =  "YES"
OR  `antennae` =  "YES"
OR  `calibrationVane` =  "YES"
OR  `meterLeakRepaired` =  "YES";


UPDATE staging 
SET  `leakFlag` =1 
WHERE  `apparentLeak` =   "NO" 
AND  `leakLocation` =  "NONE"
AND `leakType` = "UNKNOWN"
OR  `apparentLeak` =   "NO" 
AND `leakLocation` =  "NONE"
AND `leakType` = "NONE"
OR  `apparentLeak` =   "NO" 
AND `leakLocation` =  "NONE"
AND `leakType` = "N/A";
END

#1064 - 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 'UPDATE staging SET `miuFlag` =1 WHERE `lowSideMIUNumArriv` != `lowSideM' at line 10 — Geoff_S, Jun 12 '17 at 20:00
Are you using 'delimiter $$' to change the delimiter prior to running this (see https://stackoverflow.com/questions/15786240/mysql-create-stored-procedure-syntax-with-delimiter) — Nigel Ren, Jun 12 '17 at 20:22
I'm not, I'm only running exactly what's up above. I tried that in the SQL editor and then I've tried using the trigger creator in PHPmyadmin but I;m also getting an error there. I'm just not sure the proper syntax for the entire trigger either way — Geoff_S, Jun 12 '17 at 20:23
Follow the example in the link - put `DELIMITER $$` at the start of your scripy and `$$` as the last line — Nigel Ren, Jun 12 '17 at 20:25
I see. I'll try that now, thank you. So for my example should DELIMITER $$ go between FOR EACH ROW and BEGIN? — Geoff_S, Jun 12 '17 at 20:27

Trigger after INSERT in phpmyadmin

0 Answers0