SQL Store Procedure, Event, dan Trigger

SQL Store Procedure

Stored procedure adalah serangkaian perintah SQL yang disimpan sebagai objek di database dan dapat dieksekusi kapan saja dengan memanggil nama prosedur tersebut. Stored procedure dapat digunakan untuk melakukan tugas-tugas yang kompleks dan sering dilakukan, seperti pengambilan dan pengolahan data, dan dapat menghasilkan output seperti tabel atau hasil yang dikembalikan.

Keuntungan dari penggunaan stored procedure

1. Kinerja yang lebih baik: Stored procedure dapat dieksekusi lebih cepat daripada perintah SQL biasa karena mereka sudah dikompilasi dan disimpan di database.
2. Keamanan: Stored procedure dapat membantu melindungi data dengan membatasi akses ke data melalui objek stored procedure.
3. Penggunaan ulang: Stored procedure dapat digunakan ulang di banyak aplikasi, menghemat waktu dan upaya dalam mengembangkan aplikasi baru.
4. Pemeliharaan yang mudah: Ketika ada perubahan dalam logika bisnis atau struktur database, hanya perlu mengubah stored procedure yang terkait, bukan perlu merubah kode aplikasi.

Contoh penggunaan stored procedure

ketika ingin mengambil data dari beberapa tabel dan melakukan operasi pada data tersebut. Dengan stored procedure, dapat menulis kumpulan perintah SQL yang rumit dan menyimpannya dalam bentuk stored procedure. Setiap kali perlu melakukan operasi yang sama, tinggal memanggil stored procedure tersebut. Misalnya, jika ingin membuat laporan penjualan untuk semua produk dalam database, dapat menulis stored procedure yang mengambil data dari tabel produk dan tabel penjualan, dan menggabungkannya untuk menghasilkan laporan penjualan. Dengan menyimpan logika ini di dalam stored procedure, dapat digunakan ulang kapan saja, tanpa perlu menulis kode yang sama berulang-ulang.

Berikut adalah contoh kode untuk membuat stored procedure pada database SQL Server:

CREATE PROCEDURE [dbo].[GetCustomerOrders]
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM Orders
    WHERE CustomerID = @CustomerID
END

Dalam contoh di atas, stored procedure bernama GetCustomerOrders dibuat di dalam schema dbo pada database SQL Server. Stored procedure ini menerima satu parameter @CustomerID dengan tipe data int dan mengembalikan daftar pesanan dari pelanggan yang memiliki ID yang diberikan.

Untuk menggunakan stored procedure di atas, cukup panggil nama stored procedure tersebut dengan memberikan nilai parameter yang sesuai:

CALL GetCustomerOrders @CustomerID = 123

Dalam contoh di atas, stored procedure GetCustomerOrders dipanggil dengan memberikan nilai parameter 123 untuk parameter @CustomerID. Stored procedure akan mengembalikan daftar pesanan dari pelanggan dengan ID 123.

SQL Event

SQL Event adalah suatu fitur dalam database management system (DBMS) yang memungkinkan pengguna untuk menjadwalkan dan mengatur tugas-tugas tertentu untuk dijalankan secara otomatis pada waktu tertentu. Dengan SQL Event, pengguna dapat mengatur waktu eksekusi tugas secara terjadwal atau segera setelah kondisi tertentu terpenuhi.

Contoh kasus penggunaan SQL Event

untuk melakukan backup database secara otomatis setiap hari pada jam tertentu, menghapus data yang sudah kadaluwarsa setiap minggu, atau mengirimkan email notifikasi setiap kali terjadi perubahan pada tabel tertentu.

Berikut adalah contoh kode SQL Event untuk melakukan backup database MySQL setiap hari pada pukul 02:00 pagi:

CREATE EVENT backup_db
ON SCHEDULE EVERY 1 DAY
STARTS '2023-03-01 02:00:00'
DO
   BEGIN
      mysqldump -u root -p123456 --all-databases > /backup/db-backup.sql
   END

Kode di atas membuat sebuah SQL Event dengan nama “backup_db” yang dijadwalkan untuk berjalan setiap hari pada pukul 02:00 pagi dan akan memulai tugas dengan menjalankan perintah “mysqldump” untuk melakukan backup semua database ke file “db-backup.sql” pada direktori “/backup”.

SQL Trigger

SQL Trigger adalah sebuah fitur dalam database management system (DBMS) yang memungkinkan pengguna untuk menjalankan suatu tindakan (action) secara otomatis setelah sebuah event atau peristiwa tertentu terjadi pada tabel tertentu. Dalam SQL, sebuah trigger bisa digunakan untuk memonitor dan merespon event yang terjadi pada tabel seperti penghapusan, perubahan, atau penambahan data.

Contoh kasus penggunaan SQL Trigger

untuk memperbaharui data pada tabel lain setelah sebuah data dihapus, mengirimkan notifikasi email setelah terjadi perubahan data pada tabel, atau melakukan validasi data sebelum disimpan pada tabel.

Berikut adalah contoh kode SQL Trigger untuk mengirimkan email notifikasi setiap kali sebuah data baru dimasukkan pada tabel “customers”:

CREATE TRIGGER send_email
AFTER INSERT
ON customers
FOR EACH ROW
BEGIN
   DECLARE email_subject VARCHAR(255);
   DECLARE email_body VARCHAR(500);
   SET email_subject = 'New Customer Added';
   SET email_body = CONCAT('A new customer with ID ', NEW.id, ' has been added.');
   -- logic to send email here
END;

Kode di atas membuat sebuah SQL Trigger dengan nama “send_email” yang dijalankan setiap kali sebuah data baru dimasukkan pada tabel “customers”. Setiap kali trigger diaktifkan, maka variabel “email_subject” dan “email_body” akan didefinisikan terlebih dahulu dan setelah itu barulah tindakan untuk mengirim email akan dilakukan.