Membuat trigger dengan berbagai macam menu Pada Oracle PART 2

My SQL

Insert – insert

Gambar table mhs1

Gambar table mhs2

Source code :

create table mhs1 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) create table mhs2 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) CREATE TRIGGER `coba`.`tr_mhs` AFTER INSERT on `coba`.`mhs1` FOR EACH ROW BEGIN insert into mhs2 values  (new.NPM, new.nama, new.no_hp); END

Insert – Update

Gambar table mhs3

Gambar table mhs4

Source code :

create table mhs3 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) create table mhs4 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) CREATE TRIGGER `coba`.`Tr_mhs1` AFTER INSERT on `coba`.`mhs3` FOR EACH ROW BEGIN update mhs4 set nama=new.nama, no_hp=new.no_hp where npm=new.npm; END

insert – delete

Gambar table mhs14 awal

Gambar table mhs13

Gambar table mhs14 setelah insert - delete

Source code :

create table mhs13 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) create table mhs14 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ `coba`.`mhs7`$$ CREATE TRIGGER `coba`.`mhs7` AFTER INSERT on `coba`.`mhs13` FOR EACH ROW BEGIN delete from mhs14 where npm=new.npm; END$$ DELIMITER ;

Update – delete

Gambar table mhs15 awal

 

Gambar table mhs16 awal

Gambar table mhs15 setelah update – delete

Gambar table mhs16 setelah update – delete

Source code :

create table mhs15 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) create table mhs16 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ `coba`.`mhs8`$$ CREATE TRIGGER `coba`.`mhs8` AFTER UPDATE on `coba`.`mhs15` FOR EACH ROW BEGIN delete from mhs16 where npm=old.npm; END$$ DELIMITER ;

Update – insert

Gambar table mhs5

Gambar table mhs5 setelah update – insert

Gambar table mhs6 setelah update – insert

Source code :

create table mhs5 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) create table mhs6 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ `coba`.`mhs3`$$ CREATE TRIGGER `coba`.`mhs3` AFTER UPDATE on `coba`.`mhs5` FOR EACH ROW BEGIN INSERT INTO mhs6 VALUES (old.npm, old.nama, old.no_hp); END $$ DELIMITER ;

Update – update

Gambar table mhs7 awal

 

Gambar table mhs8 awal

 

Gambar table mhs7 setelah update – update

Gambar table mhs8 setelah update – update

Source code :

create table mhs7 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) create table mhs8 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ `coba`.`mhs4`$$ CREATE TRIGGER `coba`.`mhs4` AFTER UPDATE on `coba`.`mhs7` FOR EACH ROW BEGIN update mhs8 set nama=new.nama, no_hp=new.no_hp where npm=new.npm; END$$ DELIMITER ;

Delete – update

Gambar table mhs11 awal

 

Gambar table mhs12 awal

Gambar table mhs11 setelah delete – update

Gambar table mhs12 setelah delete – update

Source code :

create table mhs11 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) create table mhs12 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ `coba`.`mhs6`$$ CREATE TRIGGER `coba`.`mhs6` BEFORE DELETE on `coba`.`mhs11` FOR EACH ROW BEGIN update mhs12 set nama=old.nama, no_hp=old.no_hp where npm=old.npm; END$$ DELIMITER ;

Delete – insert

Gambar table mhs9 awal

Gambar table mhs9 setelah delete – insert

Gambar table mhs10 setelah delete – insert

Source code :

create table mhs9 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) create table mhs10 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ `coba`.`mhs5` $$ CREATE TRIGGER `coba`.`mhs5` BEFORE DELETE on `coba`.`mhs9` FOR EACH ROW BEGIN insert into mhs10 values (old.npm, old.nama, old.no_hp); END$$ DELIMITER ;

Delete – delete

Gambar table mhs17 awal

 

Gambar table mhs18 awal

 

Gambar table mhs11 setelah delete – update

Gambar table mhs12 setelah delete – update

Source code :

create table mhs17 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) create table mhs18 ( npm char(10)primary key, nama varchar(25), no_hp varchar(12) ) DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ `coba`.`mhs9` $$ CREATE TRIGGER `coba`.`mhs9` BEFORE DELETE on `coba`.`mhs17` FOR EACH ROW BEGIN delete from mhs18 where npm=old.npm; END$$ DELIMITER ;

Trigger branching

Gambar table nilai

CREATE TABLE mahasiswa ( npm CHAR(10) NOT NULL, nama VARCHAR(25), nilai_angka int, PRIMARY KEY (npm)) CREATE TABLE nilai ( npm CHAR(10) NOT NULL, nilai_angka int, nilai_abjad varchar(2), PRIMARY KEY (npm)) DELIMITER go CREATE TRIGGER tr_nilai after insert ON mahasiswa FOR EACH ROW BEGIN if new.nilai_angka <20 then insert into nilai values (new.npm, new.nilai_angka, 'E'); elseif new.nilai_angka between 20 and 39 then insert into nilai values (new.npm, new.nilai_angka, 'D'); elseif new.nilai_angka between 40 and 59 then insert into nilai values (new.npm, new.nilai_angka, 'C'); elseif new.nilai_angka between 60 and 79 then insert into nilai values (new.npm, new.nilai_angka, 'B'); elseif new.nilai_angka between 80 and 100 then insert into nilai values (new.npm, new.nilai_angka, 'A'); end if; END; go DELIMITER ; insert into mahasiswa values ('1134010051', 'pradhita', '83') select* from nilai