330 likes | 660 Views
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
E N D
ORACLE Trigger HasanDwiCahyono
Overviews • Pengertian Trigger • Schema Trigger • Aplikasi Trigger Perancangan Basis Data - Trigger
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
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
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
Schema Trigger: Create Trigger (Lanj.) Perancangan Basis Data - Trigger
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
Schema Trigger: DML Event Clause (Lanj.) Perancangan Basis Data - Trigger
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
Schema Trigger: Referencing Clause Perancangan Basis Data - Trigger
Options: Event-nya • AFTER ataubisajugaBEFORE. • UPDATE ON bisajugaDELETE ON atauINSERT ON. • Dan UPDATE ON dapatjugaUPDATE …OF… ON Perancangan Basis Data - Trigger
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Sumber: • Oracle.com • http://webhome.cs.uvic.ca/~thomo/courses/csc370Spring2009/triggers.ppt Perancangan Basis Data - Trigger