MySQL Forums
Forum List  »  Spanish

Re: substr en Trigger
Posted by: William Chiquito
Date: June 12, 2007 03:50PM

Hola Carlos,

No estoy en conocimiento de esa versiĆ³n de MySQL (5.2). Las que conozco son:

* 5.0.x (GA)
* 5.1.x (Beta)
* 6.0.0 (Alpha)

Trata de la siguiente forma:
CREATE TABLE `t1` (
  `cadena` varchar(20) collate latin1_spanish_ci default NULL,
  `partea` varchar(20) collate latin1_spanish_ci default NULL,
  `parteb` varchar(20) collate latin1_spanish_ci default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci;

DELIMITER $$

DROP TRIGGER `prueba`$$

CREATE TRIGER .`prueba` BEFORE INSERT on `t1` 
FOR EACH ROW
BEGIN
	DECLARE sizecadena INT;
	DECLARE carac INT;
	SET sizecadena = CHAR_LENGTH(NEW.cadena);
	IF sizecadena > 0 THEN
		SET carac = TRUNCATE(sizecadena / 2, 0);
		IF (carac > 0) THEN
			SET NEW.partea = SUBSTRING(NEW.cadena, 1, carac);
			SET NEW.parteb = SUBSTRING(NEW.cadena, carac+1);
		ELSE
			SET NEW.partea = NEW.cadena;
		END IF;
	END IF;
END;
$$

DELIMITER ;

INSERT INTO t1 (cadena) VALUES ('PRUEBA');

SELECT * FROM t1;
Resultado:
cadena  partea  parteb
------  ------  ------
PRUEBA  PRU     EBA

INSERT INTO t1 (cadena) VALUES ('1234567');

SELECT * FROM t1;
Resultado:
cadena   partea  parteb
-------  ------  ------
PRUEBA   PRU     EBA   
1234567  123     4567

INSERT INTO t1 (cadena) VALUES ('A');

SELECT * FROM t1;
Resultado:
cadena   partea  parteb
-------  ------  ------
PRUEBA   PRU     EBA   
1234567  123     4567  
A        A       (NULL)

INSERT INTO t1 (cadena) VALUES ('');

SELECT * FROM t1;
Resultado:
cadena   partea  parteb
-------  ------  ------
PRUEBA   PRU     EBA   
1234567  123     4567  
A        A       (NULL)
         (NULL)  (NULL)

Options: ReplyQuote


Subject
Views
Written By
Posted
3747
June 12, 2007 03:16PM
Re: substr en Trigger
4220
June 12, 2007 03:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.