TRIGGER ORACLE

6:18 PM database, oracle No Comments

Ok yesterday, yesterday I was writing an article about Oracle. And this happened again today belajat about his Oracle trigger. Let trigger more applicable here would not talk about his theory, but what I've tried to make it n cases like this nie .... We had to make three tables of goods, transaction history ma. Now each of us to make transactions in the amount of data tables to update things as well. So let check this out ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

CREATE OR REPLACE TRIGGER barang

BEFORE INSERT OR UPDATE OR DELETE ON transaksi

FOR EACH ROW

DECLARE

vJum NUMBER(10);

vJum2 NUMBER(10);

vSelisih NUMBER(10);

BEGIN

SELECT jumlah INTO vJum FROM alat WHERE id_barang = :NEW.id_barang;

IF INSERTING THEN

UPDATE alat

SET jumlah = vJum – :NEW.jumlah

WHERE id_barang = :NEW.id_barang;

SELECT jumlah INTO vJum2 FROM alat WHERE id_barang = :NEW.id_barang;

INSERT INTO history (id_barang,tanggal,stock,tipe_transaksi) VALUES(:NEW.id_barang,:NEW.tanggal,vJum2,’Tambah Data’);

ELSIF UPDATING(‘jumlah’) THEN

IF :OLD.jumlah > :NEW.jumlah THEN

vSelisih := :OLD.jumlah – :NEW.jumlah;

UPDATE alat

SET jumlah = vJum + vSelisih

WHERE id_barang = :NEW.id_barang;

SELECT jumlah INTO vJum2 FROM alat WHERE id_barang = :NEW.id_barang;

INSERT INTO history (id_barang,tanggal,stock,tipe_transaksi) VALUES(:NEW.id_barang,:NEW.tanggal,vJum2,’Update Data’);

ELSIF :OLD.jumlah < :NEW.jumlah THEN

vSelisih := :NEW.jumlah – :OLD.jumlah;

UPDATE alat

SET jumlah = vJum – vSelisih

WHERE id_barang = :NEW.id_barang;

SELECT jumlah INTO vJum2 FROM alat WHERE id_barang = :NEW.id_barang;

INSERT INTO history (id_barang,tanggal,stock,tipe_transaksi) VALUES(:NEW.id_barang,:NEW.tanggal,vJum2,’Update Data’);

END IF;

ELSIF DELETING THEN

UPDATE alat

SET jumlah = vJum + :NEW.jumlah

WHERE id_barang = :NEW.id_barang;

INSERT INTO history (id_barang,tanggal,stock,tipe_transaksi) VALUES(:NEW.id_barang,:NEW.tanggal,vJum,’Hapus Data’);

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(‘Data tidak ada’);

END;

/