Membuat trigger dengan berbagai macam menu Pada Oracle PART 3

Sql server 2000

Insert to insert

Insert to insert CREATE TABLE MHS1 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go CREATE TABLE MHS2 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go create trigger tr_mhs1 on MHS1 for insert as begin declare @NPM char(10) declare   @nama varchar(25) declare   @no_hp varchar(12) select @NPM= NPM from inserted select @nama= nama from inserted select @no_hp= no_hp from inserted insert into MHS2 values (@NPM, @nama, @no_hp) end insert into MHS1 values ('1134010061', 'pradhita yudha', '085648182230') select * from MHS2

Data pada table mhs1 di insert secara otomatis table mhs2 juga terisi

Insert to update

CREATE TABLE MHS3 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go CREATE TABLE MHS4 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go create trigger tr_mhs2 on MHS3 for insert as begin declare @NPM char(10) declare   @nama varchar(25) declare   @no_hp varchar(12) select @NPM= NPM from inserted select @nama= nama from inserted select @no_hp= no_hp from inserted update MHS4 set nama=@nama, no_hp=@no_hp where NPM=@NPM end drop trigger tr_mhs2 insert into MHS3 values ('1134010061', 'indra bakti', '085648184450') insert into MHS4 values ('1134010062', 'yudha', '085648182231') insert into MHS4 values ('1134010063', 'negara', '085648182232') select * from MHS4

Data di MHS4 sebelum insert di MHS3

Sesudah di insert

Insert to delete

CREATE TABLE MHS5 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go CREATE TABLE MHS6 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go create trigger tr_mhs3 on MHS5 for insert as begin declare @NPM char(10) declare   @nama varchar(25) declare   @no_hp varchar(12) select @NPM= NPM from inserted select @nama= nama from inserted select @no_hp= no_hp from inserted delete from MHS6 where NPM=@NPM end insert into MHS6 values ('1134010061', 'pradhita', '085648182230') insert into MHS6 values ('1134010062', 'yudha', '085648182231') insert into MHS6 values ('1134010063', 'negara', '085648182232') insert into MHS5 values ('1134010061', 'pradhita', '085648182230') select* from MHS6

Data di MHS4 sebelum insert di MHS5

Sesudah inser di mhs5 otomatis delete data yg di mhs6

Update to insert

CREATE TABLE MHS7 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go CREATE TABLE MHS8 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go create trigger tr_mhs4 on MHS7 for update as begin declare @NPM char(10) declare   @nama varchar(25) declare   @no_hp varchar(12) select @NPM= NPM from inserted select @nama= nama from inserted select @no_hp= no_hp from inserted insert into MHS8 values (@NPM, @nama, @no_hp) end insert into MHS6 values ('1134010061', 'pradhita', '085648182230') insert into MHS6 values ('1134010062', 'yudha', '085648182231') insert into MHS6 values ('1134010063', 'negara', '085648182232') insert into MHS5 values ('1134010061', 'pradhita', '085648182230')

Sebelum mhs7 di update data di mhs8 masih kosong

Mhs7 setelah diupdate

Table mhs8 terisi data yg di update

Update to update

CREATE TABLE MHS9 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go CREATE TABLE MHS10 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go create trigger tr_mhs5 on MHS9 for update as begin declare @NPM char(10) declare   @nama varchar(25) declare   @no_hp varchar(12) select @NPM= NPM from inserted select @nama= nama from inserted select @no_hp= no_hp from inserted update MHS10 set nama=@nama, no_hp=@no_hp where NPM=@NPM end insert into MHS10 values ('1134010061', 'pradhita', '085648182230') insert into MHS10 values ('1134010062', 'yudha', '085648182231') insert into MHS10 values ('1134010063', 'negara', '085648182232') update MHS9 set nama='haris cpt', no_hp='085683658123' where npm='1134010061' select* from MHS10

Data di table mhs9 dan mhs10 sama isinya

Setelah update di mhs9 maka mhs10 juga otomatis terupdate

Update to delete

CREATE TABLE MHS11 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go CREATE TABLE MHS12 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go create trigger tr_mhs6 on MHS11 for update as begin declare @NPM char(10) declare   @nama varchar(25) declare   @no_hp varchar(12) select @NPM= NPM from inserted select @nama= nama from inserted select @no_hp= no_hp from inserted delete from mhs12 where npm=@npm end insert into MHS12 values ('1134010061', 'pradhita', '085648182230') insert into MHS12 values ('1134010062', 'yudha', '085648182231') insert into MHS12 values ('1134010063', 'negara', '085648182232') update MHS11 set nama='haris cpt', no_hp='085683658123' where npm='1134010061' select* from MHS12

Data di table mhs11 dan mhs12 sama isinya

Mhs11 setelah update

Mhs12 setelah update di mhs11 maka datanya otomatis akan terhapus

Delete to delete

CREATE TABLE MHS13 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go CREATE TABLE MHS14 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go create trigger tr_mhs7 on MHS13 for delete as begin declare @NPM char(10) declare   @nama varchar(25) declare   @no_hp varchar(12) select @NPM= NPM from deleted select @nama= nama from deleted select @no_hp= no_hp from deleted delete from mhs14 where npm=@npm end insert into MHS14 values ('1134010061', 'pradhita', '085648182230') insert into MHS14 values ('1134010062', 'yudha', '085648182231') insert into MHS14 values ('1134010063', 'negara', '085648182232') delete from mhs13 where npm='1134010061' select* from MHS14

Sebelum di delete data mhs13 dan mhs14 sama

Setelah di delete data yg sama pada kedua table dihapus

Delete to update

CREATE TABLE MHS15 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go CREATE TABLE MHS16 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go create trigger tr_mhs8 on MHS15 for delete as begin declare @NPM char(10) declare   @nama varchar(25) declare   @no_hp varchar(12) select @NPM= NPM from deleted select @nama= nama from deleted select @no_hp= no_hp from deleted update mhs16 set nama=@nama, no_hp=@no_hp where npm=@npm end insert into MHS15 values ('1134010061', 'arivandi', '085648182230') insert into MHS15 values ('1134010062', 'fauzan', '085647987631') insert into MHS15 values ('1134010063', 'rizal', '085648198762') delete from mhs15 where npm='1134010062'

Data di table mhs15 sebelum di delete

Data table mhs16 sebelum dieksekusi

Data table mhs16 setelah dieksekusi

Delete to insert

CREATE TABLE MHS17 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go CREATE TABLE MHS18 (                 NPM char(10) primary key,                 nama varchar(25),                 no_hp varchar(12) ) go create trigger tr_mhs9 on MHS17 for delete as begin declare @NPM char(10) declare   @nama varchar(25) declare   @no_hp varchar(12) select @NPM= NPM from deleted select @nama= nama from deleted select @no_hp= no_hp from deleted insert into mhs18 values (@NPM, @nama, @no_hp) end insert into MHS17 values ('1134010061', 'arivandi', '085648182230') insert into MHS17 values ('1134010062', 'fauzan', '085647987631') insert into MHS17 values ('1134010063', 'rizal', '085648198762') delete from mhs17 where npm='1134010062' select* from MHS18

Data di mhs17 sebelum dihapus dan data di mhs18 masih kosong

Data di mhs17 setelah dihapus

Data di table mhs18 terisi data yang telah dihapus 

Read More

No comments

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

Read More

No comments