Saturday, October 19, 2013

Membuat trigger dengan berbagai macam menu Pada Oracle PART 1

9:09 PM

By: Unknown on 9:09 PM
Share it Please
Oracle
Insert – insert :

Gambar 1.1 Tabel Mahasiswa

Gambar 1.2 Tabel Absensi








Source code :
create table Mahasiswa(
NPM char(10) primary key not null,
Nama varchar(25)
);

create table Absensi(
NPM char(10) primary key not null,
Nama varchar(25),
Absensi varchar(4)
);

create or replace trigger Absen_mhs
after insert on Mahasiswa
for each row
begin
insert into absensi values (:new.NPM, :new.Nama, '100%');
end;
/
insert into Mahasiswa values ('1134010051', 'Pradhita Yudha Negara');
select* from Absensi


Insert – Update


Gambar 1.3 Tabel Stok Sebelum di Update

 
Gambar 1.4 Data Barang yang Dimasukkan


Gambar 1.5 Tabel Stok Setelah di Update

Source code :
create table Pasok (
id_brg varchar(10) primary key not null,
Nama_brg varchar(25),
stok_in int
);

create table Stok (
id_brg varchar(10) primary key not null,
Nama_brg varchar(25),
stok int
);

create or replace trigger tr_pasok
after insert on Pasok
for each row
begin
update Stok
set stok=stok+:new.stok_in
where id_brg=:new.id_brg;
end;


insert into Stok values ('pep00231', 'pepsodent', '0')
insert into Stok values ('pep00232', 'pepsodent cool', '0')
insert into Stok values ('pep00233', 'pepsodent herbal', '0')
insert into Stok values ('pep00234', 'pepsodent sensitif expert', '0')
insert into Stok values ('pep00235', 'pepsodent min', '0')

select* from Stok

insert into Pasok values ('pep00231', 'pepsodent', '50')


Insert – Delete


Gambar 1.6 Tabel Stok


Gambar 1.7 Tabel Stok Setelah di Delete Data

Source code :
create table Pasok1 (
id_brg varchar(10) primary key not null,
Nama_brg varchar(25),
stok_in int
);

create table Stok1 (
id_brg varchar(10) primary key not null,
Nama_brg varchar(25),
stok int
);

create or replace trigger tr_brg1
after insert on Pasok1
for each row
begin
delete Stok1
where id_brg=:new.id_brg;
end;
/
ALTER TRIGGER  "TR_PASOK" ENABLE
/

insert into Pasok1 values ('pep00231', 'pepsodent', '0')
insert into Stok1 values ('pep00232', 'pepsodent cool', '0')
insert into Stok1 values ('pep00233', 'pepsodent herbal', '0')
insert into Stok1 values ('pep00234', 'pepsodent sensitif expert', '0')
insert into Stok1 values ('pep00235', 'pepsodent min', '0')

select* from Stok1


Delete – Update


Gambar 1.8 Tabel B1


Gambar 1.9 Tabel B1 setelah di Delete


Gambar 1.10 Tabel B2

Source code :
create table B1(
id varchar(5)primary key not null,
nama varchar(25),
harga int
);

insert into B1 values ('95001', 'nuvo', '3000')
insert into B1 values ('95002', 'lifeboy', '3000')
insert into B1 values ('95003', 'citra', '4000')
insert into B1 values ('95004', 'sehat', '2500')
insert into B1 values ('95005', 'madicare', '3500')

create table B2(
id char(5),
nama varchar(25),
harga int
);

create or replace trigger B3
before delete on B1
for each row
begin
update B2 set id=:old.id, nama=:old.nama, harga=:old.harga;
end;

delete B1 where id='95002'

select* from B2

Delete – Insert


Gambar 1.11 Tabel B1


Gambar 1.12 Tabel B1 Setelah di Hapus 1 Data


Gambar 1.13 Tabel B1 Setelah di Update

Source code :
create table B1(
id varchar(5)primary key not null,
nama varchar(25),
harga int
);

insert into B1 values ('95001', 'nuvo', '3000')
insert into B1 values ('95002', 'lifeboy', '3000')
insert into B1 values ('95003', 'citra', '4000')
insert into B1 values ('95004', 'sehat', '2500')
insert into B1 values ('95005', 'madicare', '3500')

create table B2(
id char(5),
nama varchar(25),
harga int
);

create or replace trigger B3
before delete on B1
for each row
begin
insert into B2 values (:old.id, :old.nama, :old.harga);
end;

delete B1 where id='95001'

select* from B1


Delete – delete


Gambar Tabel B2


Gambar Tabel B2 setelah di Delete 1 Data

Source code :
create table B1(
id varchar(5)primary key not null,
nama varchar(25),
harga int
);

insert into B2 values ('95001', 'nuvo', '3000')
insert into B2 values ('95002', 'lifeboy', '3000')
insert into B2 values ('95003', 'citra', '4000')
insert into B2 values ('95004', 'sehat', '2500')
insert into B2 values ('95005', 'madicare', '3500')

create table B2(
id char(5),
nama varchar(25),
harga int
);

create or replace trigger B3
before delete on B1
for each row
begin
delete B2 where id=:old.id;
end;

delete B1 where id='95002'

select* from B2

Update – delete

Gambar Tabel M1


Gambar Tabel setelah di Update

Source code :
create table M1(
id varchar(5) primary key,
nama varchar(25),
alamat varchar(50)
);

insert into M2 values ('87671', 'blawung jaya', 'surabaya')
insert into M2 values ('87672', 'menyuntech jaya', 'surabaya')
insert into M2 values ('87673', 'mamouth jaya', 'surabaya')

create table M2(
id varchar(5) primary key,
nama varchar(25),
alamat varchar(50)
);

create or replace trigger M3
after update on M1
for each row
begin
update M2
set nama=:new.nama, alamat=:new.alamat
where id=:new.id;
end;

update M1
set id='87671', nama='prad jaya', alamat='kurang jaya'
where id='87671'

select* from M2

alter trigger "M3" disable

create or replace trigger M4
before update on M1
for each row
begin
delete M2
where id=:old.id;
end;

Update – Insert


Gambar  Tabel MHS


Gambar  History_Mhs

Source code :
create table MHS(
NPM char(10)primary key not null,
Nama varchar(25),
nilai int
);

insert into MHS values ('1134010051', 'pradhita yudha negara', '80')
insert into MHS values ('1134010052', 'galih prakoso', '80')
insert into MHS values ('1134010053', 'hanindyo', '80')

create table History_mhs(
NPM char(10),
Nama varchar(25),
nilai int
);

create or replace trigger his_mhs
after update on MHS
for each row
begin
insert into History_mhs values (:old.NPM, :old.Nama, :old.nilai);
end;

select* from History_mhs

update MHS
set nilai='95'
where NPM='1134010051'

Update – update


Gambar Tabel M2


Source code :
create table M1(
id varchar(5) primary key,
nama varchar(25),
alamat varchar(50)
);

insert into M2 values ('87671', 'blawung jaya', 'surabaya')
insert into M2 values ('87672', 'menyuntech jaya', 'surabaya')
insert into M2 values ('87673', 'mamouth jaya', 'surabaya')

create table M2(
id varchar(5) primary key,
nama varchar(25),
alamat varchar(50)
);

create or replace trigger M3
after update on M1
for each row
begin
update M2
set nama=:new.nama, alamat=:new.alamat
where id=:new.id;
end;

update M1
set id='87671', nama='ahong jaya', alamat='kurang asem'
where id='87671'

select* from M2

Trigger Branching


Gambar Tabel Report

Source code :
create table nilai1(
npm char(10)primary key not null,
nama varchar(25),
nilai_t int
);

create table report(
npm char(10),
nilai_t int,
kognitif varchar(2)
);

drop table report

create or replace trigger tr_rpt
after insert on NILAI1
for each row
begin
if :new.nilai_t <=20 then
insert into report values (:new.npm, :new.nilai_t, 'E');
elsif :new.nilai_t <=40 then
insert into report values (:new.npm, :new.nilai_t, 'D');
elsif :new.nilai_t <=60 then
insert into report values (:new.npm, :new.nilai_t, 'c');
elsif :new.nilai_t <=80 then
insert into report values (:new.npm, :new.nilai_t, 'b');
elsif :new.nilai_t <='100' then
insert into report values (:new.npm, :new.nilai_t, 'A');
end if;
end;

insert into nilai1 values ('1134010052', 'galih', '68')

select* from report

Trigger Looping


Gambar Tabel Kamar

Source code :
create table C1 (
id varchar(10) primary key not null,
nama varchar(25),
no_hp varchar(12),
sewa_kamar int
);

create table kamar (
id varchar(10),
kamar_ke int
);

drop table kamar

create or replace trigger C2
after insert on C1
for each row
declare
i number :=1;
k int := :new.sewa_kamar;
begin
loop
insert into kamar values (:new.id, i);
i:=i+1;
exit when i>k;
end loop;
end;


insert into C1 values ('cus000121', 'pradhita', '085648182230', '3')
select * from kamar

Trigger Message


Gambar Tabel D4


Gambar Trigger Message
Source code :

create table D4(
id char(5) primary key not null,
nama varchar(25),
no_hp varchar(12)
);

insert into D4 values ('23101', 'pradhita', '085648182230')
insert into D4 values ('23102', 'yudha', '085648182231')
insert into D4 values ('23103', 'ahong', '085648182232')
insert into D4 values ('23104', 'blawung', '085648182233')
insert into D4 values ('23105', 'coco', '085648182234')
insert into D4 values ('23106', 'angung', '085648182235')
insert into D4 values ('23107', 'faujan', '085648182236')
insert into D4 values ('23108', 'arivandi', '085648182237')

create or replace trigger D5
before update on D4
for each row
begin
RAISE_APPLICATION_ERROR(-20001,'Tabel D4 Tidak Dapat Dirubah');
end;

update D4 set nama='prad' where id='23101'

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)

 
©2013 GoGreen Theme Designed by Templateism.com