CODEWITHSUNDEEP
mysqlstored-procedurestriggers
Fadjrin Kurnia
Fadjrin Kurnia

Reputation: 23

MYSQL : How to create trigger from store procedure

i have a store procedure. This function produces table and triggers, for the table is successfully created, but until now the trigger is not successfully created :|.

source code :

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_inventarisasi`(IN _id_bbws INT, IN _nama_lokasi VARCHAR(100), IN _id_das INT, IN _ws VARCHAR(100), IN _id_provinsi INT, IN _id_kota_kab INT, IN _lat FLOAT(10,6), IN _lng FLOAT(10,6), IN _tahun_bangun INT, IN _id_tipe INT, IN _id_merk INT, IN _nama_pengamat VARCHAR(256))
BEGIN
   DECLARE txn_error INTEGER DEFAULT 0;
   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN
               SET txn_error = 1;
   END;

   SET @tipealat = (SELECT nama_tipe FROM t_tipe WHERE id_tipe = _id_tipe);
   SET @icon = 'default';

   IF UPPER(@tipealat) LIKE CONCAT(UPPER('PDA'), '%') THEN
      SET @icon = 'pda';
   ELSEIF UPPER(@tipealat) LIKE CONCAT(UPPER('PCH'), '%') THEN
         SET @icon = 'pch';
   END IF;

   SAVEPOINT savepoint_pointer;  
   INSERT INTO `dhtml`.`t_inventarisasi`
    (`id_bbws`,`nama_lokasi`,`id_das`,`ws`,`id_provinsi`,`id_kota_kab`,`lat`,`lng`,`tahun_bangun`, `id_tipe`,`id_merk`,`nama_pengamat`, `icon`)
    VALUES
    (_id_bbws, _nama_lokasi, _id_das, _ws, _id_provinsi, _id_kota_kab, _lat, _lng, _tahun_bangun, _id_tipe, _id_merk, _nama_pengamat, @icon);
   IF txn_error = 1 THEN
      ROLLBACK TO savepoint_pointer;
      SELECT '<div class="alert alert-error"><button type="button" class="close" data-dismiss="alert">&times;</button><strong>Gagal!</strong> Input Data !</div>' AS hasil;  
   ELSE
      SELECT '<div class="alert alert-success"><button type="button" class="close" data-dismiss="alert">&times;</button><strong>Sukses!</strong> Input Data !</div>' AS hasil;  
      COMMIT;

       SET @sql = CONCAT('CREATE TABLE `lokasi`.`',_nama_lokasi,'` (
                                                                  `ReceivedDate` date NOT NULL,
                                                                  `ReceivedTime` time NOT NULL,
                                                                  `SamplingDate` date NOT NULL,
                                                                  `SamplingTime` time NOT NULL,
                                                                  `Rain` int(11) DEFAULT NULL,
                                                                  `WLevel` int(11) DEFAULT NULL,
                                                                  PRIMARY KEY (`ReceivedDate`,`ReceivedTime`,`SamplingDate`,`SamplingTime`)
                                                            ) ');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET @trigger = CONCAT('DELIMITER $$ 
                               USE `lokasi`$$ 
                               CREATE 
                               DEFINER=`root`@`localhost`
TRIGGER `lokasi`.`onINSERT_',_nama_lokasi,'`
AFTER INSERT ON `lokasi`.`',_nama_lokasi,'`
FOR EACH ROW
BEGIN  
  IF NEW.WLevel > 0 THEN
     SET @icon = \'pda\';
     SET @id_tipe = (SELECT id_tipe FROM `dhtml`.`t_tipe` WHERE UPPER(nama_tipe) LIKE UPPER(\'PDA%\') LIMIT 1);
     SET @id_inventarisasi = (SELECT id_inventarisasi FROM `dhtml`.`t_inventarisasi` WHERE UPPER(nama_lokasi) = UPPER(\'',_nama_lokasi,'\') LIMIT 1);
     IF NEW.ReceivedDate = CURDATE() THEN
        IF NEW.WLevel > 500 && NEW.WLevel < 1500 THEN
           SET @icon = \'normal\'; 
        ELSEIF NEW.WLevel > 1500 && NEW.WLevel < 2500 THEN  
           SET @icon = \'sedang\'; 
        ELSEIF NEW.WLevel > 2500 THEN  
           SET @icon = \'awas\';   
        END IF;            
     ELSE
        SET @10hari = (SELECT CURDATE() - INTERVAL 10 DAY);
        IF NEW.ReceivedDate <= @10hari THEN
           SET @icon = \'mati\'; 
        END IF;        

        SET @5hari = (SELECT CURDATE() - INTERVAL 5 DAY);
        IF NEW.ReceivedDate > @10hari AND NEW.ReceivedDate <= @5hari THEN
           SET @icon = \'tdkupdate\';  
        END IF;  
     END IF;
        UPDATE `dhtml`.`t_inventarisasi` SET icon = @icon WHERE id_inventarisasi = @id_inventarisasi AND id_tipe = @id_tipe;
     END IF;

END$$
');
        PREPARE stmt FROM @trigger;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
   END IF;  

END

Have some hints?

Upvotes: 2

Views: 5512

Answers (2)

Rachmat Gunawan
Rachmat Gunawan

Reputation: 26

Check this: http://forums.mysql.com/read.php?98,588793,588793#msg-588793. 1. can not create trigger from prepared statement 2. can not create trigger from stored procedure So, split the code, you can put the part of creating trigger in PHP code.

Upvotes: 0

wchiquito
wchiquito

Reputation: 16551

Creating triggers are not currently supported from prepared statements. See 13.5. SQL Syntax for Prepared Statements

Upvotes: 3

Related Questions