oracle trigger

ORACLE Trigger

Hasan Dwi Cahyono

Perancangan Basis Data - Trigger2

Overviews Pengertian Trigger Schema Trigger Aplikasi Trigger


Pengertian Trigger Database triggers adalah prosedur yang

tersimpan dalam database dan di aktifkan ketika kondisi tertentu terjadi

Dapat digunakan untuk menambah kapabilitas agar database mampu dikostumasi secara sistematis.

Sebagai contoh, membuat Trigger yang dapat membatasi perintah DML tertentu pada sebuah tabel dan mengijinkan perintah tertentu pada waktu tertentu juga.


Pengertian Trigger (Lanjutan) Database triggers dapat digunakan dalam

sebuah table, schema, atau database. Cara memicunya ketika:

Perintah DML dieksekusi (INSERT, UPDATE, DELETE) terhadap tabel tertentu.

DDL tertentu dieksekusi (contohnya: ALTER, CREATE, DROP) pada sebuah objek pada database atau schema.

Event tertentu terjadi pada database (contohnya: STARTUP, SHUTDOWN, SERVERERROR)


Schema Trigger: Create TriggerCREATE [ OR REPLACE ] TRIGGER [ schema. ]trigger{ BEFORE | AFTER | INSTEAD OF } { dml_event_clause | { ddl_event [ OR ddl_event ]...

| database_event [ OR database_event ]... } ON { [ schema. ]SCHEMA

| DATABASE }

} [ WHEN (condition) ] { pl/sql_block | call_procedure_statement } ;


Schema Trigger: Create Trigger (Lanj.)


Schema Trigger: DML Event Clause{ DELETE | INSERT | UPDATE

[ OF column [, column ]... ] }

[ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] } ]...

ON { [ schema. ]table | [ NESTED TABLE nested_table_column OF ]

[ schema. ] view } [ referencing_clause ] [ FOR EACH ROW ]


Schema Trigger: DML Event Clause (Lanj.)


Schema Trigger: Referencing ClauseREFERENCING { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent }

[ OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent ]...


Schema Trigger: Referencing Clause


Options: Event-nya AFTER atau bisa juga BEFORE.

UPDATE ON bisa juga DELETE ON atau INSERT ON.

Dan UPDATE ON dapat juga UPDATE …OF… ON


Aplikasi Trigger (Buat tabel contoh)CREATE TABLE Project_tab (

Prj_level NUMBER,

Projno NUMBER,

Resp_dept NUMBER

);

CREATE TABLE Emp_tab ( Empno NUMBER NOT NULL,

Ename VARCHAR2(10),

Job VARCHAR2(9),

Mgr NUMBER(4),

Hiredate DATE,

Sal NUMBER(7,2),

Comm NUMBER(7,2),

Deptno NUMBER(2) NOT NULL

);

CREATE TABLE Dept_tab ( Deptno NUMBER(2) NOT NULL,

Dname VARCHAR2(14),

Loc VARCHAR2(13),

Mgr_no NUMBER,

Dept_type NUMBER

);


Contoh Trigger Pada Tabel Terjadi Ketika Insert, Update, dan DeleteCREATE OR REPLACE TRIGGER Print_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab FOR EACH ROW

WHEN (new.Empno > 0) DECLARE

sal_diff number; 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; /

Lakukan Perintah:UPDATE Emp_tab SET sal = sal + 500.00 WHERE deptno = 10; Apa yang terjadi?


Contoh Trigger Pada ViewCREATE OR REPLACE VIEW manager_info AS 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 WHERE e.empno = d.mgr_no AND d.deptno = p.resp_dept;


Contoh Trigger Pada View (Lanjutan)CREATE OR REPLACE TRIGGER manager_info_insertINSTEAD OF INSERT ON manager_infoREFERENCING NEW AS n -- new manager information

FOR EACH ROWDECLARE rowcnt number;BEGIN SELECT COUNT(*) INTO rowcnt FROM Emp_tab WHERE empno = :n.empno; IF rowcnt = 0 THEN INSERT INTO Emp_tab (empno,ename) VALUES (:n.empno, :n.ename); ELSE UPDATE Emp_tab SET Emp_tab.ename = :n.ename WHERE Emp_tab.empno = :n.empno; END IF;

SELECT COUNT(*) INTO rowcnt FROM Dept_tab WHERE deptno = :n.deptno; IF rowcnt = 0 THEN INSERT INTO Dept_tab (deptno, dept_type) VALUES(:n.deptno, :n.dept_type); ELSE UPDATE Dept_tab SET Dept_tab.dept_type = :n.dept_type WHERE Dept_tab.deptno = :n.deptno; END IF; SELECT COUNT(*) INTO rowcnt FROM Project_tab WHERE Project_tab.projno = :n.projno; IF rowcnt = 0 THEN INSERT INTO Project_tab (projno, prj_level) VALUES(:n.projno, :n.prj_level); ELSE UPDATE Project_tab SET Project_tab.prj_level = :n.prj_level WHERE Project_tab.projno = :n.projno; END IF;END;


Contoh Trigger Pada View (Lanjutan) Lakukan Perintah:INSERT INTO manager_info (ename, empno, dept_type, deptno, prj_level, projno) VALUES(‘Andi’, 101, 1, 1, 1,1); Apa yang terjadi?


Firing Triggers Sekali atau Berkali-kali (FOR EACH ROW Option)CREATE TABLE Emp_log (

Emp_id NUMBER, Log_date DATE, New_salary NUMBER, Action VARCHAR2(20)

);

CREATE OR REPLACE TRIGGER Log_salary_increaseAFTER UPDATE ON Emp_tabFOR EACH ROWWHEN (new.Sal > 1000)BEGIN INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action) VALUES (:new.Empno, SYSDATE, :new.SAL, 'NEW SAL');END;

Lakukan perintah:UPDATE Emp_tab SET Sal = Sal + 1000.0 WHERE Deptno = 20;


Trigger Ini Hanya Dijalankan SekaliCREATE OR REPLACE TRIGGER Log_emp_updateAFTER UPDATE ON Emp_tabBEGIN INSERT INTO Emp_log (Log_date, Action)

VALUES (SYSDATE, 'Emp_tab COMMISSIONS CHANGED');END;


Memanggil Java Procedure dari TriggerCREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2)IS language Javaname 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)';

CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab FOR EACH ROWCALL Before_delete (:old.Id, :old.Ename)/


thjvTriggers.javaimport 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 logtab values (“ +

old_id.intValue() + ", '“ +

old_ename.toString() + ", BEFORE DELETE');

stmt.executeUpdate (sql);

stmt.close();

return;

}

}


Latihan Menggunakan tabel Penjual(nama, minuman, harga)

dan sebuah relasi unary, simpan daftar penjual yang menaikkan harga sebesar Rp.1000. Misal relasi Unary-nya kepada: KumpulanPenjual(nama).

CREATE TABLE Penjual( minuman VARCHAR(10), nama VARCHAR(13), harga FLOAT);

CREATE TABLE KumpulanPenjual( nama VARCHAR(13));


TriggerCREATE OR REPLACE TRIGGER PriceTrigAFTER UPDATE OF price ON PenjualFOR EACH ROWWHEN (new.harga > old.harga + 1000)BEGIN

INSERT INTO KumpulanPenjualVALUES(:new.nama);

END;/


Contoh Lainnya:CREATE TABLE emp (

empno INT,ename VARCHAR(30),deptno INT, sal FLOAT, comm FLOAT

);

CREATE OR REPLACE TRIGGER emp_comm_trig BEFORE INSERT ON emp FOR EACH ROWBEGIN IF :NEW.deptno = 30 THEN :NEW.comm := :NEW.sal * .4; END IF;END;/


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 12009006 ALLEN 30 4500 1800


Trigger:Membatalkan Perintah dengan Error Trigger dapat digunakan untuk melukan pengecekan contrain.

Perintah-nya: RAISE_APPLICATION_ERROR. Perintah yang mengaktifkan Trigger (insert, update, atau delete)

akan dibatalkan. Contohnya, perintah berikut ini memaksa nilai input harus Person.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; /


Statement-Level Trigger Ketika operasi insert, update, atau delete operation

terjadi pada tabel emp, sebuah record akan ditambahkan ke tabel empauditlog yang mencatat date, user, dan action.

Sebelumnya, buat tabel empauditlog table:

CREATE TABLE empauditlog ( audit_date DATE, audit_user VARCHAR2(20), audit_desc VARCHAR2(20));


TriggerCREATE OR REPLACE TRIGGER emp_audit_trig AFTER INSERT OR UPDATE OR DELETE ON empDECLARE v_action VARCHAR2(20);BEGIN IF INSERTING THEN v_action := 'Added employee(s)'; ELSEIF UPDATING THEN v_action := 'Updated employee(s)'; ELSEIF DELETING THEN v_action := 'Deleted employee(s)'; END IF; INSERT INTO empauditlog VALUES (SYSDATE, USER, v_action);END;/


Sekarang jalankan: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 empno IN (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)

29

Hal-hal Lain dalam Trigger Viewing Defined Triggers

Untuk melihat Trigger yang tersimpan, gunakan perintah: SELECT * FROM USER_TRIGGERS[/DBA_TRIGGERS ]WHERE TABLE_NAME = ‘MY_TABLE’;

Melihat isi (code) Trigger: SELECT textFROM user_sourceWHERE name = 'PRICETRIG' ORDER BY line;

Menghapus TriggersDROP TRIGGER <trigger_name>;

Disabling atau Enabling TriggersALTER TRIGGER <trigger_name> {DISABLE|ENABLE};

Harus UpperCase?

Perancangan Basis Data - Trigger


Sumber: Oracle.com http://webhome.cs.uvic.ca/~thomo/courses/

csc370Spring2009/triggers.ppt

oracle trigger

Documents