TUGAS ORACLE

ORACLE

INSERT - INSERT 

CREATE TABLE  II_BARANG_MSK(      KD_BRG CHAR(5) NOT NULL PRIMARY KEY, NAMA_BRG VARCHAR2(25), TGL_MSK DATE ); CREATE TABLE  II_HISTORY(  KD_BRG CHAR(5),  TGL_MSK DATE ); create or replace trigger TR_II after INSERT on II_BARANG_MSK for each row begin insert into II_HISTORY values (:new.KD_BRG,:new.TGL_MSK); end; INSERT INTO II_BARANG_MSK VALUES ('AA001','RAM','1-mar-13') INSERT INTO II_BARANG_MSK VALUES ('AA002','VGA','3-mar-13') SELECT*FROM II_BARANG_MSK SELECT*FROM II_HISTORY

 INSERT – UPDATE

  

CREATE TABLE IU_SUPLAI ( KD_BRG CHAR(5) NOT NULL, NAMA_BRG VARCHAR(25), JMLH INT ); CREATE TABLE IU_STOK ( KD_BRG CHAR(5) NOT NULL, NAMA_BRG VARCHAR(25), JMLH_STOK INT ); CREATE OR REPLACE TRIGGER TR_IU AFTER INSERT ON IU_SUPLAI FOR EACH ROW BEGIN UPDATE IU_STOK SET JMLH_STOK=JMLH_STOK+:NEW.JMLH WHERE KD_BRG=:NEW.KD_BRG; END; INSERT INTO IU_STOK VALUES ('VG001','VGA','0') INSERT INTO IU_STOK VALUES ('RM001','RAM','0') SELECT* FROM IU_STOK SELECT* FROM IU_SUPLAI INSERT INTO IU_SUPLAI VALUES ('VG001', 'VGA', '25') Insert – delete

CREATE TABLE ID_LIST_DIPINJAM ( KD_GAME CHAR(5) NOT NULL, NAMA_GAME VARCHAR(25) ); CREATE TABLE ID_LIST_KEMBALI ( KD_GAME CHAR(5) NOT NULL, NAMA_GAME VARCHAR(25) ); CREATE OR REPLACE TRIGGER TR_ID AFTER INSERT ON ID_LIST_KEMBALI FOR EACH ROW BEGIN DELETE ID_LIST_DIPINJAM WHERE KD_GAME=:NEW.KD_GAME; END; INSERT INTO ID_LIST_DIPINJAM VALUES ('GMOO1','PES 2014') INSERT INTO ID_LIST_DIPINJAM VALUES ('GMOO2','GTA 5') SELECT* FROM ID_LIST_DIPINJAM SELECT* FROM ID_LIST_KEMBALI INSERT INTO ID_LIST_KEMBALI VALUES ('GMOO2','GTA 5') Update – insert

  

CREATE TABLE UI_GAME ( KODE_GAME CHAR(5) NOT NULL, NAMA_GAME VARCHAR(20) ); CREATE TABLE UI_BACKUP ( KODE_BACKUP CHAR(5) NOT NULL, NAMA_GAME VARCHAR(20) ); CREATE TRIGGER TR_BACKUP BEFORE UPDATE ON UI_GAME FOR EACH ROW BEGIN INSERT INTO UI_BACKUP VALUES (:OLD.KODE_GAME,:OLD.NAMA_GAME); END; / INSERT INTO UI_GAME VALUES('GM001','FARCRY 3') INSERT INTO UI_GAME VALUES('GM002','FIFA 2014') SELECT * FROM UI_GAME UPDATE UI_GAME SET NAMA_GAME='FARCRY 4' WHERE NAMA_GAME='FARCRY 3' SELECT * FROM UI_BACKUP Update – update

create table uu_member( id varchar(5) primary key, nama varchar(25), alamat varchar(50) ); create table uu_daftar_pinjam( id varchar(5) primary key, nama varchar(25), alamat varchar(50), nama_buku varchar(25) ); create or replace trigger uu after update on uu_member for each row begin update uu_daftar_pinjam set alamat=:new.alamat where id=:new.id; end; insert into uu_member values ('a01','hanin','graha mutiara') insert into uu_daftar_pinjam values ('a01','hanin','graha mutiara','basis data') update uu_member set alamat='pondok jati' where alamat='graha mutiara' select* from uu_member select* from uu_daftar_pinjam

Update – delete

create table ud_game( kd_game varchar(5) primary key, nama_game varchar(25), status varchar(6) ); create table ud_game_keluar( kd_game varchar(5) primary key, id_peminjam varchar(5) ); create or replace trigger tr_ud after update on ud_game for each row begin delete ud_game_keluar where kd_game=:new.kd_game; end; insert into ud_game values ('a01','pes 2014','keluar') insert into ud_game values ('a02','fifa 2014','keluar') insert into ud_game_keluar values ('a01','001') insert into ud_game_keluar values ('a02','200') select* from ud_game select* from ud_game_keluar update ud_game set status='ada' where kd_game='a01'

Delete – insert

  

create table di_barang( kd_brg varchar(5)not null, nama_brg varchar(25), harga int ); create table di_recycle( kd_brg varchar(5)not null, nama_brg varchar(25), harga int ); create or replace trigger tr_di after delete on di_barang for each row begin insert into di_recycle values (:old.kd_brg, :old.nama_brg, :old.harga); end; insert into di_barang values (‘a01’,’vga’,’1000000’) insert into di_barang values (‘a02’,’ram’,’700000’) delete from di_barang where kd_brg=’a01’ select*from di_barang select*from di_recycle Delete – update

create table du_daftar_brg_dijual( kd_brg varchar(5)not null, nama_brg varchar(25), harga int ); create table du_status_brg( kd_brg varchar(5)not null, status varchar(8) ); create or replace trigger tr_du after delete on du_daftar_brg_dijual for each row begin update du_status_brg set status='sold out' where kd_brg=:old.kd_brg; end; insert into du_daftar_brg_dijual values ('a01','lemari','400000') insert into du_daftar_brg_dijual values ('a02','meja','300000') insert into du_status_brg values ('a01','dijual') insert into du_status_brg values ('a02','dijual') select*from du_daftar_brg_dijual select*from du_status_brg delete from du_daftar_brg_dijual where kd_brg='a01' Delete – delete

create table dd_barang_stok( kd_brg varchar(5)primary key not null, nama_brg varchar(25), harga int ); create table dd_daftar_jual( kd_brg varchar(5)primary key not null, nama_brg varchar(25), harga int ); create or replace trigger tr_dd after delete on dd_barang_stok for each row begin delete dd_daftar_jual where kd_brg=:old.kd_brg; end; insert into dd_barang_stok values('a01','vga','1000000') insert into dd_barang_stok values('a02','power suply','900000') insert into dd_daftar_jual values('a01','vga','1000000') insert into dd_daftar_jual values('a02','power suply','900000') delete dd_barang_stok where kd_brg='a01' select* from dd_barang_stok select* from dd_daftar_jual Branching

  

create table b_mhs( npm char(10)primary key not null, nama varchar(25), matkul varchar(25), nilai_angka int ); create table b_nilai( npm char(10)not null, matkul varchar(25), nilai_abjad varchar(2) ); create or replace trigger tr_b after insert on b_mhs for each row begin if :new.nilai_angka <45 then insert into b_nilai values (:new.npm, :new.matkul, 'E'); elsif :new.nilai_angka between 45 and 55 then insert into b_nilai values (:new.npm, :new.matkul, 'D'); elsif :new.nilai_angka between 56 and 59 then insert into b_nilai values (:new.npm, :new.matkul, 'C'); elsif :new.nilai_angka between 60 and 63 then insert into b_nilai values (:new.npm, :new.matkul, 'C+'); elsif :new.nilai_angka between 64 and 67 then insert into b_nilai values (:new.npm, :new.matkul, 'B-'); elsif :new.nilai_angka between 68 and 71 then insert into b_nilai values (:new.npm, :new.matkul, 'B'); elsif :new.nilai_angka between 72 and 75 then insert into b_nilai values (:new.npm, :new.matkul, 'B+'); elsif :new.nilai_angka between 76 and 79 then insert into b_nilai values (:new.npm, :new.matkul, 'A-'); elsif :new.nilai_angka between 80 and 100 then insert into b_nilai values (:new.npm, :new.matkul, 'A'); end if; end; insert into b_mhs values ('1134010053', 'hanin', 'basis data', '80') insert into b_mhs values ('1134010054', 'kukuh', 'basis data', '70') select* from b_mhs select* from b_nilai Looping

create table l_sewa ( id varchar(5) primary key not null, nama varchar(25), no_hp varchar(12), jam_sewa int ); create table l_lapangan ( id varchar(5), nama varchar(25), jam_ke int ); create or replace trigger tr_l after insert on l_sewa for each row declare a number :=1; b int := :new.jam_sewa; begin loop insert into l_lapangan values (:new.id, :new.nama, a); a:=a+1; exit when a>b; end loop; end; insert into l_sewa values ('001', 'agung', '085123456789', '4') select * from l_sewa select * from l_lapangan

Message

create table m_mhs( npm char(10) primary key not null, nama varchar(25), alamat varchar(30) ); create or replace trigger tr_m before delete on m_mhs for each row begin RAISE_APPLICATION_ERROR(-20001,'data tidak dapat dihapus XD'); end; insert into m_mhs values ('1134010053','hanin','graha mutiara') insert into m_mhs values ('1134010054','kukuh','candi') select*from m_mhs delete from m_mhs where npm='1134010053'