mirror codeshare/2B47kb

-- Untuk Menampilkan Database Object
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER'

SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE');

SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TRIGGER') AND OWNER = 'JTK';

-- Untuk Membuat User Baru (Otomatis Membuat Schema Baru)
CREATE USER jtk
  IDENTIFIED BY jtkway;

GRANT CONNECT, RESOURCE, DBA TO jtk;

-- Untuk Menampilkan Schema
SELECT username AS schema_name
FROM sys.dba_users
ORDER BY username;

--- SIMULASI TRIGGER
CREATE TABLE produk (
    id_produk NUMERIC NOT NULL,
    produk VARCHAR2(20) NOT NULL,
    stok NUMERIC,
    satuan VARCHAR2(20),
    CONSTRAINT produk_pk PRIMARY KEY (id_produk)
);

CREATE TABLE pesanan (
    id_pesanan NUMERIC NOT NULL,
    id_produk NUMERIC NOT NULL,
    jumlah NUMERIC NOT NULL,
    tgl_pesanan DATE,
    CONSTRAINT pesanan_pk PRIMARY KEY (id_pesanan)
);

INSERT INTO produk VALUES (1, 'Pocky', 25, 'pcs');
INSERT INTO produk VALUES (2, 'Nescafe', 15, 'pcs');
INSERT INTO produk VALUES (3, 'Chitato', 10, 'pcs');
INSERT INTO produk VALUES (4, 'Makaroni', 5, 'box');

CREATE OR REPLACE TRIGGER trg_update_stok
AFTER INSERT OR DELETE OR UPDATE ON pesanan
FOR EACH ROW
BEGIN
   IF INSERTING THEN
    UPDATE produk SET stok = (stok - :NEW.jumlah) WHERE id_produk = :NEW.id_produk;
   ELSIF DELETING THEN
    UPDATE produk SET stok = (stok + :OLD.jumlah) WHERE id_produk = :OLD.id_produk;
   ELSIF UPDATING THEN
    UPDATE produk SET stok = ((stok + :OLD.jumlah) - :NEW.jumlah) WHERE id_produk = :OLD.id_produk;
   END IF;
END;