Solved

execute the statement of creating trigger inside a MySQL store procedure

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Creating and Managing Databases with phpMyAdmin in cPanel.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now