{ Berbagi, Menulis, Dan Mengajar } Ilmu… » 7. Operating System » Trigger All Fix 13092011 –> CopyPbk,DeletePbk,UpdatePbk
Trigger All Fix 13092011 –> CopyPbk,DeletePbk,UpdatePbk
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 ;
Filed under: 7. Operating System