Simple SQL Trigger (SST)

A trigger is a stored procedure that is automatically executed when a modification event occurs on the database server, carried out by the user.

This modification event is known as DML (Data Manipulation Language), which is commonly used for triggers including:

BEFORE INSERT -- executed when data is entered into the table.
AFTER INSERT -- executed after data has been entered into the table.
BEFORE UPDATE -- executed before the data update process.
AFTER UPDATE -- executed after the data update process.
BEFORE DELETE -- executed before the data deletion process.
AFTER DELETE -- executed after the data delete process.

1. Create a table to be used

We will create a feature that records the log of changes in the price of goods in a sales database, where there is a product table as a table for storing product information that has product code, product name and price fields.

then we will create a product_price_log table to store product price change information, the information we will store is product_code, old_price, new_price and change_time.

2. Create MySQL Trigger

Next, we will create a trigger to record changes in product prices when a product record is updated. The syntax for creating a trigger with the above conditions is as follows:

DELIMITER $$
CREATE TRIGGER before_produk_update 
    BEFORE UPDATE ON produk
    FOR EACH ROW 
BEGIN
    INSERT INTO log_harga_produk
    set kode_produk = OLD.kode_produk,
    harga_baru=new.harga,
    harga_lama=old.harga,
    waktu_perubahan = NOW(); 
END$$
DELIMITER;

Script Explanation:

line 2 -- We create a new trigger with the name before_produk_update
line 3 -- In this Trigger we use the BEFORE UPDATE event
line 6 -- SQL query to insert data into the log_harga_produk table

3. Testing Trigger

After everything is finished, we will test the trigger that we created, we will insert some data that we will use for testing:

INSERT INTO `produk` VALUES ('BR001','SEMINGGU JAGO CODEIGNITER',120000);
INSERT INTO `produk` VALUES ('BR002','SEMINGGU JAGO PHP MYSQL',80000);

Next, we try to display the results of the data we have input.

]

The next step is that we will update the product data:

update produk set harga=90000 WHERE kode_produk='BR001'

As you can see, even after we have carried out the data update process, a new record appears in the log_harga_produk table regarding information about changes to the updated product data.

4. How to Display Trigger List

To display a list of triggers on a database, you can use the following command:

SHOW TRIGGERS

5. How to Delete Triggers

Maybe one day you want to delete a trigger that you have created, the method is as follows:

DROP TRIGGER nama_trigger;
//contoh implementasinya
DROP TRIGGER before_produk_update;

6. Closing and Conclusion

The conclusion is that we use this trigger to execute a sql command based on a certain event/condition, although you can create it manually using a certain programming language but with Trigger it certainly helps developers. that's all the tutorial posts for creating triggers on the MySQL database, you can try using other events to deepen your understanding of Triggers on MySQL, hopefully useful.

Exercise

A short note in the 3rd semester DBMS practical class. A lecturer taught us about a feature that is very rarely used in DBMS, maybe yes for a simple system, but a simple system will be smart if it is able to implement "TRIGGER".

Okay, our first penetration is given a case to create a database about data input, yes, it's not far from just about students, and this is also just a stimulant for us students who are still new to the 3rd semester meeting.

OK, so these are the simple instructions.

CREATE TABLE mahasiswa(
nim VARCHAR (10) NOT NULL PRIMARY KEY,
nama VARCHAR (50) NOT NULL,
alamat VARCHAR (100) not null,
jnskel ENUM ("lAKI-LAKI", "Perempuan"),
prodi VARCHAR (20)NOT NULL,
notelp VARCHAR(15)NOT NULL
);

CREATE TABLE matakuliah(
idmatakuliah INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
nama VARCHAR(50) NOT NULL,
sks TINYINT(1) NOT NULL,
semester ENUM('Gasal','Genap')
);

CREATE TABLE dosen(
iddosen INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
nama VARCHAR(50)NOT NULL,
alamat VARCHAR(100)NOT NULL,
notelp VARCHAR(15)NOT NULL
);

CREATE TABLE kuliah(
idkuliah INT(11)NOT NULL PRIMARY KEY AUTO_INCREMENT,
iddosen INT(11)NOT NULL,
idmatakuliah INT(11)NOT NULL,
nim VARCHAR(10)NOT NULL,
nilai ENUM("A", "B", "C", "D", "E"),
thajaran VARCHAR(5)NOT NULL
);

Meanwhile, a simple code example for the trigger looks something like this.

DROP TRIGGER IF EXISTS 'input_mahasiswa';
DELIMITER $$
CREATE TRIGGER input_mahasiswa AFTER INSERT ON mahsiswa
FOR EACH ROW
BEGIN
    INSERT INTO kuliah(nim, thajaran)
    VALUES (NEW.nim, '20141');
END $$

Testing it

Enter via the localhost/academic/main/input student input form
After inputting, make sure the student table and lecture table have added records,
Command To delete trigger
DROP TRIGGER input_student;

Other notes

%Search%

%=represents the character/word before if placed before, after if placed after

We are also asked to create a simple user interface for the following case.

show data
data input
edit data
delete data
search data

Still around CodeIgniter, the control mechanism is mostly determined by the uri segment.

segment starts from base url --> localhost/academic/segmenstart...
example $nim = $this->uri->segment(3);

Source

https://belajarphp.net/tutorial-trigger-mysql/