view,Trigger dan store procediue

Basis Data VIEW

SQL View adalahtabel virtual (bukantabelsebenarnya) yang dibuatdaribeberapatabelatau
view lain.SQL View tidakmemiliki data sendiri, tetapi data‑datanyaberasaldaritabel-tabelatau view lain.SQL View dibuatataudibangundengan SELECT statement.

Di dalam query SELECT tersebutkitabisamenambahkan ORDER BY statement untukmengurutkandata atautidaksamasekali.SQL View dapatdigunakanuntukmemudahkanataumenyederhanakan data yang ditampilkan, mencegahmenampilkan data yang pentingdenganmenyembunyikannamakolomnya.

Kegunaan View

1. Hide Columns or rows
2. Display results ofcomputations
3. Hide complicated SQL Sintax
4. Layer Built - in functions
5. Provide level of isolation betwen table data and user
6. Assign different processing permisions  to different views of the same table

Membuat View

CREATE VIEW [nama_ view] AS

[select _sta tem en t]

Contoh :Create view costumer name view as select [name] as costumer name from costumer;

MenggunakanVIEW :

Select * from customername

VIEW UNTUK MENYEMBUNYIKAN COLUMN DAN BARIS

Contoh, misalkanpenggunaaplikasi View Ridge inginmenyederhanakandaftar customer, sehingga yangtampilhanyanamadannomorteleponsaja.

Create view basic customerdata as select [name], areacode,phonenumber

FROM CUSTOMER;

Contohlain, jikapihakmanajemeninginmenyembunyikankolomAcquisitionPricedan Sales Pricemakadapatmembuat View dengantanpamengikutsertakankolom-kolomtersebut.

View jugadapatdigunakanuntukmenyembunyikan baris data tertentu. Yaitudenganmenambahkanklausa WHERE pada query-nya.

 VIEW UNTUK SQL yang ComplexDenganmenggunakan views, programmer (developer) tidakperlumemasukan SQL statement yang kompleksketikamerekamembutuhkansuatu data.Misalkan view berikutmenghasilkan data ketertarikancustomer terhadap artist yang ada.

 CREATE VIEW CustomerInterests ASSELECTC.Name as Customer, A.Name as Artist

FROM CUSTOMER CJOIN CUSTOMER _ARTIST _INT CI

ON C.CustomerID = CI.CustomerID

JOIN ARTIST AON CI.ArtistID = A.ArtistID;

Contoh Lain Create View:

CREATE VIEW "Alphabetical list of products" AS

SELECT Products.*, Categories.CategoryName

FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

WHERE (((Products.Discontinued)=0))

Contoh Modify View :

ALTER VIEW "Alphabetical list of products" AS

SELECT Products.*, Categories.CategoryName

FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID

WHERE (((Products.Discontinued)=0))

Contoh Running View :

SELECT TOP 1000 *  

FROM [AdventureWorks2008].[Sales].[vIndividualCustomer]

Mengubah View

ALTER VIEW [nama_ view] AS

[select
_statement]

Contoh:

ALTER
VIEW CustomerNameViewAS

SELECT[Name]
AS NamaPelangganFROM   CUSTOMER;

MENGHAPUS View

DROP VIEW[nama_ view]






Menggunakan TRIGGER

Triggeradalah program yang dieksekusioleh DBMS ketikasuatu event terjadi.

Trigger
diaplikasikanpadatabelatauview. Sebuahtabelatau view dapatmemilikilebihdari 1 trigger.
Tetapisebuah trigger hanyadigunakanpada 1tabelatau
view.

Trigger
dijalankankarena request insert,update,
ataudelete padasuatutabel

atau view.

3 Tipe trigger:




1.BEFORE (insert, update, delete)

2.INSTEAD OF (insert,update, delete)

3.AFTER (insert,update, delete) 

Total ada 9 tipe trigger.

SQL
Server hanyamensupport 6 tipe

trigger.
Hanyatipe AFTER dan INSTEADOF saja.

KegunaanMemakai Trigger

Vropvide
default values

Enforce data
constraints

Update Views

CREATE TRIGGER[nama_trigger]

ON [    t a b  l 
e      a  t 
a  u      v  
i    e  w  ]

{ FOR| AFTER
|
INSTEAD OF }

{ [DELETE] [,][INSERT]
[,][UPDATE]
}

AS

[sql_statement]

Sample study kasus TRIGGER

Database KAM PUS memiliki table
MHS, MK, dan NILAIMHS, dengan data sbb:





-- CREATE OUR TEST DATABASE
CREATE DATABASE Test
 
-- USE TEST DATABASE
USE Test
1 Next, we create our Test Tables:
1
--- CREATE TEST TABLES
---Table1
CREATE TABLE [dbo].[Table1](
[Field1] [VARCHAR] (50) NULL,
[Field2] [VARCHAR] (50)NULL)


---Table InsertedTable
CREATE TABLE [dbo].[InsertedTable](
[DATE] SMALLDATETIME,
[Field1] [VARCHAR] (50) NULL,
[Field2] [VARCHAR] (50) NULL)


---Table DeletedTable
CREATE TABLE [dbo].[DeletedTable](
[DATE] SMALLDATETIME,
[Field1] [VARCHAR] (50) NULL,
[Field2] [VARCHAR] (50) NULL)

--- CREATE TRIGGER
CREATE TRIGGER CatchInsertDelete
ON Table1
FOR INSERT, DELETE
AS
INSERT INTO InsertedTable (DATE,Field1, Field2)
SELECT GETDATE(), Field1, Field2 FROM Inserted
INSERT INTO DeletedTable (DATE, Field1, Field2)
SELECT GETDATE(), Field1, Field2 FROM Deleted
Go

---Insert New Records To Table1
INSERT INTO Table1 VALUES ('Mike', 'Hotek')
INSERT INTO Table1 VALUES ('Paul','Nielsen')
INSERT INTO Table1 VALUES ('Denny','Cherry')
INSERT INTO Table1 VALUES ('Bill','Gates')
INSERT INTO Table1 VALUES ('John','Doe')
INSERT INTO Table1 VALUES ('Jane','Doe')



Syntax




CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ {FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ]
[ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column ) 
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator} updated_bitmask)
{ comparison_operator} column_bitmask [ ...n ]
} ]
sql_statement [...n ]
}
}

biarlebihmengerti,
kitamulaidengancontoh.. jadikitabutuhmembuat 2 tabeltersebut di SQL server
2005, script sebagaiberikut

create table BARANG
(
BAR_ID int not null,
BAR_NAMA varchar(255) not null,
BAR_STOCK int null
default 0,
constraint PK_BARANG primary key (BAR_ID)
)
go

create table PEMBELIAN
(
PEM_ID int not null,
BAR_ID int null ,
PEM_JUMLAH int null ,
constraint PK_PEMBELIAN primary key (PEM_ID)
)
go

create index RELATION_FK on
PEMBELIAN (BAR_ID)
go

alter table PEMBELIAN
add constraint FK_PEMBELIA_RELATION_BARANG foreign key (BAR_ID)
references BARANG (BAR_ID)
go

INSERT INTO BARANG (BAR_ID,BAR_NAMA)
VALUES (1,’AQUA’);
INSERT INTO BARANG (BAR_ID,BAR_NAMA) VALUES (2,’TOTAL’);
INSERT INTO BARANG (BAR_ID,BAR_NAMA) VALUES (3,’AQUADES’); 

setelahandamenjalankan script
tersebut, makaandaakanmempunyai 2 tabel, yaitutabelbarangdenganisi 3 buah data,
dantabelpembeliandengan data masih kosong.3 data di
tabelbarangtersebutsecaradefautstocknyaadalah 0

kemudiansaatnyakitabuat trigger
sehinggaketikakitamenambahkan data di
tabelpembeliandenganjumlahpembelianbarangtertentu, maka stock di
tabelbarangakanbertambahsesuaidenganbarangygdibeli, syntac trigger
tersebutadalahsebagaiberikut

create trigger tambahStockbarang on
pembelian
for insert
as
update b set b.bar_stock = b.bar_stock + i.pem_jumlah
from barang b join inserted i on b.bar_id = i.bar_id

artidarikodetersebutadalahsebagaiberikut

create trigger tambahStockbarang on pembelian
membuat trigger dengannamatambahStockBarangdimana trigger
tersebutakanterpicujikaadaperubahan di tabelpembelian

for insert
as
perubahantersebutadalahpenambahan(insert) di tabelpembelian , selainpenambahanbisajugadiisidenganperubahan(update) ataupenghapusan(delete)
fordisinijugabisarubahisinyajadiafteratauinstead of .
perbedaanyaadalahwaktu trigger dikerjakan, biasayaygseringdigunakanadalahfor

update b set b.bar_stock =
b.bar_stock + i.pem_jumlah

from barang b join inserted i on b.bar_id = i.bar_id

iniadalahkodeygdikerjakanketikakejadian trigger terpicu,
kodediatasbertujuanmerubahnilaibar_stockpadatabelbarangdenganmenambahkannilaibar_stockygsekarangdenganjumlahbarangygdibeli
(pem_jumlah). perhatikandisiniadatabel yang bernamainserted,
tabeltersebutmerupakantabellogikaygdigunakanuntukmenyimpan data yang
memicuterjadinya trigger, dalamhalininilai data ygdimasukkan(insert)
kedalamtabelpembelian, selaininserted, tabellogikalainnyaadalahdeleted,
tabellogikainidigunakanuntuk trigger ygterpicudengankejadiandelete

kitacobamasukkankodeberikut

INSERT INTO PEMBELIAN (PEM_ID,
BAR_ID, PEM_JUMLAH) VALUES (1,1,4);
INSERT INTO PEMBELIAN (PEM_ID, BAR_ID, PEM_JUMLAH) VALUES (2,3,2);
INSERT INTO PEMBELIAN (PEM_ID, BAR_ID, PEM_JUMLAH) VALUES (3,1,1);

artikodetersebut
pem_idharuslahbedakarenamerupakan primary key
beli aqua(kodebar_id=1) sebanyak 4
beliaquades (kodebar_id=3) sebanyak 2
beli aqua lagisebanyak 1

sehinggasecarakeseluruhanygdibeli
aqua sebanyak 5 danaquadessebanyak 2
karena default nilai stock barangadalah 0, makaseharusnyanilai aqua 5
danaquades 2 adalahjumlah stock barangsekarangini

Read more »

Posted in: Tugas Basdat

Query Tugas Basdat

Menampilkan Stock buku atau majalah yang terbanyak
SELECT MAX(kd_barang) AS Kode_Barang, MAX(kode_tipe_bacaan) AS  tipe_Bacaan,
MAX (jumlah_stok) AS Stok_Terbanyak FROM dbo.[gito_tmbarang]
Menampilkan total belanja masing-masing penerbit

Script Query :
SELECT kd_penerbit, SUM (total_bersih_pembelian) AS TOTAL_Belanja FROM
dbo.[gito_pembelian] GROUP BY kd_penerbit
. Menampilkan jumlah stock masing-masing barang dengan mengkategorikan barang-barang tersebut

Sciprt Query :
SELECT kd_kategori, kd_tipe_bacaan, jumlah_stok FROM dbo.[gito_tmbarang] GROUP BY
jumlah_stok, kd_kategori, kd_tipe_bacaan
 Menampilkan total penjualan barang yang dikategorikan

Script Query :
SELECT dbo.[gito_penjualan_detil].no_penjualan, dbo.[gito_penjualan_detil].kd_barang,
SUM(dbo.[gito_penjualan].total_bersih_penjualan) AS Total_Penjualan FROM
dbo.[gito_penjualan_detil] INNER JOIN dbo.[gito_penjualan] ON
dbo.[gito_penjualan_detil].no_penjualan = dbo.[gito_penjualan].no_penjualan
GROUP BY dbo.[gito_penjualan_detil].kd_barang, dbo.[gito_penjualan_detil].no_penjualan

Read more »

Posted in: Tugas Basdat