Solved

execute the statement of creating trigger inside a MySQL store procedure

Posted on 2013-11-21
4
1,052 Views
Last Modified: 2013-11-21
Dear all,

I created a SP like that:

use PCCWSP;
DELIMITER $$

CREATE PROCEDURE `Create_BEFORE_DELETE_TRIGGER` (IN Trigger_databasename VARCHAR(40), IN trigger_tablename VARCHAR(40))  	
BEGIN  
DECLARE a, b, fieldnameCursor_finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE current_fieldname  VARCHAR(50) DEFAULT ""; 
DECLARE fullexcutecmd VARCHAR(5000);
DECLARE fullexcutecmd1 VARCHAR(5000);
DECLARE fullexcutecmd2 VARCHAR(5000);
DECLARE fullexcutecmd3 VARCHAR(60000);
DECLARE fullexcutecmd4 VARCHAR(100);
DECLARE fullexcutecmd5 VARCHAR(100);

/*Declare and populate the cursor with a SELECT statement */  	
	  
DECLARE fieldname CURSOR FOR 	
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fieldnameCursor_finished = 1;     
/*Specify what to do when no more records found, notice that the handler declaration must appear after variable and cursor declaration inside the stored procedures*/  	

/* SET @fullexcutecmd =  CONCAT('SELECT COLUMN_NAME,  DATA_TYPE, COLUMN_TYPE,  CHARACTER_MAXIMUM_LENGTH, COLUMN_DEFAULT,
  IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE TABLE_SCHEMA = ','"', databaseName,'"',' AND TABLE_NAME = ','"',  current_tablelist ,'"',';');  */
 
SET @fullexcutecmd1= CONCAT( ' CREATE TRIGGER ',trigger_tablename,'_BEFORE_DELETE_TRIGGER BEFORE DELETE ON ',trigger_tablename,' FOR EACH ROW
BEGIN 
INSERT INTO Audit_info.DBAudit_',trigger_tablename,' SET 
TriggerAction=','"','BEFORE','",','
Action=','"','DELETE','",','
ActionDate=now(),
ActionBy=USER()');   

OPEN fieldname ;	
set @fullexcutecmd2=' ';
set @fullexcutecmd3=' ';

	get_fieldlist: LOOP	
 
   FETCH fieldname INTO current_fieldname;	
         IF fieldnameCursor_finished = 1 THEN 	
          LEAVE get_fieldlist;	
          END IF;	

 /* set @fullexcutecmd2= CONCAT('fullexcutecmd2', fullexcutecmd); */
/* set @fullexcutecmd2= CONCAT(@fullexcutecmd2,@fullexcutecmd);  -- working  */
   /* for debug purpose only: 
select @fullexcutecmd2; */
 
SET @fullexcutecmd2 = CONCAT(' ',current_fieldname,'=OLD.',current_fieldname);
 set @fullexcutecmd3= CONCAT(@fullexcutecmd3,',',@fullexcutecmd2); 
 /*
 PREPARE stmt FROM @fullexcutecmd2;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;    */
   
      END LOOP  get_fieldlist;	
close fieldname;                             
/*
 set fullexcutecmd4= CONCAT('set delimiter to ||'); 
 select @fullexcutecmd4; 
 
 PREPARE stmt FROM @fullexcutecmd4;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  */
  
  set @fullexcutecmd3= CONCAT(@fullexcutecmd1,@fullexcutecmd3,'; END '); 
 /* select @fullexcutecmd3;  */

   PREPARE stmt FROM @fullexcutecmd3;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt; 
  /*
 set fullexcutecmd5= CONCAT('||'); 
 select @fullexcutecmd5;
 
  PREPARE stmt FROM @fullexcutecmd5;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;   */
  
END $$
DELIMITER ; 

Open in new window


when I call it and I want to execute the statement @fullexcutecmd3 which create a trigger.

but when I call this SP, it has error:

Lookup Error - MySQL Database Error: This command is not supported in the prepared statement protocol yet

Open in new window


what I can say is I can do select @fullexcutecmd3;  to list the create trigger command out and run it in the console.

can I call the create trigger statement inside a store procedure in mYSQL ?
0
Comment
Question by:marrowyung
  • 2
  • 2
4 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39665647
>>"Can I call the create trigger statement inside a store procedure in MySQL ?"
as at MySQL 5.7 (and all earlier versions) the answer is No, not through a prepared statement.

see: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html
These are the allowed create items from that reference:
{CREATE | DROP} INDEX
{CREATE | RENAME | DROP} DATABASE
{CREATE | DROP} TABLE
{CREATE | RENAME | DROP} USER
{CREATE | DROP} VIEW
Right now you would have to pass the dynamic sql into another environment (e.g. PHP) to execute the create trigger code.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39665887
ok, or copy and paste the create trigger statements (which is working fine from dynamic SQL point of view)  to the workbench windows and execute it ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39667641
yes, you can copy/paste into workbench and execute it from there.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39668065
simple answer enought
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

777 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