Trigger on SQL 2008

==========

TUGAS 1

==========

Create database Sukriyadi

use Sukriyadi

CREATE TABLE TblMahasiswa (

NPM char(10),

Status char(10),

constraint PKTblMahasiswa primary key (NPM)

)

CREATE TABLE TblNilai

(

KodeNilai char(4),

NPM char(10),

Nilai float,

constraint PKTblNilai primary key (NPM),

constraint FKTblNilai foreign key (NPM) references TblMahasiswa(NPM)

)

BUAT TRIGGER INPUT NILAI DAN SECARA OTOMATIS MENGGATI STATUS

DARI MAHASISWA TERKAIT ?

========================

INSERT INTO TblMahasiswa VALUES('2010240331','')

INSERT INTO TblMahasiswa VALUES('2010240332','')

INSERT INTO TblMahasiswa VALUES('2010240333','')

INSERT INTO TblMahasiswa VALUES('2010240334','')

select * from tblMahasiswa

Preview :

ALTER TRIGGER tr_status

ON TblNilai

FOR INSERT, UPDATE

AS

DECLARE @KodeNilai char(4)

DECLARE @NPM char(10)

DECLARE @Nilai float

SELECT @KodeNilai = KodeNilai,@NPM = NPM, @Nilai = Nilai FROM TblNilai

IF @nilai >= 60

begin

UPDATE TblMahasiswa SET Status = 'Lulus' WHERE NPM=@NPM

end

ELSE if  @nilai < 60

begin

UPDATE TblMahasiswa SET status = 'Gagal' WHERE NPM=@NPM

end

go

Insert into TblNilai values ('0001','2010240331',70)

Insert into TblNilai values ('0002','2010240332',50)

Insert into TblNilai values ('0003','2010240333',80)

Insert into TblNilai values ('0004','2010240334',40)

select * from tblMahasiswa

select * from TblNilai

Preview :

==========

TUGAS 2

==========

create table TblBarang

(

IdBarang varchar(4) not null,

NamaBarang varchar(255) not null,

JumlahBarang int null default 0,

constraint Key_Barang primary key (IdBarang)

)

create table TblBeli

(

NoPembelian Varchar(4) not null primary key,

IdBarang varchar(4) null ,

JumlahBeli int null

constraint FkBeliBarang foreign key (IdBarang) references TblBarang(IdBarang)

)

Select * from TblBarang

Select * from TblBeli

INSERT INTO TblBarang  VALUES('B001','Laptop',20)

INSERT INTO TblBarang  VALUES('B002','CPU',30)

INSERT INTO TblBarang  VALUES('B003','Printer',40)

BUAT TRIGGER PEMBELIAN BARANG (STOK BERTAMBAH),

DAN TRIGGER PENJUALAN BARANG (STOK BERKURANG)

================================================

CREATE TRIGGER tr_Pembelian

ON TblBeli

FOR INSERT

AS

UPDATE TblBarang SET TblBarang.JumlahBarang =TblBarang.JumlahBarang + TblBeli.JumlahBeli

from TblBarang join inserted TblBeli on TblBarang.IdBarang = TblBeli.IdBarang

go

INSERT INTO TblBeli VALUES('PB01','B001',5)

INSERT INTO TblBeli VALUES('PB02','B002',7)

INSERT INTO TblBeli VALUES('PB03','B003',9)

Select * from TblBarang

Select * from TblBeli

================================================

create table TblJual

(

NoPenjualan Varchar(4) not null primary key,

IdBarang varchar(4) null ,

JumlahJual int null

constraint FkJualBarang foreign key (IdBarang) references TblBarang(IdBarang)

)

CREATE TRIGGER tr_Penjualan

ON TblJual

FOR DELETE

AS

UPDATE TblBarang SET TblBarang.JumlahBarang =TblBarang.JumlahBarang - TblJual.JumlahJual

from TblBarang join deleted TblJual on TblJual.IdBarang = TblJual.IdBarang

go

=================================================
Cara 2 :

create trigger tgBeli

on tblbeli

after insert

as

declare @a varchar (4)

declare @b integer

set @a = (select IdBarang from inserted)

set @b = (select IdBarang from inserted)

update TblBarang set JumlahBarang = JumlahBarang + @b where idbarang = @a

 

===================================

Untuk Update TblBeli

 

 

create trigger tgBeli_update

on tblbeli

after update

as

declare @a varchar (4)

declare @b integer

declare @c integer

set @a = (select IdBarang from inserted)

set @b = (select JumlahBeli from inserted)

set @c = (select JumlahBeli from deleted)

update TblBarang set JumlahBarang = JumlahBarang - @c + @b

Update TblBeli set JumlahBeli = 7 where NoPembelian = 'PB01'

Select * from TblBarang

Select * from TblBeli

 

==================================

 

INSERT INTO TblJual VALUES('JL01','B001',5)

INSERT INTO TblJual VALUES('JL02','B002',7)

INSERT INTO TblJual VALUES('JL03','B003',9)

Select * from TblBarang

Select * from TblBeli

Select * from TblJual

SEMOGA BERMANFAAT

---