Trigger 2

Setelah sebelumnya kita telah mempelajari bagaimana membuat trigger dalam database, Kali ini kita akan mempelajari tentang bagaimana caranya membuat trigger dalam sistem.

Di dalam system terdapat Event-event Yang Mungkin terjadi yaitu :

Event	Possible Value
AFTER SERVERERROR	Trigger akan diaktifkan ketika terjadinya server error
AFTER LOGON	Trigger akan diaktifkan ketiga ada user yang masuk ke database
BEFORE LOGOFF	Trigger akan diaktifkan ketika user ingin keluar dari database
AFTER STARTUP	Trigger akan dijalankan ketika database dijalankan
BEFORE SHUTDOWN	Trigger akan dijalankan ketika database ingin di shut down

Kita dapat menggunakan event-event trigger diatas pada SCHEMA maupun DATABASE tetapi tidak untuk event ShutDown dan StartUp yang hanya dapat dijalankan pada DATABASE saja.

Syntax :

CREATE [OR REPLACE] TRIGGER trigger_name
timing
[database_event1 [OR database_event2 OR ...]]
ON {DATABASE|SCHEMA}
trigger_body

Contoh :

Pada contoh kali ini kita akan membuat sebuah trigger yang digunakan untuk mengetahui kapan dan siapa user yang login dan logoff pada sebuah schema.

Hal pertama yang kita lakukan  adalah membuat sebuah table History yang nantinya akan menampung data tentang siapa yang login dan logoff dari schema

CREATE TABLE History (
Users Varchar2(20),
LogDate Date,
Action VARCHAR2(20)

);

Setelah itu kita buat 2 buah trigger yaitu trigger logon_trig yang digunakan untuk memasukkan detail data user yang logon ke dalam tabel history dan yang kedua adalah trigger logoff_trig yang digunakan untuk memasukkan detail data user yang logoff dari skema.

CREATE OR REPLACE TRIGGER logon_trig
AFTER LOGON ON SCHEMA
BEGIN
INSERT INTO History VALUES (USER, SYSDATE, 'Logging on');
END;
/

CREATE OR REPLACE TRIGGER logoff_trig
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO History VALUES (USER, SYSDATE, 'Logging off');
END;
/

Karena contoh diatas kita menspesifikkan trigger tersebut pada schema (ON SCHEMA), trigger logon dan logoff_trig hanya akan diaktifkan ketika kita login dan logoff pada schema tempat kita membuat trigger login dan logoff_trigger, misalkan kita membuat trigger tersebut pada schema yang dimiliki oleh hr, maka data yang ada adalah data yang keluar dan masuk pada skema hr.

Sedangkan jika kita menspesifikkan trigger tersebut pada DATABASE (ON DATABASE), trigger tersebut akan aktif terhadap schema yang dimiliki oleh semua user. Namun untuk dapat membuat sebuah trigger pada database diperlukan sebuat ADMINISTER DATABASE TRIGGER privilege.

Berikut Privilege yang digunakan dalam trigger :

• CREATE/ALTER/DROP (ANY) TRIGGER privilege, yang digunakan untuk membuat trigger pada schema
• ADMINISTER DATABASE TRIGGER privilege, yang digunakan untuk membuat trigger dalam database
• EXECUTE privilege, yang digunakan apabila trigger yang kita buat diperuntukkan untuk object yang tidak kita miliki

CALL Statement

Dengan Call Statement, kita dapat menjalankan stored procedure di dalam Trigger

Syntax :

CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON table_name
[REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW]
[WHEN condition]
CALL procedure_name
/

Contoh :

CREATE TRIGGER salary_check
BEFORE UPDATE OF salary, job_id ON employees
FOR EACH ROW
WHEN (NEW.job_id <> 'SH_CLERK')
CALL check_salary(:NEW.job_id, :NEW.salary)
/

Business Aplication Scenarios For Implementing Triggers

Dengan adanya trigger kita dapat menggunakannya untuk :

• Security
  Dengan adanya trigger kita dapat membatasi siapa saja yang dapat mengakses object dalam database sesuai dengan nilai yang telah dideklarasikan oleh trigger tersebut.
• Auditing
  Dengan adanya trigger kita dapat melacak aliran data yang terjadi di dalam database
• Data integrity
  Trigger dapat menjaga integritas data
• Referential integrity
  Ketika Oracle Server hanya dapat membuat standard referential integrity rules, Trigger dapat mengimplementasikan nonstandard functionality

Table replication
Trigger dapat menyalin Table ke dalam bentuk replika

• Computing derived data automatically
  Triggers compute derived data values automatically
• Event logging
  Trigger dapat membuat hisrory logging secara transparant

Trigger

Trigger adalah sebuah blok PL/SQL yang disimpan dalam database yang secara otomatis akan teraktivasi ketika terjadi suatu operasi pada sebuah tabel, view, schema, atau database.

Trigger terdapat 2 jenis :

1. Application Trigger
   Yang dimana tigger tersebut akan teraktivasi ketika terjadi suatu event pada sebuah aplikasi tertentu
2. Database Trigger
   Yang dimana trigger tersebut akan teraktivasi ketika terjadi suatu data event(operasi DML(INSERT, UPDATE, DELETE) atau system event (logon or shutdowm) pada sebuah schema atau database dan untuk selanjutnya kita akan membahas tentang trigger ini

TRIGGER TIMING

Terdapat 3 waktu untuk sebuah trigger teraktivasi :

• BEFORE
  Dimana sebuah trigger akan teraktivasi sebelum terjadinya proses DML pada tabel
• AFTER
  Dimana sebuah trigger akan teraktivasi setelah terjadinya proses DML pada tabel
• INSTEAD OF
  Trigger yang hanya berfungsi pada VIEW dan biasanya digunakan untuk mengUpdate data pada view kompleks

Syntax :

CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON object_name
[[REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW
[WHEN (condition)]]
trigger_body

Contoh :

CREATE OR REPLACE TRIGGER backup
BEFORE UPDATE ON employees
BEGIN
RAISE_APPLICATION_ERROR(-20001,'Tabel Employees Tidak Dapat Dirubah');
END;

Trigger diatas berfungsi untuk melarang terjadinya proses UPDATE pada tabel employees dan Ketika kita mencoba untuk mengUpdate tabel employees

UPDATE employees
SET salary = 1000
WHERE employee_id= 100

Hasilnya :

ERROR at line 1:
ORA-20001: Tabel Employees Tidak Dapat Dirubah
ORA-06512: at “HR.BACKUP_SALARY”, line 2
ORA-04088: error during execution of trigger ‘HR.BACKUP_SALARY’

Selain contoh diatas kita juga dapat menspesifikkan pada kolom mana trigger tersebut aktif

Contoh :                       

CREATE OR REPLACE TRIGGER backup
BEFORE UPDATE OF SALARY ON employees
BEGIN
RAISE_APPLICATION_ERROR(-20001,'Data Salary Pada Tabel Employees Tidak Dapat Dirubah');
END;

Trigger diatas akan aktif ketika kita melakukan operasi UPDATE pada Kolom Salary di Tabel employees.

IF-ELSE TRIGGER

contoh :

CREATE OR REPLACE TRIGGER backup2
BEFORE UPDATE OR DELETE OR INSERT ON employees
BEGIN
IF UPDATING THEN
RAISE_APPLICATION_ERROR(-20001, 'Tabel Emp Tidak Dapat DiRubah');
ELSIF DELETING THEN
RAISE_APPLICATION_ERROR(-20002, 'Tabel Emp Tidak Dapat DiHapus');
ELSE
RAISE_APPLICATION_ERROR(-20003, 'Tabel Emp Tidak Dapat DITambah');
END IF;
END;

Ketika kita mencoba menghapus data pada tabel employees

DELETE FROM Employees
WHERE employee_id = 120;

Hasilnya :

ERROR at line 1:
ORA-20001: Tabel Emp Tidak Dapat DiHapus
ORA-06512: at “HR.BACKUP2″, line 5
ORA-04088: error during execution of trigger ‘HR.BACKUP2′

Contoh Trigger After :

CREATE OR REPLACE TRIGGER Konfirmasi
AFTER DELETE OR INSERT OR UPDATE ON Employees
BEGIN
IF DELETING THEN
DBMS_OUTPUT.PUT_LINE ('Data Telah di Delete');
ELSIF UPDATING THEN
DBMS_OUTPUT.PUT_LINE ('Data Telah di Update');
ELSIF INSERTING THEN
DBMS_OUTPUT.PUT_LINE ('Data Telah di Insert');
END IF;
END;

Trigger diatas akan menampilkan pesan ketika kita melakukan operasi DML(INSERT, UPDATE, DELETE) pada tabel employees

Alias Di Dalam Trigger

Di dalam trigger dikenal istilah alias atau referensi, yaitu sejenis variabel yang menyimpan nilai dari suatu kolom di dalam tabel. Alias tersebut terbagi menjadi dua yaitu :new dan :old. Alias :new untuk menyimpan nilai terbaru sedangkan alias :old untuk menyimpan nilai lama.

Ketersediaan Alias Di dalam Trigger

• Pada statement UPDATE, terdapat alias :new dan :old
• Pada statement DELETE, hanya terdapat alias :old
• Pada statement INSERT, hanya terdapat alias :new

Contoh :

CREATE OR REPLACE TRIGGER backup_sal
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO backup_salary(emp_id, old_salary, new_salary, backupdate) VALUES (: old.employee_id, : old.salary, :new.salary, SYSDATE);
END;

Trigger diatas berfungsi untuk memasukkan data employee_id yang lama, salary yang lama, salary yang baru dan tanggal perubahan ke tabel backup_salary. Trigger tersebut teraktivasi Ketika kita mengUpdate salary pada Tabel Employees

UPDATE employees
SET Salary = 2000
WHERE employee_id = 130;

Maka secara otomatis, data yang kita UPDATE tersebut sudah terBackup di tabel backup_salary

Contoh lain:

CREATE OR REPLACE TRIGGER Validasi
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
IF :new.salary < 2000 THEN
RAISE_APPLICATION_ERROR (-20000,'Gaji Minimal $2000');
END IF;
END;

Trigger diatas berfungsi untuk memastikan kalau data salary pada tabel employees yang dimasukkan minimal $2000

Membuat Trigger dalam DDL Statement

Pada contoh-contoh sebelumnya kita telah membuat trigger pada DML statement, pada contoh kali ini kita akan membuat trigger pada DDL statement.

Trigger yang dapat dipasang pada DDL stament yaitu DROP, ALTER, dan CREATE

syntax :

CREATE [OR REPLACE] TRIGGER trigger_name
Timing
[ddl_event1 [OR ddl_event2 OR ...]]
ON {DATABASE|SCHEMA}
trigger_body

Contoh :

CREATE OR REPLACE TRIGGER BackupData
BEFORE DROP ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20005,' Tidak diperbolehkan untuk menghapus table pada schema ini');
END;

Trigger diatas akan mencegah terjadinya pengeksekusian statement DROP pada schema, untuk lebih memahami penggunaan trigger di dalam SCHEMA atau DATABASE, silakan baca artikel Trigger di Oracle Part 2

MANAGING TRIGGER

Mengaktifkan TRIGGER

Syntax :

ALTER TRIGGER trigger_name ENABLE

Menonaktifkan Trigger

Syntax :

ALTER TRIGGER trigger_name DISABLE

Mengaktifkan atau Menonaktifkan Semua Trigger

Syntax :

ALTER TABLE table_name DISABLE | ENABLE
ALL TRIGGERS

Menghapus TRIGGER

Syntax :

DROP TRIGGER nama_trigger

sumber:http://risnotes.com/2012/02/trigger-di-oracle/

Soal Cursor

Setelah lama ga update blog, malah ketemu soal tentang cursor yang menarik

1. Buatlah statement pl/sql dengan menggunakan cursor untuk menampilkan employee_id, department_id, salary, dan rata-rata salary per departemen maksimal 10 baris. Dan Tampilkan juga berapa rows yg ditampilkan.

SET SERVEROUTPUT ON DECLARE emp employees.employee_id%type; dep employees.department_id%type; sal employees.salary%type; avsal employees.salary%type; CURSOR emp_cursor IS SELECT employee_id, salary, department_id,(selectavg(salary) FROM employees WHERE department_id= b.department_id) FROM employees b; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor into emp, sal, dep, avsal; dbms_output.put_line('emp id: ' || emp || ' ' || 'department id : ' || dep || ' ' || 'salary : ' || sal || ' ' || 'average salary : ' || avsal); EXIT WHEN emp_cursor%ROWCOUNT = 10; END LOOP; dbms_output.put_line('jumlah baris= : ' || emp_cursor%ROWCOUNT); CLOSE emp_cursor; end; / Hasilnya : emp id: 198 department id : 50 salary : 2600 average salary : 3475,56 emp id: 199 department id : 50 salary : 2600 average salary : 3475,56 emp id: 200 department id : 10 salary : 4400 average salary : 4400 emp id: 201 department id : 20 salary : 13000 average salary : 10450 emp id: 202 department id : 20 salary : 7900 average salary : 10450 emp id: 203 department id : 40 salary : 6500 average salary : 6500 emp id: 204 department id : 70 salary : 10000 average salary : 10000 emp id: 205 department id : 110 salary : 12000 average salary : 10150 emp id: 206 department id : 110 salary : 8300 average salary : 10150 emp id: 100 department id : 90 salary : 1000 average salary : 11666,67 jumlah baris = : 10 PL/SQL procedure successfully completed.

2. Tampilkanlah first name, last name, dan salary dari tabel employees dengan menggunakan cursor yang memiliki sebuah parameter. Kemudian Tampilkan data karyawan tersebut dimana karyawan tersebut memiliki salary yang nilainya lebih kecil dari salary dari parameter cursor

DECLARE CURSOR cur_emps(salary_param NUMBER) IS SELECT first_name, last_name, salary FROM employees WHERE salary < salary_param; f_name employees.first_name%type; l_name employees.last_name%type; gaji employees.salary%type; BEGIN OPEN cur_emps(2500); LOOP FETCH cur_emps INTO  f_name, l_name, gaji; EXIT WHEN cur_emps%NOTFOUND; DBMS_OUTPUT.PUT_LINE(f_name || ' ' || l_name || ' memiliki gaji: ' || gaji); END LOOP; CLOSE cur_emps; END; / Hasil jika nilai parameter=2500 Steven King memiliki gaji: 1000  James Landry memiliki gaji: 2400  Steven Markle memiliki gaji: 2200  TJ Olson memiliki gaji: 2100  Ki Gee memiliki gaji: 2400  Hazel Philtanker memiliki gaji: 2200  Peter Tucker memiliki gaji: 1000  Tayler Fox memiliki gaji: 2100  PL/SQL procedure successfully completed.

3. Buatlah sebuah cursor yang digunakan untuk mengUPDATE salary karyawan menjadi 2 kali gaji awal dimana id karyawan tersebut adalah 109. Kemudian tampilkan pesan ‘Data Telah diUpdate’ ketika data telah berhasil diUPDATEdan tampilkan pesan ‘Karyawan tidak ditemukan’ ketika employee_id nya tidak terdapat dalam database.

DECLARE CURSOR cur_upd_salary(comp_emp_id NUMBER) IS SELECT salary FROM employees WHERE employee_id = comp_emp_id      FOR UPDATE OF SALARY; id employees.employee_id%type; BEGIN OPEN cur_upd_salary(109); FETCH cur_upd_salary INTO id; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE ('Karyawan tidak ditemukan!'); ELSE UPDATE employees SET salary = salary * 2 WHERE CURRENT OF cur_upd_salary; DBMS_OUTPUT.PUT_LINE('UPDATE SUCCESS!'); END IF; CLOSE cur_upd_salary; END; /

4. Buatlah sebuah table yang sama dengan table employees dengan nama employees2. Kemudian buatlah sebuah cursor yang berguna untuk menghapus data karyawan yang memiliki employee_id = 120. Dan pastikan data yang telah dihapus tidak dapat dikembalikan

CREATE TABLE employees2 AS SELECT * FROM employees; DECLARE CURSOR cur_upd_emp(emp_id varchar2) IS SELECT employee_id FROM employees2 WHERE employee_id = emp_id FOR UPDATE; id employees.employee_id%TYPE; BEGIN   OPEN cur_upd_emp(120); FETCH cur_upd_emp into id;   IF cur_upd_emp%FOUND then DELETE FROM employees2   WHERE CURRENT OF cur_upd_emp;   COMMIT;   ELSE    DBMS_OUTPUT.PUT_LINE('data not found');   END IF;   CLOSE cur_upd_emp; END; /

5. Buatlah sebuah Anonymous block untuk case berikut ini Gunakan CURSOR dan WHERE CURRENT OF cursor sebagai solusi
Case :
Lakukan update terhadap data lokasi beberapa nama department berikut :
- Department “Recruiting” dan “Administration” akan diupdate lokasinya sama dengan  lokasi department  “Human Resources”
- Department  “Retail Sales” dan  “Government Sales” akan diupdate lokasinya sama dengan department “Sales”
Notes :

• Buatlah agar perubahan tersebut tidak dapat dirollback
• Buatlah exception jika terjadi table departments sedang dilock Tampilkan pesan ‘ Row of Departments table is locked’ (error number is –54, dapat menggunakan PRAGMA EXCEPTION_INIT)
• Jika terjadi error lainnya tampilkan RAISE_APPLICATION_ERROR nomor -20001, pesannya ‘Unknown Error‘

DECLARE CURSOR dept_cursor  IS SELECT * FROM departments WHERE department_namein('Recruiting','Administration','Retail Sales','Government Sales') for update; Loc1 departments.location_id%type; Loc2 departments.location_id%type; Loc departments.location_id%type; row_lock EXCEPTION; PRAGMA EXCEPTION_INIT(row_lock, -54); BEGIN SELECT select location_id INTO loc1 from departmentswhere department_name ='Human Resources'; SELECT location_id INTO loc2 from departments wheredepartment_name='Sales'; for dept_record in dept_cursor loop if ( dept_record.department_name ='Recruiting' or dept_record.department_name ='Administration' )  then loc:=loc1;   else loc:=loc2; END IF; UPDATE departments set location_id =loc WHERE current of dept_cursor; COMMIT; end loop; EXCEPTION WHEN row_lock THEN DBMS_OUTPUT.PUT_LINE('Row of Departments table is locked'); WHEN OTHERS THEN RAISE_APPLICATION_ERROR (-20001,'Unknown Error'); end;