Triggers

1
Triggers Stored Procedure

• The main reference of this presentation is from
  book Peter Rob, Carlos Coronel, Database
  systems Design, Implementation and Management
  4th ed, Thomson Learning, 2000.
• Additional reference from Oracle course handout
  Matthew P. Johnson CISDD, CUNY, January, 2005

2
Outline

• Trigger
• Stored Procedure

3
Triggers

• Trigger merupakan kode PL/SQL yang secara
  otomatis dijalankan oleh DBMS jika suatu event
  database terjadi
• Event tersebut bisa berupa operasi
• INSERT, UPDATE, DELETE

4
Trigger lanjut

• Sebuah trigger selalu dijalankan sebelum atau
  sesudah sebuah data row di-SELECT, di-INSERT atau
  di-UPDATE
• Sebuah trigger selalu berasosiasi dengan tabel
  pada basis data
• Setiap tabel bisa mempunyai satu atau lebih
  trigger
• Sebuah trigger dieksekusi sebagai bagian dari
  transaksi yang men-trigger trigger tsb.

5
Kegunaan Trigger

• Trigger dapat digunakan untuk memaksakan
  constraint yang tidak dapat dilakukan pada
  perancangan dan implementasi DBMS
• Trigger dapat secara otomatis memberikan pesan
  warning jika terjadi gangguan pada IC. Penggunaan
  trigger yg umum adalah untuk meningkatkan
  referential IC
• Trigger dapat digunakan untuk update nilai pada
  tabel, insert tuple pada tabel, dan memanggil
  stored procedure yg lain

6
Recommends Oracle

• Oracle merekomendasikan trigger utk
• Tujuan audit (membuat audit log)
• Secara otomatis men-generate nilai dari derived
  column
• Memaksakan constraint (business atau security)
• Membuat replikasi tabel untuk tujuan back-up

7
Sintaks Trigger dalam Oracle
CREATE OR REPLACE TRIGGER trigger-name BEFORE
AFTER INSERT DELETE UPDATE UPDATE OF
column-list ON table-name FOR EACH ROW WHEN
(...) DECLARE ... BEGIN ... executable
statements ...(PL/SQL statement) EXCEPTION ...
END trigger name
8
Keterangan Sintaks Trigger

• OR REPLACE
• Buat trigger baru jika sudah ada. Dapat
  digunakan juga utk mengubah definisi dari trigger
  yg sudah ada.
• Trigger-name
• Nama dari trigger-nya
• BEFORE
• Mengindikasikan ORACLE menjalankan trigger tsb
  sebelum suatu statement
• AFTER
• Mengindikasikan ORACLE menjalankan trigger tsb
  setelah suatu statement
• INSERT
• Mengindikasikan ORACLE menjalankan trigger tsb
  ketika adanya statement INSERT row pada suatu
  tabel
• DELETE
• Mengindikasikan ORACLE menjalankan trigger tsb
  ketika adanya statement DELETE row pada suatu
  tabel
• UPDATE..OF
• Mengindikasikan ORACLE menjalankan trigger tsb
  ketika adanya statement UPDATE pada kolom
  tertentu dari tabel. Jika OF-nya dihilangkan maka
  trigger dijalankan ketika adanya UPDATE pada
  setiap kolom.

9
Keterangan Sintaks Trigger

• FOR EACH ROW
• Trigger diaktivasi oleh perubahan pada setiap
  tuple pada tabel yang diawasi oleh trigger.
  Sedangkan jika ini tidak dispesifikasikan, maka
  diasumsikan statement trigger, dimana trigger
  diaktivasi setiap adanya statement INSERT, DELETE
  atau UPDATE yg dilakukan, tanpa melihat adanya
  perubahan pada row atau tidak (bahkan akan
  diaktivasi walaupun row yg diawasi tidak berubah)
• WHEN
• Adalah syarat trigger tsb bisa dieksekusi
• PL/SQL statement
• Merupakan body dari trigger yg menspesifikasikan
  apa saja yang dilakukan jika trigger tsb
  diaktivasi

10
Penamaan pada Trigger

• Untuk tujuan dokumentasi, baiknya trigger
  diberikan nama yang merefleksikan tabel yang
  terkait dengan trigger tsb.
• Sebagai tambahan gunakan awalan TRG di namanya
  TRG_TABLE_NAME
• Contoh
• TRG_PRODUCT_REORDER, mengindikasikan bahwa tabel
  PRODUCT adalah source dari trigger REORDER

11
The PRODUCT Table
12
TRG_PRODUCT_REORDER Trigger
TRG_PRODUCT_REORDER untuk mengevaluasi kuantitas
product on-hand (P_ONHAND). Jika kuantitas on
hand dibawah nilai minimum (P_MIN), maka nilai
P_REORDER diset 1, yang artinya harus diorder.
13
Verifying the TRG_PRODUCT_REORDER Trigger
Execution
14
The P_REORDER Value Mismatch After Update of the
P_MIN Attribute
Jika kita mengubah P_MIN dari product 2232/QWE,
P_ONHAND lt P_MIN, namun P_REORDER masih 0, Kenapa
?
15
TRG_PRODUCT_REORDER versi 2

• CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER
• AFTER INSERT OR UPDATE OF P_ONHAND, P_MIN on
  PRODUCT
• BEGIN
• UPDATE PRODUCT
• SET P_REORDER 1
• WHERE P_ONHAND lt P_MIN
• END

16

• Jika P_ONHAND pada 11QER/31 diupdate menjadi 29
  sehingga nilainya lebih besar dari P_MIN,
  ternyata P_REORDER masih 1. Kenapa ?

17
TRG_PRODUCT_REORDER versi 3

• CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER
• AFTER INSERT OR UPDATE OF P_ONHAND, P_MIN on
  PRODUCT
• FOR EACH ROW
• BEGIN
• IF NEW.P_ONHAND lt NEW.P_MIN THEN
• NEW.P_REORDER 1
• ELSE
• NEW.P_REORDER 0
• END IF
• END

18
Trigger maintenance

• Enabling disabling
• ALTER TRIGGER emp_after_insert DISABLE
• ALTER TRIGGER emp_after_insert ENABLE
• Deleting
• DROP TRIGGER emp_after_insert
• Viewing
• select trigger_name from user_triggers
• select text from user_source where
  name'TOWN_CRIER'
• Check validity
• select object_name, status from user_objects
  where object_type'TRIGGER'

19
Stored Procedure

• Sebuah stored procedure adalah kumpulan dari
  prosedur dan statement SQL yang terdapat pada
  DBMS.
• Dikenali dengan nama
• Dieksekusi sebagai sebuah sebuah kesatuan (unit)
• Di Oracle, dikenal dengan PL/SQL (Procedural
  Language/SQL)

20
Keuntungan stored procedure

• Mengurangi network traffic dan meningkatkan
  performance
• Tidak ada transmisi individual SQL statements
  pada network

21
Stored Procedure syntax

• CREATE OR REPLACE PROCEDURE procedure_name
  (argument IN/OUT data-type, etc)
• IS/AS BEGIN
• DECLARE variable name and data type
• PL/SQL or SQL statement
• END
• DECLARE ? digunakan untuk mendeklarasikan
  variabel yg digunakan dan tipe datanya
• Argumen menspesifikasikan parameter ? IN (input)
  OUT (output)
• Data type merupakan salah satu dari tipe data
  yang terdapat pada DBMS.
• Untuk mengeksekusinya
• EXEC stored_procedure_name (parameter, parameter,
  )

22
Creating the PRC_PROD_DISCOUNT Stored Procedure
23
Results of the PRC_PROD_DISCOUNT Stored Procedure
24
The PRC_CUS_ADD Stored Procedure
25
The PRC_INV_ADD and PRC_LINE_ADD Stored Procedures
26
Testing the PRC_INV_ADD and PRC_LINE_ADD
Procedures
27
Trigger vs Stored Procedure
Stored Procedure diaktifkan secara eksplisit
Trigger diaktifkan oleh event
Source adapted from Mullins, 1995.
28
More Resource on Trigger

• Contsraint Triggers
• http//www-db.stanford.edu/ullman/fcdb/oracle/or
  -triggers.html
• PL/SQL Reference Tutorial http//www.ilook.fsnet
  .co.uk/ora_sql/sqlmain2.htm