{ Berbagi, Menulis, Dan Mengajar } Ilmu…

1.) CopyPbk –>

DELIMITER $$

USE `sms2`$$

DROP TRIGGER /*!50032 IF EXISTS */ `CopyPbk`$$

CREATE
/*!50017 DEFINER = ‘dimas’@’%’ */
TRIGGER `CopyPbk` AFTER INSERT ON `pbk`
FOR EACH ROW BEGIN
INSERT INTO sms2.pbk_copy (GroupID,NAME,Number,id_pbk,id_user)
VALUE ( NEW.GroupID,NEW.NAME,NEW.Number,NEW.id_pbk,NEW.id_user);

IF (SELECT LEFT(NEW.Number,3))=081 THEN

UPDATE pbk_copy
SET Number = REPLACE(Number,’081′,’+6281′);

ELSEIF (SELECT LEFT(NEW.Number,3))=085 THEN

UPDATE pbk_copy
SET Number = REPLACE(Number,’085′,’+6285′);

ELSEIF (SELECT LEFT(NEW.Number,3))=082 THEN

UPDATE pbk_copy
SET Number = REPLACE(Number,’082′,’+6282′);

ELSEIF (SELECT LEFT(NEW.Number,3))=083 THEN

UPDATE pbk_copy
SET Number = REPLACE(Number,’083′,’+6283′);
ELSEIF (SELECT LEFT(NEW.Number,3))=087 THEN

UPDATE pbk_copy
SET Number = REPLACE(Number,’087′,’+6287′);
ELSEIF (SELECT LEFT(NEW.Number,3))=088 THEN

UPDATE pbk_copy
SET Number = REPLACE(Number,’088′,’+6288′);

ELSEIF (SELECT LEFT(NEW.Number,3))=089 THEN

UPDATE pbk_copy
SET Number = REPLACE(Number,’089′,’+6289′);

ELSEIF (SELECT LEFT(NEW.Number,2))=03 THEN

UPDATE pbk_copy
SET Number = REPLACE(Number,’03’,’+623′);

ELSEIF (SELECT LEFT(NEW.Number,2))=02 THEN

UPDATE pbk_copy
SET Number = REPLACE(Number,’02’,’+622′);

END IF;

END;
$$

DELIMITER ;

2.)  DeletePbk –>

DELIMITER $$

USE `sms2`$$

DROP TRIGGER /*!50032 IF EXISTS */ `DeletePbk`$$

CREATE
/*!50017 DEFINER = ‘dimas’@’%’ */
TRIGGER `DeletePbk` AFTER DELETE ON `pbk`
FOR EACH ROW BEGIN

DELETE FROM pbk_copy WHERE pbk_copy.id_pbk=OLD.id_pbk;

END;
$$

DELIMITER ;

3.) UpdatePbk –>

DELIMITER $$

USE `sms2`$$

DROP TRIGGER /*!50032 IF EXISTS */ `UpdatePbk`$$

CREATE
/*!50017 DEFINER = ‘dimas’@’%’ */
TRIGGER `UpdatePbk` AFTER UPDATE ON `pbk`
FOR EACH ROW BEGIN

UPDATE pbk_copy

SET pbk_copy.NAME = NEW.NAME,
pbk_copy.GroupID = NEW.GroupID,
pbk_copy.Number = NEW.Number,
pbk_copy.id_user = NEW.id_user
WHERE id_pbk = NEW.id_pbk;

END;
$$

DELIMITER ;