Contoh Trigger, View dan Insert Dalam Database MySql

Konten [Tampil]

Trigger 

- merupakan perintah SQL secara otomatis yang diasosiaikan sbh table dan akan aktif ketika sebuah even terjadi
- terjadi jika ada eksekusi insert, update, delete
- dipicu oleh before and after

Sintaks

- CREATE [OR REPLACE] TRIGGER trigger_name
  Timing
  Event1 [OR event2 OR event3]
  ON objectname
  [[REFERENCING OLD AS old | NEW AS new]
  FOR EACH RIW
  [WHEN(ondition)]]
  Trigger_body

Trigger_name harus unik

TIming = waktu Before | After
Event = DML
Object_name = penentuan objek database misal nama table
Referencing utk memberi nama lain [new |old]
for each row trigger baris
when = menerapkan kondisi yang akan di eksekusi
Trigger _body = [call]

-----------------------------------------------------------------------
CONTOH :

CREATE TABLE PRODUCT_PRICE_HISTORY
(
PRODUCT_ID NUMBER(5),
PRODUCT_NAME VARCHAR2(32),
SUPPLIER_NAME VARCHAR2(32),
UNIT_PRICE NUMBER(7,2));

CREATE TABLE PRODUCT
(
PRODUCT_ID NUMBER(5),
PRODUCT_NAME VARCHAR2(32),
SUPPLIER_NAME VARCHAR2(32),
UNIT_PRICE NUMBER(7,2));
-----------------------------------------------------------------------

CREATE OR REPLACE TRIGGER PRICE_HISTORY_trigger
BEFORE UPDATE OF UNIT_PRICE
ON PRODUCT
FOR EACH ROW
BEGIN
INSERT INTO PRODUCT_PRICE_HISTORY VALUES
(:OLD.PRODUCT_ID, :OLD.PRODUCT_NAME,
 :OLD.SUPPLIER_NAME, :OLD.UNIT_PRICE);
END;
-----------------------------------------------------------------------

VIEW

Merupakan table virtual/bayangan. Field berasal dari table.
Bedanya di table bisa mengupdate/delete, View tidak bisa

CONTOH :

CREATE[OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery/query

CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

describe empvu80
select * from empvu80


INPUT TRANSAKSI
--------------------------
- PEMBELIAN
INSERT INTO PEMBELIAN VALUES
(11001, '22/08/2014', 310001, 2000000)

- DETAIL PEMBELIAN
INSERT INTO D_PEMBELIAN VALUES
(11001, 100001, 'KANGKUNG CAH', 'MAKANAN', 40000, 50, 2000000)

-PENUALAN
INSERT INTO PENJUALAN VALUES
(12001, '18/09/2014', 210002, 600000)

-DETAIL PENJUALAN
INSERT INTO D_PENJUALAN VALUES
(12001, 100003, 'ES SEREH', 'MINUMAN', 6000, 5, 30000)
INSERT INTO D_PENJUALAN VALUES
(12001, 100003, 'NASI KARUHUN', 'MAKANAN', 6000, 5, 30000)

Share this post