Cara Membuat Trigger MySQL AFTER DELETE

Panduan MySQL ini menjelaskan Cara Membangun Trigger MySQL AFTER DELETE dengan sintaks dan contoh.

Penjelasan

Kegunaan Trigger MySQL AFTER DELETE yakni berarti MySQL akan memicu pemicu ini setelah operasi DELETE dijalankan.

Syntax

Syntax bagi membangun Trigger MySQL AFTER DELETE yakni

CREATE TRIGGER trigger_name<br />
AFTER DELETE<br />
   ON table_name FOR EACH ROW</p>
<p>BEGIN</p>
<p>   — variable declarations</p>
<p>   — trigger code</p>
<p>END;

CREATE TRIGGER trigger_name

AFTER DELETE

ON table_name FOR EACH ROW

BEGIN

— variable declarations

— trigger code

END;

Penjelasan Parameter

trigger_name
Nama pemicu bagi yang akan dibangun
AFTER DELETE
Ini menunjukkan bahwa pemicu akan berfungsi setelah operasi DELETE dijalankan.
table_name
Nama tabel yang memicu pemicunya.

Pembatasan

Tak dapat membangun Trigger di suatu tampilan atau on a view
Tak dapat memperbarui nilai NEW.
Tak dapat memperbarui nilai OLD.

Contoh

Mari kita lihat contoh bagaimana Membangun Trigger MySQL AFTER DELETE menggunakan pernyataan CREATE TRIGGER di MySQL.
Seandainya teman-teman mempunyai tabel yang dibangun selaku berikut:

CREATE TABLE contacts<br />
( contact_id INT(11) NOT NULL AUTO_INCREMENT,<br />
  last_name VARCHAR(30) NOT NULL,<br />
  first_name VARCHAR(25),<br />
  birthday DATE,<br />
  created_date DATE,<br />
  created_by VARCHAR(30),<br />
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)<br />
);

CREATE TABLE contacts

( contact_id INT(11) NOT NULL AUTO_INCREMENT,

last_name VARCHAR(30) NOT NULL,

first_name VARCHAR(25),

birthday DATE,

created_date DATE,

created_by VARCHAR(30),

CONSTRAINT contacts_pk PRIMARY KEY (contact_id)

);

Kita kemudian dapat menggunakan pernyataan CREATE TRIGGER bagi Membangun Trigger MySQL AFTER DELETE selaku berikut:

DELIMITER //</p>
<p>CREATE TRIGGER contacts_after_delete<br />
AFTER DELETE<br />
   ON contacts FOR EACH ROW</p>
<p>BEGIN</p>
<p>   DECLARE vUser varchar(50);</p>
<p>   — Find username of person performing the DELETE into table<br />
   SELECT USER() INTO vUser;</p>
<p>   — Insert record into audit table<br />
   INSERT INTO contacts_audit<br />
   ( contact_id,<br />
     deleted_date,<br />
     deleted_by)<br />
   VALUES<br />
   ( OLD.contact_id,<br />
     SYSDATE(),<br />
     vUser );</p>
<p>END; //</p>
<p>DELIMITER ;

DELIMITER //

CREATE TRIGGER contacts_after_delete

AFTER DELETE

ON contacts FOR EACH ROW

BEGIN

DECLARE vUser varchar(50);

— Find username of person performing the DELETE into table

SELECT USER() INTO vUser;

— Insert record into audit table

INSERT INTO contacts_audit

( contact_id,

deleted_date,

deleted_by)

VALUES

( OLD.contact_id,

SYSDATE(),

vUser );

END; //

DELIMITER ;

sumber :kursuswebprogramming.com/blog/

(Visited 67 times, 1 visits today)

Penjelasan

Syntax

Penjelasan Parameter

Pembatasan

Contoh

Leave a Reply Cancel reply