1 / 28

Triggers & Stored Procedure

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.

trista
Download Presentation

Triggers & Stored Procedure

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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 Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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. Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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 Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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 Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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]; Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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. Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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 Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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 Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  11. The PRODUCT Table Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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. Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  13. Verifying the TRG_PRODUCT_REORDER Trigger Execution Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  14. The P_REORDER ValueMismatch After Update of the P_MIN Attribute Jika kita mengubah P_MIN dari product “2232/QWE”, P_ONHAND < P_MIN, namun P_REORDER masih 0, Kenapa ? Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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 <= P_MIN; END; Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  16. Jika P_ONHAND pada “11QER/31” diupdate menjadi 29 sehingga nilainya lebih besar dari P_MIN, ternyata P_REORDER masih 1. Kenapa ? Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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 <= :NEW.P_MIN THEN :NEW.P_REORDER := 1 ELSE :NEW.P_REORDER := 0 END IF; END; Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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'; Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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) Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  20. Keuntungan stored procedure • Mengurangi network traffic dan meningkatkan performance • Tidak ada transmisi individual SQL statements pada network Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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, …) Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  22. Creating the PRC_PROD_DISCOUNT Stored Procedure Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  23. Results of the PRC_PROD_DISCOUNT Stored Procedure Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  24. The PRC_CUS_ADD Stored Procedure Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  25. The PRC_INV_ADD and PRC_LINE_ADD Stored Procedures Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  26. Testing the PRC_INV_ADD and PRC_LINE_ADD Procedures Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  27. Trigger vs Stored Procedure Stored Procedure diaktifkan secara eksplisit Trigger diaktifkan oleh event Source: adapted from Mullins, 1995. Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

  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 Elmasri and Navathe, Fundamentals of Database Systems, Fourth Edition Revised by IB & SAM, Fasilkom UI, 2005

More Related