.::: Sample Create VIEW, Routine / Store Procedure, Trigger with DEFINER=`simple_admin`@`%` in MariaDB :::.

 

correlation https://teguhth.blogspot.com/2019/04/study-kasus-praktis-belajar-query-mysql.html#more

1. create user 

SELECT Host, User FROM mysql.user WHERE User='simple_admin';

create user 'simple_admin'@'%' identified by 'simple_admin';

GRANT CREATE ROUTINE, ALTER ROUTINE, TRIGGER, CREATE VIEW
ON teguhth.* TO `simple_admin`@`%`;

GRANT EXECUTE ON PROCEDURE teguhth.sp_insert_pembelian
TO 'simple_admin'@'%';

GRANT ALL PRIVILEGES ON *.* TO 'simple_admin'@'%' WITH GRANT OPTION;

flush PRIVILEGES;

Kalau DEFINER tidak ada ? ERROR 1449
 
2. sample for table 

create table pembelian(
KODE_PEMBELIAN char(10),
KODE_BARANG char(6),KODE_CUSTOMER char(6),
TANGGAL_PEMBELIAN date,
JUMLAH_PEMBELIAN decimal(4),
primary key(KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER),
foreign key(KODE_BARANG)references barang(KODE_BARANG),
foreign key(KODE_CUSTOMER)references customer(KODE_CUSTOMER));

3. ROUTINE (Stored Procedure)
Contoh: procedure insert data ke tabel pembelian

DELIMITER $$

CREATE DEFINER=`simple_admin`@`%`
PROCEDURE sp_insert_pembelian (
    IN p_kode_pembelian CHAR(10),
    IN p_kode_barang CHAR(6),
    IN p_kode_customer CHAR(6),
    IN p_tanggal DATE,
    IN p_jumlah DECIMAL(4)
)
BEGIN
    INSERT INTO pembelian (
        KODE_PEMBELIAN,
        KODE_BARANG,
        KODE_CUSTOMER,
        TANGGAL_PEMBELIAN,
        JUMLAH_PEMBELIAN
    )
    VALUES (
        p_kode_pembelian,
        p_kode_barang,
        p_kode_customer,
        p_tanggal,
        p_jumlah
    );
END$$

DELIMITER ;

CALL sp_insert_pembelian('BEL-E001','ELK-01','J-0001','2002-05-20',3 );

CALL sp_insert_pembelian('BEL-E001','ELK-01','J-0001','2002-05-20',10 );

3.1 check existing routine 

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DEFINER
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='teguhth'
  AND DEFINER='simple_admin@%';
 
3.2 if additonal for ignore or insert 

DELIMITER $$

CREATE DEFINER=`simple_admin`@`%`
PROCEDURE sp_insert_pembelian (
    IN p_kode_pembelian CHAR(10),
    IN p_kode_barang CHAR(6),
    IN p_kode_customer CHAR(6),
    IN p_tanggal DATE,
    IN p_jumlah DECIMAL(4)
)
BEGIN
    INSERT IGNORE INTO pembelian (
        KODE_PEMBELIAN,
        KODE_BARANG,
        KODE_CUSTOMER,
        TANGGAL_PEMBELIAN,
        JUMLAH_PEMBELIAN
    )
    VALUES (
        p_kode_pembelian,
        p_kode_barang,
        p_kode_customer,
        p_tanggal,
        p_jumlah
    );

    -- INFO hasil insert
    IF ROW_COUNT() = 1 THEN
        SELECT 
            'INSERTED' AS status,
            p_kode_pembelian AS kode_pembelian;
        select * from pembelian where kode_pembelian=p_kode_pembelian;
    ELSE
        SELECT 
            'IGNORED (DUPLICATE)' AS status,
            p_kode_pembelian AS kode_pembelian;
        select * from pembelian where kode_pembelian=p_kode_pembelian;
    END IF;
END$$

DELIMITER ;

CALL sp_insert_pembelian('BEL-E001','ELK-01','J-0001','2002-05-20',3 );
CALL sp_insert_pembelian('BEL-E001','ELK-01','J-0001','2002-05-20',10 );
 

4. TRIGGER
Contoh: trigger validasi sebelum insert

(mencegah JUMLAH_PEMBELIAN <= 0)

DELIMITER $$

CREATE DEFINER=`simple_admin`@`%`
TRIGGER trg_pembelian_bi
BEFORE INSERT ON pembelian
FOR EACH ROW
BEGIN
    IF NEW.JUMLAH_PEMBELIAN <= 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'JUMLAH_PEMBELIAN harus lebih besar dari 0';
    END IF;
END$$

DELIMITER ;

4.1 check existing routine  Cek dengan query kamu

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA='teguhth'
  AND DEFINER='simple_admin@%';
  
CALL sp_insert_pembelian('BEL-E051','ELK-02','B-0002','2002-08-02',4);

CALL sp_insert_pembelian('BEL-E055','ELK-02','B-0002','2002-08-02',0);
  

5. VIEW
Contoh: view laporan pembelian

CREATE OR REPLACE
ALGORITHM=UNDEFINED
DEFINER=`simple_admin`@`%`
SQL SECURITY DEFINER
VIEW v_pembelian AS
SELECT
    KODE_PEMBELIAN,
    KODE_BARANG,
    KODE_CUSTOMER,
    TANGGAL_PEMBELIAN,
    JUMLAH_PEMBELIAN
FROM pembelian;
 

5.1 Cek dengan query kamu

SELECT TABLE_SCHEMA, TABLE_NAME, DEFINER
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA='teguhth'
  AND DEFINER='simple_admin@%';
  
select * from  v_pembelian;
  

6. query to check all

SET @dbname='teguhth';
#-- routine
SELECT R.ROUTINE_SCHEMA, R.ROUTINE_NAME, R.ROUTINE_TYPE, R.DATA_TYPE,R.`DEFINER`,R.*
FROM INFORMATION_SCHEMA.ROUTINES R
WHERE R.ROUTINE_SCHEMA=@dbname AND R.`DEFINER`='simple_admin@%';

#trigger
SELECT T.TRIGGER_SCHEMA, T.TRIGGER_NAME, T.TRIGGER_CATALOG, T.`DEFINER`
FROM INFORMATION_SCHEMA.`TRIGGERS` T
WHERE T.TRIGGER_SCHEMA=@dbname AND T.`DEFINER`='simple_admin@%';

#--view
SELECT V.TABLE_SCHEMA, V.TABLE_NAME,V.`DEFINER`, V.CHARACTER_SET_CLIENT
, CONCAT('CREATE OR REPLACE ALGORITHM=UNDEFINED DEFINER=`sysadmin`@`%` SQL SECURITY DEFINER VIEW ',V.TABLE_NAME,' AS ',V.VIEW_DEFINITION)'script'
#,V.*
 FROM information_schema.VIEWS V 
WHERE V.TABLE_SCHEMA=@dbname AND V.`DEFINER`='simple_admin@%';