Trigger
Sebelum memulai materi tentang Trigger ada baiknya kita mengenal terlebih dahulu apa itu Trigger, Trigger adalah perintah SQL yang berfungsi sebagai perintah otomatis ketika suatu kondisi terpenuhi hampir mirip seperti Store Procedure perintah yang digunakan biasanya INSERT,DELETE,UPDATE agar mudah digunakan untuk kepentingan kedepannya.
Untuk melanjutkan materi Trigger download Query ini terleih dahulu.
Sebelum melanjutkan perintah Trigger, harus menambahkan kolom Stok di tabel Products agar bisa melanjutkan
alter table products add stok int
update products set stok = 50
where prod_id = 'P0001'
or prod_id = 'P0002'
or prod_id = 'P0003'
or prod_id = 'P0004'
or prod_id = 'P0005'
or prod_id = 'P0006'
Membuat tabel log_product untuk mengetahui product yang ditambahkan dan dihapus
create table log_products (
prod_id char(5) not null,
status_prod varchar(10) not null,
status_date datetime default getdate())
Sebagai contoh
insert into log_products(
prod_id,
status_prod)
values
('P0001','added')
Trigger simpan_produk
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')
select * from log_products
select * from products
end
Sebagai contoh
insert into products values
('P0015','Speaker Simbadda',450000,'V0003',100)
Trigger hapus_produk
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')
select * from log_products
select * from products
end
Sebagai contoh
delete from products
where prod_id = 'P0015'
Membuat tabel log_orderitems untuk mengetahui orderitems yang ditambahkan dan dihapus
create table log_orderitems (
order_num char(5) not null,
status_order varchar(10) not null,
status_date datetime default getdate())
Sebagai contoh
insert into log_orderitems(
order_num,
status_order)
values
('O0003','added')
Trigger simpan_orderitems
create trigger simpan_orderitems
on orderitems
after insert
as
update products set products.stok = products.stok - inserted.quantity
from products,inserted
where products.prod_id = inserted.prod_id
begin
declare @id char(5)
select @id = order_num from inserted
insert into log_orderitems (order_num,status_order)
values (@id, 'added')
select * from log_orderitems
select * from orderitems
select * from products
end
Sebagai contoh
insert into orderitems values
('O0002','P0006',5)
Trigger hapus_orderitems
create trigger hapus_orderitems
on orderitems
for delete
as
update products set products.stok = products.stok + deleted.quantity
from products,deleted
where products.prod_id = deleted.prod_id
begin
declare @id char(5)
select @id = order_num from deleted
insert into log_orderitems (order_num,status_order)
values (@id, 'deleted')
select * from log_orderitems
select * from orderitems
select * from products
end
Sebagai contoh
delete from orderitems
where order_num = 'O0002'
Comments
Post a Comment