Coding Trigger Album

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `toko_dbc110116`.`insertAlbum`$$

CREATE TRIGGER `toko_dbc110116`.`insertAlbum` AFTER INSERT on `toko_dbc110116`.`album`
FOR EACH ROW BEGIN
insert into log values ('aryudi',now(),'INSERT');
END$$

DELIMITER ;
-------------------------------------

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `toko_dbc110116`.`UpdateAlbum`$$

create trigger `toko_dbc110116`.`UpdateAlbum` AFTER UPDATE on `toko_dbc110116`.`album`
for each row BEGIN
insert into log values ('aryudi',now(),'UPDATE');
END;
$$

DELIMITER ;
------------------------------------

DELIMITER $$

DROP TRIGGER /*!50032 IF EXISTS */ `toko_dbc110116`.`DeleteAlbum`$$

CREATE TRIGGER `toko_dbc110116`.`DeleteAlbum` AFTER DELETE on `toko_dbc110116`.`album`
FOR EACH ROW BEGIN
insert into log values ('aryudi',now(),'DELETE');
END$$

DELIMITER ;

Koding Program Procedure untuk inputDetailPenjualan

Koding Program Procedure untuk inputDetailPenjualan pada SQL

DELIMITER $$

DROP PROCEDURE IF EXISTS `toko_dbc110071`.`inputDetailPenjualan`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `inputDetailPenjualan`(vkode_album varchar(5), vqty smallint, vharga decimal)
BEGIN
DECLARE vkode_penjualan VARCHAR(20);
declare vsubtotal decimal;
set vsubtotal = vqty*vharga;
SET vkode_penjualan = getKodePenjualan(now());
INSERT INTO detail_penjualan VALUES (vkode_penjualan, vkode_album, vqty, vharga, vsubtotal);
select vkode_penjualan, vkode_album,vqty, vharga from detail_penjualan;
    END$$

DELIMITER ;

koding program stored dan function

DELIMITER $$

DROP FUNCTION IF EXISTS `toko_dbc110091`.`getKodepenjualan`$$

CREATE FUNCTION `toko_dbc110091`.`getKodepenjualan`(vTgl_pinjam DATETIME)
RETURNS VARCHAR(10)
   DETERMINISTIC


BEGIN

DECLARE vthn CHAR(2);

DECLARE vbln CHAR(2);

DECLARE vtgl CHAR(2);

DECLARE vUrut INT;


SET vthn=SUBSTR(vTgl_pinjam,3,4);

SET vbln=SUBSTR(vTgl_pinjam,6,6);

SET vtgl=SUBSTR(vTgl_pinjam,9,9);

SELECT IFNULL (MAX(CAST(SUBSTR(Kode_Penjualan, 4, 5) AS UNSIGNED)),0)

FROM detail_penjualan

WHERE vtgl_pinjam LIKE CONCAT (vthn, vbln, vtgl, '%')INTO vUrut;


RETURN CONCAT (vthn, vbln,vtgl, vUrut+1);
   

END$$

DELIMITER ;