Assalamualaikum

Kali ini kita akan membuat REST API yang akan dipakai oleh aplikasi flutter kita nanti dengan menggunakan Slim Framework

Buat database dengan nama : dbcatatan

Buat Tabel : tb_catatan

CREATE TABLE tb_catatan (
  id int(11) NOT NULL,
  judul varchar(50) DEFAULT NULL,
  catatan text DEFAULT NULL,
  status int(11) DEFAULT 0,
  create_at timestamp NOT NULL DEFAULT current_timestamp(),
  update_at timestamp NOT NULL DEFAULT current_timestamp()
);

Buat Tabel : tb_catatan_log

CREATE TABLE tb_catatan_log (
  id int(11) NOT NULL,
  id_catatan int(11) DEFAULT NULL,
  method varchar(15) DEFAULT NULL,
  judul varchar(50) DEFAULT NULL,
  catatan text DEFAULT NULL,
  update_at timestamp NOT NULL DEFAULT current_timestamp()
);

Buat view : v_catatan

CREATE VIEW v_catatan AS select * from tb_catatan where status = 0 order by id DESC LIMIT 100

Buat view : v_catatan_log

CREATE VIEW v_catatan_log AS select * from tb_catatan_log order by id DESC LIMIT 100

Buat Trigger : create

CREATE TRIGGER create AFTER INSERT ON tb_catatan FOR EACH ROW INSERT INTO tb_catatan_log(
    id_catatan, method,judul, catatan, update_at
)VALUES(      
    NEW.id,
    "INSERT",
    NEW.judul,
    NEW.catatan,
    NOW()
);

Buat Trigger : delete

CREATE TRIGGER delete BEFORE DELETE ON tb_catatan FOR EACH ROW INSERT INTO tb_catatan_log(
    id_catatan, method,judul, catatan, update_at
)VALUES(      
    OLD.id,
    "DELETE",
    OLD.judul,
    OLD.catatan,
    NOW()
);

Buat Trigger : BEFOREUPDATE

CREATE TRIGGER beforeupdate BEFORE UPDATE ON tb_catatan FOR EACH ROW INSERT INTO tb_catatan_log(
    id_catatan, method,judul, catatan, update_at
)VALUES(      
    OLD.id,
    "BEFOREUPDATE",
    OLD.judul,
    OLD.catatan,
    NOW()
);

Buat Trigger : AFTERUPDATE

CREATE TRIGGER afterupdate AFTER UPDATE ON tb_catatan FOR EACH ROW INSERT INTO tb_catatan_log(
    id_catatan, method,judul, catatan, update_at
)VALUES(      
    NEW.id,
    "UPDATE",
    NEW.judul,
    NEW.catatan,
    NOW()
);

ok, kita lihat hasilnya

Semoga Bermanfaat