I am trying to import database trigger on one of my tables I have imported earlier. It is giving me following error.
I have googled a lot to resolve this issue. everybody asks me to set following things.
I did both things together and one-bye-one too. But nothing helped.
Below is my import file content. (generated from mysql workbench)
-- MySQL dump 10.13 Distrib 5.7.21, for Win64 (x86_64)
--
-- Host: localhost Database: cpr
-- ------------------------------------------------------
-- Server version 5.7.21-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `cpr.updateTRNumber`
BEFORE INSERT ON cpr.changerequest FOR EACH ROW
BEGIN
DECLARE nextNumber VARCHAR(10);
SET nextNumber = (select if(cnt = 0, 'TR0000001', (select concat('TR',lpad(cast(max(substr(CRTRNumber,3))+1 as char),7,'0'))
from cpr.changerequest where CRLocationId=new.CRLocationId)) as nextTicketNumber
from (SELECT count(*) as cnt
FROM cpr.changerequest
where CRLocationId=new.CRLocationId) as TRCountInLocation);
SET new.CRTRNumber = nextNumber;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `cpr`.`updateTCRPumber` BEFORE UPDATE ON `changerequest` FOR EACH ROW
BEGIN
DECLARE nextNumber VARCHAR(11);
DECLARE currNumber VARCHAR(11);
DECLARE statusidtocheck integer;
select CRSMID
into statusidtocheck
from changerequeststatusmaster
where CRSMStatus = "Requester Hod Approved Proposed Solution";
if (new.CRStatus = statusidtocheck) then
select IFNULL(new.CRTCRPNumber,"") into currNumber from cpr.changerequest where CRId = new.CRId;
if (currNumber = "") THEN
SET nextNumber = (select if(cnt = 0, 'CRP0000001', (select concat('CRP',lpad(cast(max(substr(CRTCRPNumber,4))+1 as char),7,'0'))
from cpr.changerequest where CRLocationId=new.CRLocationId)) as nextTicketNumber
from (SELECT count(*) as cnt
FROM cpr.changerequest
where CRLocationId=new.CRLocationId
and CRTCRPNumber is not null) as TRCountInLocation);
#SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = new.CRLocationId ;
SET new.CRTCRPNumber = nextNumber;
END IF;
end if;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-01-24 12:02:31
I have been searching for last week and not able to find any solution to this issue. I found from Google Cloud SQL group that SUPER privilege is not supported.
