Trigger, Sequence, View & Package

Tujuan pembelajaran

1. Membuat seq_penjualan_demo mulai 200 dan menjelaskan NEXTVAL/CURRVAL per sesi.
2. Membedakan view sederhana yang dapat diperbarui dengan view agregasi read-only.
3. Membuat trigger BEFORE/AFTER untuk INSERT/UPDATE/DELETE dengan :OLD/:NEW.
4. Merekam audit stok tanpa COMMIT dan membuktikan rollback audit.
5. Menyusun specification/body pkg_latihan_demo dan memeriksa USER_ERRORS.

Pokok bahasan / praktikum

Prasyarat: Lima tabel dan seed modul 02/03, procedure/function modul 12 serta transaksi modul 11. Buat objek modul ini sebelum uji DML; modul 14 memakai sequence, aggregate view, audit table, dan trigger berikut.

Video pembelajaran

Video utama — fokus bab ini

Pembuatan trigger Oracle dengan SQL Developer. Tiga referensi tambahan melengkapi sequence, view dan package specification/body.

How to Create Oracle Trigger Using Oracle SQL Developer
Simple Oracle DB · Buka video utama di YouTube

Video tambahan sesuai subtopik

1. SQL tutorial 61: SEQUENCE in Oracle Database By Manish Sharma RebellionRider
   Manish Sharma

   Fokus: Oracle sequence: pembuatan dan penggunaan nomor untuk primary key.

2. Indexes and Views in Oracle SQL | Oracle Tutorial | SQL Tutorial | SQL Developer | #oracle #sql
   Raj Cloud Technologies Online Job-Based Training

   Fokus: Views dalam Oracle SQL.

   Topik index pada video bersifat tambahan; fokuskan perhatian pada bagian view.

3. PL/SQL Package with Example | Package Spec and Body
   Explore IT

   Fokus: Package specification dan package body Oracle PL/SQL dengan contoh.

Batas cakupan dan panduan menonton: Video utama tidak mewakili seluruh bab13. NEXTVAL/CURRVAL, audit :OLD/:NEW, pembatasan view dan package dipelajari bersama kode Oracle pada halaman.

Video merupakan pendamping, bukan pengganti keseluruhan bab. Pemeriksaan relevansi memakai transkrip yang tersedia, deskripsi pembuat dan bab waktu; bukan klaim seluruh durasi telah ditonton. Versi/editor atau bahasa dapat berbeda. Praktikum tetap mengikuti SQL/PLSQL Oracle pada halaman dan pilihan XE atau FreeSQL login. Jika pemutar dibatasi oleh YouTube, gunakan tautan Buka video.

Praktikum Oracle XE atau FreeSQL

Gunakan FreeSQL dengan login atau SQL Developer yang terhubung ke XE. Baca panduan dua jalur praktikum. Jalankan kode sesuai urutan dan keterangan tiap langkah.

Output contoh adalah prediksi berdasarkan dataset, bukan bukti bahwa kode telah dijalankan pada akun Anda. Periksa koneksi, hak membuat objek, autocommit dan panel DBMS_OUTPUT. Slash pada baris sendiri adalah pemisah mode skrip klien tertentu, bukan statement PL/SQL.

Gunakan tabel pertemuan02 dan data pertemuan03 atau setup index sekali. Periksa keadaan data bila latihan sebelumnya pernah di-commit. Jangan menjalankan ulang CREATE TABLE/INSERT atau menghapus data secara buta.

SELECT object_name, object_type, status FROM user_objects
WHERE object_name IN ('SEQ_PENJUALAN_DEMO','V_TOTAL_PENJUALAN_DEMO','AUDIT_STOK_DEMO','TRG_AUDIT_STOK_DEMO','PKG_LATIHAN_DEMO')
ORDER BY object_name, object_type;

-- Sekali saja, hanya ketika belum ada.
CREATE SEQUENCE seq_penjualan_demo
 START WITH 200 INCREMENT BY 1 MAXVALUE 99999999 NOCYCLE NOCACHE;
SELECT seq_penjualan_demo.NEXTVAL AS nomor_baru FROM dual;
SELECT seq_penjualan_demo.CURRVAL AS nomor_sesi FROM dual;

CREATE OR REPLACE VIEW v_produk_tersedia_demo AS
 SELECT id_produk,nama_produk,harga,stok FROM produk_demo WHERE stok > 0
 WITH CHECK OPTION;
CREATE OR REPLACE VIEW v_total_penjualan_demo AS
 SELECT p.id_jual,p.id_pelanggan,p.tanggal,p.status,
        NVL(SUM(d.jumlah*d.harga),0) AS total
 FROM penjualan_demo p LEFT JOIN detail_jual_demo d ON d.id_jual=p.id_jual
 GROUP BY p.id_jual,p.id_pelanggan,p.tanggal,p.status
 WITH READ ONLY;
SELECT * FROM v_total_penjualan_demo ORDER BY id_jual;
SELECT column_name,updatable FROM user_updatable_columns
 WHERE table_name='V_TOTAL_PENJUALAN_DEMO' ORDER BY column_name;

-- CREATE TABLE sekali saja ketika belum ada.
CREATE TABLE audit_stok_demo (
 id_produk NUMBER(4) NOT NULL,
 operasi VARCHAR2(6) NOT NULL CHECK (operasi IN ('INSERT','UPDATE','DELETE')),
 stok_lama NUMBER(6),
 stok_baru NUMBER(6),
 waktu TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
 pelaku VARCHAR2(128) NOT NULL
);
CREATE OR REPLACE TRIGGER trg_audit_stok_demo
AFTER INSERT OR UPDATE OR DELETE ON produk_demo
FOR EACH ROW
BEGIN
 IF INSERTING THEN
  INSERT INTO audit_stok_demo(id_produk,operasi,stok_lama,stok_baru,pelaku)
  VALUES(:NEW.id_produk,'INSERT',NULL,:NEW.stok,USER);
 ELSIF DELETING THEN
  INSERT INTO audit_stok_demo(id_produk,operasi,stok_lama,stok_baru,pelaku)
  VALUES(:OLD.id_produk,'DELETE',:OLD.stok,NULL,USER);
 ELSIF UPDATING THEN
  IF :OLD.stok <> :NEW.stok THEN
   INSERT INTO audit_stok_demo(id_produk,operasi,stok_lama,stok_baru,pelaku)
   VALUES(:NEW.id_produk,'UPDATE',:OLD.stok,:NEW.stok,USER);
  END IF;
 END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_validasi_produk_demo
BEFORE INSERT OR UPDATE OR DELETE ON produk_demo
FOR EACH ROW
BEGIN
 IF INSERTING OR UPDATING THEN
  IF :NEW.stok IS NULL OR :NEW.stok < 0 OR :NEW.stok <> TRUNC(:NEW.stok) THEN
   RAISE_APPLICATION_ERROR(-20020,'Stok harus integer nonnegatif');
  END IF;
  IF :NEW.harga IS NULL OR :NEW.harga <= 0 THEN
   RAISE_APPLICATION_ERROR(-20021,'Harga produk harus positif');
  END IF;
 ELSE
  DBMS_OUTPUT.PUT_LINE('DELETE produk ' || :OLD.id_produk);
 END IF;
END;
/

CREATE OR REPLACE PACKAGE pkg_latihan_demo AS
 FUNCTION label_produk(p_nama IN VARCHAR2,p_stok IN NUMBER) RETURN VARCHAR2;
 PROCEDURE tampil_produk;
END pkg_latihan_demo;
/
CREATE OR REPLACE PACKAGE BODY pkg_latihan_demo AS
 FUNCTION label_produk(p_nama IN VARCHAR2,p_stok IN NUMBER) RETURN VARCHAR2 IS
 BEGIN
  RETURN NVL(p_nama,'Tanpa nama') || ' | stok=' || TO_CHAR(p_stok);
 END label_produk;
 PROCEDURE tampil_produk IS
 BEGIN
  FOR r IN (SELECT nama_produk,stok FROM produk_demo ORDER BY id_produk) LOOP
   DBMS_OUTPUT.PUT_LINE(label_produk(r.nama_produk,r.stok));
  END LOOP;
 END tampil_produk;
END pkg_latihan_demo;
/
BEGIN
 pkg_latihan_demo.tampil_produk;
END;
/
SELECT pkg_latihan_demo.label_produk('Kopi',20) AS label FROM dual;

-- Transaksi mandiri: jangan ada pekerjaan penting yang tertunda.
DECLARE
 v_sebelum NUMBER;
 v_sesudah NUMBER;
 v_ada NUMBER;
BEGIN
 SELECT COUNT(*) INTO v_sebelum FROM audit_stok_demo;
 SELECT COUNT(*) INTO v_ada FROM produk_demo WHERE id_produk=99;
 IF v_ada > 0 THEN RAISE_APPLICATION_ERROR(-20022,'ID produk uji 99 sudah dipakai'); END IF;
 UPDATE v_produk_tersedia_demo SET stok=stok+1 WHERE id_produk=11;
 INSERT INTO produk_demo(id_produk,nama_produk,harga,stok) VALUES(99,'Produk uji',1000,2);
 UPDATE produk_demo SET stok=3 WHERE id_produk=99;
 DELETE FROM produk_demo WHERE id_produk=99;
 SELECT COUNT(*) INTO v_sesudah FROM audit_stok_demo;
 IF v_sesudah-v_sebelum <> 4 THEN RAISE_APPLICATION_ERROR(-20023,'Jumlah audit tidak sesuai'); END IF;
 DBMS_OUTPUT.PUT_LINE('Audit tambahan sementara=' || (v_sesudah-v_sebelum));
 ROLLBACK;
 SELECT COUNT(*) INTO v_sesudah FROM audit_stok_demo;
 IF v_sesudah <> v_sebelum THEN RAISE_APPLICATION_ERROR(-20024,'Audit tidak kembali sesudah rollback'); END IF;
 DBMS_OUTPUT.PUT_LINE('Rollback data dan audit dibuktikan');
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
END;
/
SELECT id_produk,stok FROM produk_demo WHERE id_produk IN (11,99) ORDER BY id_produk;

DECLARE
 v_kode NUMBER;
BEGIN
 SAVEPOINT batas_view_13;
 BEGIN
  UPDATE v_produk_tersedia_demo SET stok=0 WHERE id_produk=11;
  RAISE_APPLICATION_ERROR(-20996,'CHECK OPTION tidak menolak perubahan');
 EXCEPTION WHEN OTHERS THEN
  v_kode := SQLCODE;
  ROLLBACK TO batas_view_13;
  IF v_kode <> -1402 THEN RAISE; END IF;
  DBMS_OUTPUT.PUT_LINE('CHECK OPTION menolak stok nol');
 END;
 BEGIN
  EXECUTE IMMEDIATE 'UPDATE v_total_penjualan_demo SET total=1 WHERE id_jual=101';
  RAISE_APPLICATION_ERROR(-20997,'View read-only justru dapat diubah');
 EXCEPTION WHEN OTHERS THEN
  v_kode := SQLCODE;
  ROLLBACK TO batas_view_13;
  IF v_kode NOT IN (-42399,-1732) THEN RAISE; END IF;
  DBMS_OUTPUT.PUT_LINE('View agregat/read-only menolak UPDATE');
 END;
 ROLLBACK;
EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE;
END;
/

SELECT name, type, line, position, text
FROM user_errors
WHERE name IN ('TRG_AUDIT_STOK_DEMO','TRG_VALIDASI_PRODUK_DEMO','PKG_LATIHAN_DEMO')
ORDER BY name, type, sequence;
SELECT object_name,object_type,status FROM user_objects WHERE object_name IN ('TRG_AUDIT_STOK_DEMO','TRG_VALIDASI_PRODUK_DEMO','PKG_LATIHAN_DEMO','V_TOTAL_PENJUALAN_DEMO') ORDER BY object_name,object_type;

Tugas / latihan

1. Pada XE lokal ATAU FreeSQL schema sendiri, buat objek baru sekali dan lampirkan daftar objek/status serta USER_ERRORS aktual.
2. Bandingkan NEXTVAL/CURRVAL dalam sesi yang sama dan jelaskan mengapa rollback tidak memulihkan nomor. Tidak wajib membuka dua sesi bila editor tidak mendukung.
3. Uji view sederhana dan dokumentasikan penolakan UPDATE pada view agregat dalam transaksi tersendiri; rollback perubahan.
4. Jalankan audit INSERT/UPDATE/DELETE menggunakan ID produk kosong; buktikan audit ikut rollback tanpa menghapus seed.
5. Presentasikan pemisahan specification/body pkg_latihan_demo dan tandai empat objek yang digunakan kembali pada modul 14.

Pertanyaan analisis

1. Mengapa audit DELETE tidak diberi foreign key ke produk, dan apa batas integritas pilihan ini?
2. Mengapa CURRENT session CURRVAL dan celah nomor tidak boleh dianggap bukti transaksi berhasil atau gagal?

Pengumpulan

Tugas boleh dikerjakan di Oracle XE lokal atau FreeSQL dengan login; cukup pilih salah satu. Kumpulkan satu laporan melalui LMS/kanal yang ditetapkan dosen. Cantumkan Nama, NPM, pertemuan dan lingkungan yang dipakai. Sertakan kode SQL/PLSQL sebagai teks, output aktual atau pesan error, serta jawaban analisis.

Jika FreeSQL menyediakan tautan berbagi yang dapat diakses dosen, tautan boleh ditambahkan sebagai bukti pendamping, bukan pengganti hasil eksekusi yang terdokumentasi. Jangan membagikan password, token atau koneksi pribadi.

Hasil yang belum dieksekusi diberi label “prediksi”. Screenshot ilustrasi browser bukan bukti praktikum Oracle.

Rubrik penilaian

• Ketepatan konsep dan rancangan: 20%
• Kebenaran query/program: 30%
• Pengujian normal, batas dan gagal: 20%
• Analisis serta penjelasan hasil: 20%
• Identitas dan kerapian laporan: 10%

Referensi resmi

• Oracle 19c — CREATE SEQUENCE
• Oracle 19c — CREATE VIEW
• Oracle 19c — PL/SQL Triggers
• Oracle 19c — PL/SQL Packages
• Oracle 19c — USER_ERRORS

Panduan XE21c digunakan untuk konteks instalasi lokal; dokumentasi SQL/PLSQL untuk konsep bahasa. Versi FreeSQL/editor dapat berbeda, sehingga periksa fitur dan pesan aktual.