Solved

execute the statement of creating trigger inside a MySQL store procedure

Posted on 2013-11-21
4
1,074 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

820 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