Mysql trigger affecting same table

Posted: April 22, 2011 in MySql, PHP
DELIMITER $$

CREATE   
    TRIGGER `firstname_lastname_on_update` BEFORE UPDATE
    ON `student`
    FOR EACH ROW BEGIN
	SET NEW.firstName = SUBSTRING_INDEX( NEW.name, ' ', 1 ),
	NEW.lastName = IF( 
			STRCMP( SUBSTRING_INDEX( NEW.name, ' ', 1 ) ,SUBSTRING_INDEX( NEW.name, ' ', -1 )) , 
			SUBSTRING_INDEX( NEW.name, ' ', -1 ) ,
			'' );
    END$$

DELIMITER ;


DELIMITER $$

CREATE    
    TRIGGER `firstname_lastname_on_insert` BEFORE INSERT
    ON `student`
    FOR EACH ROW BEGIN
	SET NEW.firstName = SUBSTRING_INDEX( NEW.name, ' ', 1 ),
	NEW.lastName = IF( 
			STRCMP( SUBSTRING_INDEX( NEW.name, ' ', 1 ) ,SUBSTRING_INDEX( NEW.name, ' ', -1 )) , 
			SUBSTRING_INDEX( NEW.name, ' ', -1 ) ,
			'' );
    END$$

DELIMITER ;


DROP TRIGGER firstname_lastname_on_update;
SHOW TRIGGERS;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s