Contoh coding TRIGGER, Lulus/Tidak Lulus Unknown7:28 AM 0 comments

Ini masih mengunakan MySQL Server 2005, kalau menggunakan cersi yang lain mungkin hasilnya akan beda.. tapi coding ini patut di coba :D

CREATE PROCEDURE simpan @nim varchar(10), @nama varchar(10), @nilai int
AS
BEGIN
insert into tb_mhs (nim, nama, nilai) values (@nim, @nama, @nilai)
END
GO
CREATE PROCEDURE ubah @nim varchar(10), @nama varchar(10), @nilai int
AS
BEGIN
update tb_mhs set nama=@nama, nilai=@nilai where nim=@nim
END
GO
CREATE PROCEDURE hapus @nim varchar(10)
AS
BEGIN
delete from tb_mhs where nim=@nim
END
GO
alter PROCEDURE simpan_nilai @nim varchar(10), @status varchar(20)
AS
BEGIN
insert into tb_nilai (nim, status) values (@nim, @status)
END
GO
CREATE PROCEDURE ubah_nilai @nim varchar(10), @status varchar(20)
AS
BEGIN
update tb_nilai set status=@status where nim=@nim
END
GO
CREATE PROCEDURE hapus_nilai @nim varchar(10)
AS
BEGIN
delete from tb_nilai where nim=@nim
END
GO
---------------------------
CREATE TRIGGER tambah   ON  tb_mhs FOR INSERT,UPDATE
AS
BEGIN
declare @nim varchar(10), @nilai int
select @nim=nim, @nilai=nilai from tb_mhs
if @nilai<= 40
update tb_nilai SET status='TIDAK LULUS' where nim=@nim
else
if @nilai <=65
update tb_nilai SET status='hampir LULUS' where nim=@nim
else
if @nilai <=100
update tb_nilai SET status='LULUS' where nim=@nim
END
GO
-------------------------
exec simpan_nilai '3',''
exec ubah_nilai '3','kuda'
exec hapus_nilai '3'
exec simpan '3','kuda','20'
exec ubah '3','kuda','80'
exec hapus '3'


Semoga sukses.. !