1 / 30

ORACLE Trigger

ORACLE Trigger. Hasan Dwi Cahyono. Overviews. Pengertian Trigger Schema Trigger Aplikasi Trigger. Pengertian Trigger. Database triggers adalah prosedur yang tersimpan dalam database dan di aktifkan ketika kondisi tertentu terjadi

kedma
Download Presentation

ORACLE Trigger

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. ORACLE Trigger HasanDwiCahyono

  2. Overviews • Pengertian Trigger • Schema Trigger • Aplikasi Trigger Perancangan Basis Data - Trigger

  3. Pengertian Trigger • Database triggers adalahprosedur yang tersimpandalam database dan di aktifkanketikakondisitertentuterjadi • Dapatdigunakanuntukmenambahkapabilitas agar database mampudikostumasisecarasistematis. • Sebagaicontoh, membuatTrigger yang dapatmembatasiperintah DML tertentupadasebuahtabeldanmengijinkanperintahtertentupadawaktutertentujuga. Perancangan Basis Data - Trigger

  4. Pengertian Trigger (Lanjutan) • Database triggers dapatdigunakandalamsebuah table, schema, ataudatabase. • Cara memicunyaketika: • Perintah DML dieksekusi (INSERT, UPDATE, DELETE) terhadaptabeltertentu. • DDL tertentudieksekusi (contohnya: ALTER, CREATE, DROP) padasebuahobjekpada database atau schema. • Event tertentuterjadipada database (contohnya:STARTUP, SHUTDOWN, SERVERERROR) Perancangan Basis Data - Trigger

  5. Schema Trigger: Create Trigger CREATE [ OR REPLACE ] TRIGGER [ schema. ]trigger { BEFORE | AFTER | INSTEAD OF } { dml_event_clause | { ddl_event [ ORddl_event ]... | database_event [ ORdatabase_event ]... } ON { [ schema. ]SCHEMA | DATABASE } } [ WHEN (condition) ] { pl/sql_block | call_procedure_statement } ; Perancangan Basis Data - Trigger

  6. Schema Trigger: Create Trigger (Lanj.) Perancangan Basis Data - Trigger

  7. Schema Trigger: DML Event Clause { DELETE | INSERT | UPDATE [ OF column [, column ]... ] } [ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] } ]... ON{ [ schema. ]table | [ NESTED TABLEnested_table_columnOF ] [ schema. ] view } [ referencing_clause ] [ FOREACHROW ] Perancangan Basis Data - Trigger

  8. Schema Trigger: DML Event Clause (Lanj.) Perancangan Basis Data - Trigger

  9. Schema Trigger: Referencing Clause REFERENCING { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent } [ OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent ]... Perancangan Basis Data - Trigger

  10. Schema Trigger: Referencing Clause Perancangan Basis Data - Trigger

  11. Options: Event-nya • AFTER ataubisajugaBEFORE. • UPDATE ON bisajugaDELETE ON atauINSERT ON. • Dan UPDATE ON dapatjugaUPDATE …OF… ON Perancangan Basis Data - Trigger

  12. Aplikasi Trigger (Buattabelcontoh) CREATE TABLEDept_tab( Deptno NUMBER(2) NOT NULL, Dname VARCHAR2(14), Loc VARCHAR2(13), Mgr_no NUMBER, Dept_type NUMBER ); CREATE TABLEProject_tab ( Prj_level NUMBER, Projno NUMBER, Resp_dept NUMBER ); CREATE TABLEEmp_tab ( EmpnoNUMBER NOT NULL, EnameVARCHAR2(10), Job VARCHAR2(9), MgrNUMBER(4), HiredateDATE, Sal NUMBER(7,2), CommNUMBER(7,2), DeptnoNUMBER(2) NOT NULL ); Perancangan Basis Data - Trigger

  13. Contoh Trigger PadaTabel • TerjadiKetikaInsert, Update, danDelete CREATE OR REPLACE TRIGGERPrint_salary_changes BEFOREDELETE OR INSERT OR UPDATE ON Emp_tab FOR EACH ROW WHEN (new.Empno > 0) DECLARE sal_diffnumber; BEGIN sal_diff:= :new.sal - :old.sal; dbms_output.put('Old salary: ' || :old.sal); dbms_output.put(' New salary: ' || :new.sal); dbms_output.put_line(' Difference ' || sal_diff); END; / • LakukanPerintah: UPDATE Emp_tab SET sal = sal + 500.00 WHERE deptno = 10; • Apa yang terjadi? Perancangan Basis Data - Trigger

  14. Contoh Trigger Pada View CREATE OR REPLACE VIEWmanager_infoAS SELECT e.ename, e.empno, d.dept_type, d.deptno, p.prj_level, p.projno FROM Emp_tab e, Dept_tab d, Project_tab p WHEREe.empno = d.mgr_no ANDd.deptno = p.resp_dept; Perancangan Basis Data - Trigger

  15. Contoh Trigger PadaView (Lanjutan) CREATE OR REPLACE TRIGGER manager_info_insert INSTEAD OF INSERT ON manager_info REFERENCING NEW ASn -- new manager information FOR EACH ROW DECLARE rowcnt number; BEGIN SELECT COUNT(*) INTOrowcnt FROMEmp_tabWHEREempno = :n.empno; IF rowcnt = 0 THEN INSERT INTO Emp_tab (empno,ename) VALUES(:n.empno, :n.ename); ELSE UPDATEEmp_tab SETEmp_tab.ename = :n.ename WHEREEmp_tab.empno = :n.empno; END IF; SELECT COUNT(*) INTOrowcnt FROMDept_tab WHERE deptno = :n.deptno; IF rowcnt = 0 THEN INSERT INTODept_tab (deptno, dept_type) VALUES(:n.deptno, :n.dept_type); ELSE UPDATEDept_tab SETDept_tab.dept_type = :n.dept_type WHEREDept_tab.deptno = :n.deptno; END IF; SELECT COUNT(*) INTOrowcnt FROMProject_tab WHEREProject_tab.projno = :n.projno; IF rowcnt = 0 THEN INSERT INTOProject_tab (projno, prj_level) VALUES(:n.projno, :n.prj_level); ELSE UPDATEProject_tab SETProject_tab.prj_level = :n.prj_level WHEREProject_tab.projno = :n.projno; END IF; END; Perancangan Basis Data - Trigger

  16. Contoh Trigger Pada View (Lanjutan) • LakukanPerintah: INSERT INTO manager_info (ename, empno, dept_type, deptno, prj_level, projno) VALUES(‘Andi’, 101, 1, 1, 1,1); • Apa yang terjadi? Perancangan Basis Data - Trigger

  17. Firing Triggers SekaliatauBerkali-kali (FOR EACH ROW Option) CREATE TABLE Emp_log( Emp_idNUMBER, Log_dateDATE, New_salaryNUMBER, Action VARCHAR2(20) ); CREATE OR REPLACE TRIGGER Log_salary_increase AFTER UPDATE ONEmp_tab FOR EACH ROW WHEN (new.Sal > 1000) BEGIN INSERT INTOEmp_log (Emp_id, Log_date, New_salary, Action) VALUES(:new.Empno, SYSDATE, :new.SAL, 'NEW SAL'); END; Lakukanperintah: UPDATEEmp_tabSET Sal = Sal + 1000.0 WHEREDeptno = 20; Perancangan Basis Data - Trigger

  18. Trigger IniHanyaDijalankanSekali CREATE OR REPLACE TRIGGER Log_emp_update AFTER UPDATE ON Emp_tab BEGIN INSERT INTO Emp_log (Log_date, Action) VALUES(SYSDATE, 'Emp_tabCOMMISSIONS CHANGED'); END; Perancangan Basis Data - Trigger

  19. Memanggil Java Procedure dari Trigger CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2) IS language Java name 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)'; CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab FOR EACH ROW CALLBefore_delete (:old.Id, :old.Ename) / Perancangan Basis Data - Trigger

  20. thjvTriggers.java import java.sql.* import java.io.* import oracle.sql.* import oracle.oracore.* public class thjvTriggers { public state void beforeDelete(NUMBER old_id, CHAR old_name) Throws SQLException, CoreException { Connection conn = JDBCConnection.defaultConnection(); Statement stmt = conn.CreateStatement(); String sql = “insert into logtabvalues (“ + old_id.intValue() + ", '“ + old_ename.toString() + ", BEFORE DELETE'); stmt.executeUpdate(sql); stmt.close(); return; } } Perancangan Basis Data - Trigger

  21. Latihan • MenggunakantabelPenjual(nama, minuman, harga)dansebuahrelasi unary, simpandaftarpenjual yang menaikkanhargasebesar Rp.1000. • Misalrelasi Unary-nyakepada: KumpulanPenjual(nama). CREATE TABLE Penjual( minuman VARCHAR(10), namaVARCHAR(13), hargaFLOAT ); CREATE TABLE KumpulanPenjual( namaVARCHAR(13) ); Perancangan Basis Data - Trigger

  22. Trigger CREATE OR REPLACE TRIGGER PriceTrig AFTER UPDATE OF price ON Penjual FOR EACH ROW WHEN (new.harga> old.harga+ 1000) BEGIN INSERT INTO KumpulanPenjual VALUES(:new.nama); END; / Perancangan Basis Data - Trigger

  23. ContohLainnya: CREATE TABLE emp ( empno INT, ename VARCHAR(30), deptno INT, sal FLOAT, comm FLOAT ); CREATE OR REPLACETRIGGERemp_comm_trig BEFORE INSERT ON emp FOR EACH ROW BEGIN IF :NEW.deptno = 30 THEN :NEW.comm := :NEW.sal * .4; END IF; END; / Perancangan Basis Data - Trigger

  24. Latihan: INSERT INTO emp VALUES (9005,'ROBERS',30, 3000,NULL); INSERT INTO emp VALUES (9006,'ALLEN',30, 4500,NULL); SELECT * FROM emp WHERE empno IN (9005, 9006); EMPNO ENAME DEPTNO SAL COMM ----------------------------------- 9005 ROBERS 30 3000 1200 9006 ALLEN 30 4500 1800 Perancangan Basis Data - Trigger

  25. Trigger:MembatalkanPerintahdengan Error • Trigger dapatdigunakanuntukmelukanpengecekancontrain. • Perintah-nya: RAISE_APPLICATION_ERROR. • Perintah yang mengaktifkan Trigger (insert, update, ataudelete) akandibatalkan. • Contohnya, perintahberikutinimemaksanilai input harusPerson.age>= 0: CREATE TABLE Person(age INT); CREATE TRIGGER PersonCheckAge AFTER INSERT OR UPDATE OF age ON Person FOR EACH ROW BEGIN IF (:new.age < 0) THEN RAISE_APPLICATION_ERROR(-20000, 'no negative age allowed'); END IF; END; / Perancangan Basis Data - Trigger

  26. Statement-Level Trigger • Ketikaoperasi insert, update, ataudelete operation terjadipadatabelemp, sebuah record akanditambahkanketabelempauditlog yang mencatatdate, user, danaction. • Sebelumnya, buattabelempauditlog table: CREATE TABLE empauditlog( audit_date DATE, audit_user VARCHAR2(20), audit_desc VARCHAR2(20) ); Perancangan Basis Data - Trigger

  27. Trigger CREATE OR REPLACE TRIGGER emp_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_actionVARCHAR2(20); BEGIN IFINSERTINGTHEN v_action := 'Added employee(s)'; ELSEIFUPDATINGTHEN v_action := 'Updated employee(s)'; ELSEIFDELETINGTHEN v_action := 'Deleted employee(s)'; END IF; INSERT INTO empauditlog VALUES (SYSDATE, USER, v_action); END; / Perancangan Basis Data - Trigger

  28. Sekarangjalankan: INSERT INTO emp (empno, ename, deptno) VALUES (9001,'SMITH',50); INSERT INTO emp(empno, ename, deptno) VALUES (9002,'JONES',50); UPDATE emp SET ename = 'SMITH BROWN' WHERE empno=9001; DELETE FROM emp WHERE empnoIN (9001, 9002); SELECT TO_CHAR(AUDIT_DATE,'DD-MON-YY HH24:MI:SS') AS "AUDIT DATE", audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC; AUDIT DATE AUDIT_USER AUDIT_DESC ------------------ -------------------- -------------------- 08-FEB-08 09:43:02 THOMO Added employee(s) 08-FEB-08 09:43:02 THOMO Deleted employee(s) 08-FEB-08 09:43:02 THOMO Updated employee(s) 08-FEB-08 09:43:02 THOMO Added employee(s) Perancangan Basis Data - Trigger

  29. Hal-hal Lain dalam Trigger • Viewing Defined Triggers • Untukmelihat Trigger yang tersimpan, gunakanperintah: SELECT* FROMUSER_TRIGGERS[/DBA_TRIGGERS ] WHERETABLE_NAME = ‘MY_TABLE’; • Melihatisi (code) Trigger: SELECT text FROMuser_source WHERE name = 'PRICETRIG' ORDER BY line; • MenghapusTriggers DROP TRIGGER <trigger_name>; • Disabling atauEnabling Triggers ALTERTRIGGER<trigger_name> {DISABLE|ENABLE}; HarusUpperCase? Perancangan Basis Data - Trigger

  30. Sumber: • Oracle.com • http://webhome.cs.uvic.ca/~thomo/courses/csc370Spring2009/triggers.ppt Perancangan Basis Data - Trigger

More Related