Apa itu trigger di mysql
Introduction on TriggersA trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT, UPDATE, or DELETE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail. Show Uses for triggers:
Benefits of using triggers in business:
Implementation of SQL triggers is based on the SQL standard. It supports constructs that are common to most programming languages. It supports the declaration of local variables, statements to control the flow of the procedure, assignment of expression results to variables, and error handling. MySQL Triggers We assume that you are habituated with "MySQL Stored Procedures", if not you can read our MySQL Procedures tutorial. You can use the following statements of MySQL procedure in triggers:
How to create MySQL triggers? A trigger is a named database object that is associated with a table, and it activates when a particular event (e.g. an insert, update or delete) occurs for the table. The statement CREATE TRIGGER creates a new trigger in MySQL. Here is the syntax : Syntax: CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } Explanation: DEFINER clause: The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER().
trigger_name: All triggers must have unique names within a schema. Triggers in different schemas can have the same name. trigger_time: trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified. trigger_event: trigger_event indicates the kind of operation that activates the trigger. These trigger_event values are permitted:
tbl_name : The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view. trigger_body: trigger_body is the statement to execute when the trigger activates. To execute multiple statements, use the BEGIN ... END compound statement construct. This also enables you to use the same statements that are permissible within stored routines. Here is a simple example: mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum + NEW.amount; Query OK, 0 rows affected (0.06 sec) In the above example, there is new keyword 'NEW' which is a MySQL extension to triggers. There is two MySQL extension to triggers 'OLD' and 'NEW'. OLD and NEW are not case sensitive.
A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.) Sample database, table, table structure, table records for various examples Database Name: hr Structure of the table : emp_details Records of the table (on some fields): emp_details mysql> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; Tool to create MySQL Triggers You can write a procedure in MySQL command line tool or you can use MySQL workbench which is an excellent front-end tool (here we have used version 5.3 CE). MySQL command line tool: - Select MySQL command Client from Start menu: Selecting MySQL command prompt following screen will come: After a successful login, you can access the MySQL command prompt: Now you can write your own trigger on a specific table, see the following example : MySQL workbench (5.3 CE): - Select MySQL workbench from Start menu : After selecting MySQL workbench following login screen will come: Now input the login details : After successful login, a new screen will come and from the object browser panel select a database: After selecting the database, select the tables: Now right click on emp_details a window pops up, click on Alter Table: Clicking on " Alter Table " details of emp_details will come: Now click on Trigger tab in the previous section, then select the Timing/Event it may be AFTER DELETE, AFTER INSERT, AFTER UPDATE or BEFORE DELETE, BEFORE INSERT OR BEFORE UPDATE. Let we select AFTER INSERT, you also notice that there is a button Add Trigger. Clicking on Add Trigger button a default code on trigger will come on the basis of choosing Timing/Event: Trigger Name: emp_details_AINS USE `hr`; DELIMITER $$ CREATE TRIGGER `emp_details_AINS` AFTER INSERT ON emp_details FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one After completing the code, click on apply button. Note: See a new text Delete Trigger has come in Add Trigger button. Clicking on this you can delete the trigger. Finally you can review the script once again, as there is no error, let click on Apply button: This the final window before finish. Let click on Finish button. If you take a look at the schema, you will see emp_details_AINS trigger under the emp_details table as follows: MySQL Trigger : Example AFTER INSERT In the following example, we have two tables: emp_details and log_emp_details. To insert some information into log_ emp_details table (which have three fields employee id and salary and edttime) every time, when an INSERT happen into emp_details table we have used the following trigger : DELIMITER $$ USE `hr` $$ CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `hr`.`emp_details_AINS` AFTER INSERT ON `hr`.`emp_details` FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one BEGIN INSERT INTO log_emp_details VALUES(NEW.employee_id, NEW.salary, NOW()); END$$ Records of the table (on some columns) : emp_details mysql> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; +-------------+------------+-----------+---------+----------+----------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | JOB_ID | SALARY | COMMISSION_PCT | +-------------+------------+-----------+---------+----------+----------------+ | 100 | Steven | King | AD_PRES | 24000.00 | 0.10 | | 101 | Neena | Kochhar | AD_VP | 17000.00 | 0.50 | | 102 | Lex | De Haan | AD_VP | 17000.00 | 0.50 | | 103 | Alexander | Hunold | IT_PROG | 9000.00 | 0.25 | | 104 | Bruce | Ernst | IT_PROG | 6000.00 | 0.25 | | 105 | David | Austin | IT_PROG | 4800.00 | 0.25 | +-------------+------------+-----------+---------+----------+----------------+ 6 rows in set (0.00 sec) Records of the table (all columns) : log_emp_details mysql> SELECT * FROM log_emp_details; +-------------+----------+---------------------+ | emp_details | SALARY | EDTTIME | +-------------+----------+---------------------+ | 100 | 24000.00 | 2011-01-15 00:00:00 | | 101 | 17000.00 | 2010-01-12 00:00:00 | | 102 | 17000.00 | 2010-09-22 00:00:00 | | 103 | 9000.00 | 2011-06-21 00:00:00 | | 104 | 6000.00 | 2012-07-05 00:00:00 | | 105 | 4800.00 | 2011-06-21 00:00:00 | +-------------+----------+---------------------+ 6 rows in set (0.02 sec) Now insert one record in emp_details table see the records both in emp_details and log_emp_details tables : mysql> INSERT INTO emp_details VALUES(236, 'RABI', 'CHANDRA', 'RABI','590.423.45700', '2013-01-12', 'AD_VP', 15000, .5); Query OK, 1 row affected (0.07 sec) mysql> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; +-------------+------------+-----------+---------+----------+----------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | JOB_ID | SALARY | COMMISSION_PCT | +-------------+------------+-----------+---------+----------+----------------+ | 100 | Steven | King | AD_PRES | 24000.00 | 0.10 | | 101 | Neena | Kochhar | AD_VP | 17000.00 | 0.50 | | 102 | Lex | De Haan | AD_VP | 17000.00 | 0.50 | | 103 | Alexander | Hunold | IT_PROG | 9000.00 | 0.25 | | 104 | Bruce | Ernst | IT_PROG | 6000.00 | 0.25 | | 105 | David | Austin | IT_PROG | 4800.00 | 0.25 | | 236 | RABI | CHANDRA | AD_VP | 15000.00 | 0.50 | +-------------+------------+-----------+---------+----------+----------------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM log_emp_details; +-------------+----------+---------------------+ | emp_details | SALARY | EDTTIME | +-------------+----------+---------------------+ | 100 | 24000.00 | 2011-01-15 00:00:00 | | 101 | 17000.00 | 2010-01-12 00:00:00 | | 102 | 17000.00 | 2010-09-22 00:00:00 | | 103 | 9000.00 | 2011-06-21 00:00:00 | | 104 | 6000.00 | 2012-07-05 00:00:00 | | 105 | 4800.00 | 2011-06-21 00:00:00 | | 236 | 15000.00 | 2013-07-15 16:52:24 | +-------------+----------+---------------------+ 7 rows in set (0.00 sec) MySQL Trigger : Example BEFORE INSERT In the following example, before insert a new record in emp_details table, a trigger check the column value of FIRST_NAME, LAST_NAME, JOB_ID and Here is the trigger code : USE `hr`; DELIMITER $$ CREATE TRIGGER `emp_details_BINS` BEFORE INSERT ON emp_details FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one BEGIN SET NEW.FIRST_NAME = TRIM(NEW.FIRST_NAME); SET NEW.LAST_NAME = TRIM(NEW.LAST_NAME); SET NEW.JOB_ID = UPPER(NEW.JOB_ID);END; $$Now insert a row into emp_details table (check the FIRST_NAME, LAST_NAME, JOB_ID columns) : mysql> INSERT INTO emp_details VALUES (334, ' Ana ', ' King', 'ANA', '690.432.45701', '2013-02-05', 'it_prog', 17000, .50); Now list the following fields of emp_details : mysql> SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, SALARY, COMMISSION_PCT FROM emp_details; +-------------+------------+-----------+---------+----------+----------------+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | JOB_ID | SALARY | COMMISSION_PCT | +-------------+------------+-----------+---------+----------+----------------+ | 100 | Steven | King | AD_PRES | 24000.00 | 0.10 | | 101 | Neena | Kochhar | AD_VP | 17000.00 | 0.50 | | 102 | Lex | De Haan | AD_VP | 17000.00 | 0.50 | | 103 | Alexander | Hunold | IT_PROG | 9000.00 | 0.25 | | 104 | Bruce | Ernst | IT_PROG | 6000.00 | 0.25 | | 105 | David | Austin | IT_PROG | 4800.00 | 0.25 | | 236 | RABI | CHANDRA | AD_VP | 15000.00 | 0.50 | | 334 | Ana | King | IT_PROG | 17000.00 | 0.50 | +-------------+------------+-----------+---------+----------+----------------+ 8 rows in set (0.00 sec) See the last row : FIRST_NAME - > ' Ana ' has changed to 'Ana' MySQL Trigger : Example AFTER UPDATE We have two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS. stu_log table has two columns user_id and description. mysql> SELECT * FROM STUDENT_MAST; +------------+------------------+----------+ | STUDENT_ID | NAME | ST_CLASS | +------------+------------------+----------+ | 1 | Steven King | 7 | | 2 | Neena Kochhar | 8 | | 3 | Lex De Haan | 8 | | 4 | Alexander Hunold | 10 | +------------+------------------+----------+ 4 rows in set (0.00 sec) Let we promote all the students in next class i.e. 7 will be 8, 8 will be 9 and so on. After updating a single row in student_mast table a new row will be inserted in stu_log table where we will store the current user id and a small description regarding the current update. Here is the trigger code : -- Full Trigger DDL Statements -- Note: Only CREATE TRIGGER statements are allowed DELIMITER $$ USE `test` $$ CREATE DEFINER=`root`@`127.0.0.1` TRIGGER `test`.`student_mast_AUPD` AFTER UPDATE ON `test`.`student_mast`FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one BEGIN INSERT into stu_log VALUES (user(), CONCAT('Update Student Record ', OLD.NAME,' Previous Class :',OLD.ST_CLASS,' Present Class ', NEW.st_class)); END $$ After update STUDENT_MAST table : mysql> UPDATE STUDENT_MAST SET ST_CLASS = ST_CLASS + 1; Query OK, 4 rows affected (0.20 sec) Rows matched: 4 Changed: 4 Warnings: 0 The trigger show you the updated records in 'stu_log'. Here is the latest position of STUDENT_MAST and STU_LOG tables : mysql> SELECT * FROM STUDENT_MAST; +------------+------------------+----------+ | STUDENT_ID | NAME | ST_CLASS | +------------+------------------+----------+ | 1 | Steven King | 8 | | 2 | Neena Kochhar | 9 | | 3 | Lex De Haan | 9 | | 4 | Alexander Hunold | 11 | +------------+------------------+----------+ 4 rows in set (0.00 sec)mysql> SELECT * FROM STU_LOG; +----------------+---------------------------------------------------------------------------+ | user_id | description | +----------------+---------------------------------------------------------------------------+ | [email protected] | Update Student Record Steven King Previous Class :7 Present Class 8 | | [email protected] | Update Student Record Neena Kochhar Previous Class :8 Present Class 9 | | [email protected] | Update Student Record Lex De Haan Previous Class :8 Present Class 9 | | [email protected] | Update Student Record Alexander Hunold Previous Class :10 Present Class 11| +----------------+---------------------------------------------------------------------------+ 4 rows in set (0.00 sec) MySQL Trigger : Example BEFORE UPDATE We have a table student_marks with 10 columns and 4 rows. There are data only in STUDENT_ID and NAME columns. mysql> SELECT * FROM STUDENT_MARKS; +------------+------------------+------+------+------+------+------+-------+-----------+-------+ | STUDENT_ID | NAME | SUB1 | SUB2 | SUB3 | SUB4 | SUB5 | TOTAL | PER_MARKS | GRADE | +------------+------------------+------+------+------+------+------+-------+-----------+-------+ | 1 | Steven King | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 2 | Neena Kochhar | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 3 | Lex De Haan | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 4 | Alexander Hunold | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | +------------+------------------+------+------+------+------+------+-------+-----------+-------+ 4 rows in set (0.00 sec) Now the exam is over and we have received all subject marks, now we will update the table, total marks of all subject, the percentage of total marks and grade will be automatically calculated. For this sample calculation, the following conditions are assumed : Total Marks (will be stored in TOTAL column) : TOTAL = SUB1 + SUB2 + SUB3 + SUB4 + SUB5 Percentage of Marks (will be stored in PER_MARKS column) : PER_MARKS = (TOTAL)/5 Grade (will be stored GRADE column) : Here is the code : mysql> UPDATE STUDENT_MARKS SET SUB1 = 54, SUB2 = 69, SUB3 = 89, SUB4 = 87, SUB5 = 59 WHERE STUDENT_ID = 1; Query OK, 1 row affected (0.05 sec) Rows matched: 1 Changed: 1 Warnings: 0 Let update the marks of a student : USE `test`; DELIMITER $$ CREATE TRIGGER `student_marks_BUPD` BEFORE UPDATE ON student_marks FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one BEGIN SET NEW.TOTAL = NEW.SUB1 + NEW.SUB2 + NEW.SUB3 + NEW.SUB4 + NEW.SUB5; SET NEW.PER_MARKS = NEW.TOTAL/5; IF NEW.PER_MARKS >=90 THEN SET NEW.GRADE = 'EXCELLENT'; ELSEIF NEW.PER_MARKS>=75 AND NEW.PER_MARKS<90 THEN SET NEW.GRADE = 'VERY GOOD'; ELSEIF NEW.PER_MARKS>=60 AND NEW.PER_MARKS<75 THEN SET NEW.GRADE = 'GOOD'; ELSEIF NEW.PER_MARKS>=40 AND NEW.PER_MARKS<60 THEN SET NEW.GRADE = 'AVERAGE'; ELSESET NEW.GRADE = 'NOT PROMOTED'; END IF; END; $$ Now check the STUDENT_MARKS table with updated data. The trigger show you the updated records in 'stu_log'. mysql> SELECT * FROM STUDENT_MARKS; +------------+------------------+------+------+------+------+------+-------+-----------+-------+ | STUDENT_ID | NAME | SUB1 | SUB2 | SUB3 | SUB4 | SUB5 | TOTAL | PER_MARKS | GRADE | +------------+------------------+------+------+------+------+------+-------+-----------+-------+ | 1 | Steven King | 54 | 69 | 89 | 87 | 59 | 358 | 71.60 | GOOD | | 2 | Neena Kochhar | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 3 | Lex De Haan | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | | 4 | Alexander Hunold | 0 | 0 | 0 | 0 | 0 | 0 | 0.00 | | +------------+------------------+------+------+------+------+------+-------+-----------+-------+ 4 rows in set (0.00 sec) MySQL Trigger : Example AFTER DELETE In our 'AFTER UPDATE' example, we had two tables student_mast and stu_log. student_mast have three columns STUDENT_ID, NAME, ST_CLASS and stu_log table has two columns user_id and description. We want to store some information in stu_log table after a delete operation happened on student_mast table. Here is the trigger : USE `test`; DELIMITER $$ CREATE TRIGGER `student_mast_ADEL` AFTER DELETE ON student_mast FOR EACH ROW -- Edit trigger body code below this line. Do not edit lines above this one BEGIN INSERT into stu_log VALUES (user(), CONCAT('Update Student Record ', OLD.NAME,' Clas :',OLD.ST_CLASS, '-> Deleted on ', NOW())); END; $$ Let delete a student from STUDENT_MAST. mysql> DELETE FROM STUDENT_MAST WHERE STUDENT_ID = 1; Query OK, 1 row affected (0.06 sec) Here is the latest position of STUDENT_MAST, STU_LOG tables : mysql> SELECT * FROM STUDENT_MAST; +------------+------------------+----------+ | STUDENT_ID | NAME | ST_CLASS | +------------+------------------+----------+ | 2 | Neena Kochhar | 9 | | 3 | Lex De Haan | 9 | | 4 | Alexander Hunold | 11 | +------------+------------------+----------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM STU_LOG; +----------------+-----------------------------------------------------------------------------+ | user_id | description | +----------------+-----------------------------------------------------------------------------+ | [email protected] | Update Student RecordSteven King Previous Class :7 Present Class 8 | | [email protected] | Update Student RecordNeena Kochhar Previous Class :8 Present Class 9 | | [email protected] | Update Student RecordLex De Haan Previous Class :8 Present Class 9 | | [email protected] | Update Student RecordAlexander Hunold Previous Class :10 Present Class 11 | | [email protected] | Update Student Record Steven King Clas :8-> Deleted on 2013-07-16 15:35:30 | +----------------+-----------------------------------------------------------------------------+ 5 rows in set (0.00 sec) How MySQL handle errors during trigger execution?
Delete a MySQL trigger To delete or destroy a trigger, use a DROP TRIGGER statement. You must specify the schema name if the trigger is not in the default (current) schema : DROP TRIGGER [IF EXISTS] [schema_name.]trigger_nam if you drop a table, any triggers for the table are also dropped. Reference: MySQL 5.6 Manual Previous:
MySQL Procedure Apa fungsi dari trigger MySQL?TRIGGER adalah kumpulan kode SQL yang berjalan secara otomatis untuk mengeksekusi perintah INSERT, UPDATE, DELETE.
Apa fungsi dari trigger?Trigger dapat didefinisikan untuk menjalankan penrintah sebelum atau setelah eksekusi DML (Data Manipulation Language) seperti INSERT, UPDATE, dan DELETE. Trigger banyak digunakan untuk menjaga integritas informasi pada database.
Apa yang dimaksud dengan trigger dalam database?Trigger dapat didefinisikan sebagai himpunan kode (prosedural) yang dieksekusi secara otomatis sebagai respon atas suatu kejadian berkaitan dengan tabel basis data. Kejadian (event) yang dapat membangkitkan trigger umumnya berupa pernyataan INSERT, UPDATE, dan DELETE.
Apa itu trigger dalam pemrograman?Trigger adalah prosedur tersimpan pada Microsoft SQL Server yang secara otomatis dijalankan apabila data di dalam tabel berubah karena eksekusi perintah SQL (INSERT, UPDATE atau DELETE). Mengupdate tabel-tabel lain jika ada perubahan (Insert, update atau delete) pada tabel yang sedang aktif.
|