Solved

execute the statement of creating trigger inside a MySQL store procedure

Posted on 2013-11-21
4
1,151 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
[X]
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
  • 2
  • 2
4 Comments
 
LVL 49

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 49

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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

691 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