TRIGGER

 RAMA SUHERMAN

19312152

TUGAS TRIGGER

use sample_pbd_Rama

ALTER TABLE products ADD stock INT

CREATE TABLE Log_products (prod_id char(5) not null, status_prod varchar(10) not null, status_date datetime DEFAULT GETDATE()); select * from Log_products

update products SET stock = 100;

SOAL 1 Buatlah trigger dengan nama simpan_product yang aktif pada saat ada penambahan data di tabel product. Action => menyimpan data ke dalam Tabel Log_products dengan status ADDED

CREATE TRIGGER simpan_produk ON products AFTER INSERT AS BEGIN        DECLARE @id CHAR(5)        SELECT @id = prod_id from inserted          INSERT INTO Log_products(prod_id,status_prod)        VALUES (@id, 'added') END

INSERT INTO products VALUES ('P0007','Puma FX',350000,'V0001',100);

 

select * from Log_products

SOAL 2 Setelah menghapus data dari tabel products, trigger akan aktif dan menambahkan data ketabel Log_products. Mari kita lihat dari trigger dengan menambahkan tabel Log_products

CREATE TRIGGER hapus_produk ON products AFTER DELETE AS BEGIN        DECLARE @id CHAR(5)        SELECT @id = prod_id from deleted          INSERT INTO Log_products(prod_id,status_prod)        VALUES (@id,'deleted') END;   DELETE FROM products WHERE prod_id = 'P0008' select * from Log_products

SOAL 3 Buatlah sebuah trigger dengan nama simpan_orderitems, trigger aktif setelah menyimpan data pada tabel orderitem. Action => merubah stock pada tabel Products(stok berkurang)

 

CREATE TRIGGER simpan_orderitem ON orderitems AFTER INSERT AS BEGIN        DECLARE @id CHAR(5), @Qty INT        SELECT @id = prod_id,@Qty = quantity        FROM inserted          UPDATE products SET stock = stock-@Qty        WHERE prod_id = @id END;   INSERT INTO orderitems VALUES ('O0002','P0005',3);

CREATE TRIGGER hapus_orderitem ON orderitems AFTER DELETE AS BEGIN        DECLARE @id CHAR(5), @Qty INT        SELECT @id = prod_id,@Qty = quantity        FROM deleted          UPDATE products SET stock = stock-@Qty        WHERE prod_id = @id END;   DELETE FROM orderitems WHERE order_num = 'O0002' select * from products