mysql

Khóa học miễn phí MySQL – Show Trigger nhận dự án làm có lương

Posted on by user

MySQL – SHOW TRIGGERS

Table of content

Triggers in MySQL are stored programs similar to procedures. These can be created on a table, schema, view and database that are associated with an event and whenever an event occurs the respective trigger is invoked.

MySQL provides a statement to list out all the existing triggers present in a database. Knowing the trigger information can be useful while creating new triggers, so that a user wouldn”t use the same name for multiple triggers.

Show Triggers in MySQL

The SHOW TRIGGERS Statement is used in MySQL to display information about all the triggers defined in the current database.

Syntax

Following is the syntax of the MySQL SHOW TRIGGERS Statement −

SHOW TRIGGERS
[{FROM | IN} db_name]
[LIKE ''pattern'' | WHERE expr]

Example

In this example, we are creating a table named STUDENT using the query below −

CREATE TABLE STUDENT(
   Name varchar(35), 
   Age INT, 
   Score INT
);

Following query creates a trigger sample_trigger which will set the score value to 0 if you enter a negative score value into the table.

DELIMITER //
CREATE TRIGGER sample_trigger 
BEFORE INSERT ON STUDENT 
FOR EACH ROW
BEGIN
 IF NEW.score < 0 THEN SET NEW.score = 0;
END IF;
END //
DELIMITER ;

Assume we have created another trigger using the AFTER clause −

DELIMITER //
CREATE TRIGGER testTrigger
AFTER UPDATE ON Student
FOR EACH ROW
BEGIN
 INSERT INTO Student
 SET action = ''update'',
 Name = OLD.Name,
 Age = OLD.age,
 Score = OLD.score;
END;
END //

DELIMITER ;

Following query shows the existing triggers in the current database −

SHOW TRIGGERS G;

Output

The list of triggers will be displayed as follows −

*************************** 1. row ***************************
Trigger: sample_trigger
Event: INSERT
Table: student
Statement: BEGIN
           IF NEW.score < 0 THEN SET NEW.score = 0;
           END IF;
           END
Timing: BEFORE
Created: 2021-05-12 19:08:04.50
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
Trigger: testTrigger
Event: UPDATE
Table: student
Statement: INSERT INTO Student
           SET Name = OLD.Name,
               Age = OLD.age,
               Score = OLD.score
Timing: AFTER
Created: 2021-05-12 19:10:44.49
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.00 sec)

With FROM or IN Clause

You can retrieve the information of triggers from a specific database using the FROM clause.

Example

Assume that the current database is named demo. Following query shows the triggers present in the database demo −

SHOW TRIGGERS FROM demoG

You can also use the IN clause instead of FROM, to get the same output.

SHOW TRIGGERS IN demoG

Output

The existing triggers present in the demo database −

*************************** 1. row ***************************
             Trigger: sample_trigger
               Event: INSERT
               Table: student
           Statement: BEGIN
 IF NEW.score < 0 THEN SET NEW.score = 0;
END IF;
END
              Timing: BEFORE
             Created: 2023-09-29 11:42:33.58
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
             Trigger: testTrigger
               Event: UPDATE
               Table: student
           Statement: BEGIN
 INSERT INTO Student
 SET action = ''update'',
 Name = OLD.Name,
 Age = OLD.age,
 Score = OLD.score;
END
              Timing: AFTER
             Created: 2023-09-29 11:43:10.27
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.00 sec)

With WHERE Clause

You can use the WHERE clause of the SHOW TRIGGERS statements to retrieve info about the triggers which match the specified condition.

Example

Following query retrieves the triggers in the current database whose event is update −

SHOW TRIGGERS FROM demo WHERE Event = ''UPDATE'' G;

Output

The required list of triggers is displayed as follows −

*************************** 1. row ***************************
             Trigger: testTrigger
               Event: UPDATE
               Table: student
           Statement: BEGIN
 INSERT INTO Student
 SET action = ''update'',
 Name = OLD.Name,
 Age = OLD.age,
 Score = OLD.score;
END
              Timing: AFTER
             Created: 2023-09-29 11:43:10.27
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: cp850
collation_connection: cp850_general_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Showing Trigger Using Client Program

We can also Show a trigger using a client program.

Syntax

To show a trigger through a PHP program, we need to execute the SHOW TRIGGERS statement using the mysqli function query() as follows −

$sql = "Show TRIGGER";
$mysqli->query($sql);

To show a trigger through a JavaScript program, we need to execute the SHOW TRIGGERS statement using the query() function of mysql2 library as follows −

sql = "Show TRIGGER";
con.query(sql);

To show a trigger through a Java program, we need to execute the SHOW TRIGGERS statement using the JDBC function executeQuery() as follows −

String sql = "Show TRIGGER";
statement.executeQuery(sql);

To show a trigger through a python program, we need to execute the SHOW TRIGGERS statement using the execute() function of the MySQL Connector/Python as follows −

Show_trigger_query = ''SHOW TRIGGER''
cursorObj.execute(Show_trigger_query)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$db = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
printf(''Connected successfully.
'');

// Create a trigger
$sql = "CREATE TRIGGER testTrigger AFTER UPDATE ON Student FOR EACH ROW INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score";

if ($mysqli->query($sql)) {
    printf("Trigger created successfully...!
");
} else {
    printf("Trigger creation failed: %s
", $mysqli->error);
}

// Show created trigger details
$sql = "SHOW TRIGGERS"; 
$res = $mysqli->query($sql);
if ($res) {
    while ($row = $res->fetch_assoc()) {
        // Print trigger details
        foreach ($row as $key => $value) {
            printf("%s: %s
", $key, $value);
        }
        printf("
");
    }
    $res->free();
} else {
    printf("Failed to retrieve triggers: %s
", $mysqli->error);
}
$mysqli->close();

Output

The output obtained is as follows −

Connected successfully.
Trigger created successfully...!
Trigger: testTrigger
Event: UPDATE
Table: student
Statement: INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score
Timing: AFTER
Created: 2023-09-08 12:16:27.54
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci

var mysql = require(''mysql2'');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});

 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SHOW TRIGGERS";
 con.query(sql);
 console.log("show trigger query executed successfully..!");
 console.log("Triggers: ");
 sql = "SHOW TRIGGERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Output

The output produced is as follows −

show trigger query executed successfully..!
Triggers:
[
  {
    Trigger: ''testTrigger'',
    Event: ''UPDATE'',
    Table: ''student'',
    Statement: "INSERT INTO Student SET action = ''update'', Name = OLD.Name, age = OLD.age, score = OLD.score",
    Timing: ''AFTER'',
    Created: 2023-08-01T05:21:18.540Z,
    sql_mode: ''IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'',
    Definer: ''root@localhost'',
    character_set_client: ''utf8mb4'',
    collation_connection: ''utf8mb4_unicode_ci'',
    ''Database Collation'': ''utf8mb4_0900_ai_ci''
  }
] 

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ShowTrigger {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String user = "root";
      String password = "password";
      ResultSet rs;
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "Create Trigger sample_trigger BEFORE INSERT ON student FOR EACH ROW BEGIN IF NEW.score < 0 THEN SET NEW.score = 0; END IF; END";
            st.execute(sql);
            System.out.println("Trigger created successfully...!");
            String sql1 = "SHOW TRIGGERS";
            rs = st.executeQuery(sql1);
            System.out.println("Triggers: ");
            while(rs.next())
            {
               String triggers = rs.getNString(1);
               System.out.println(triggers);
            }
            
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Output

The output obtained is as shown below −

Trigger created successfully...!
Triggers: 
sample_trigger
testTrigger   

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
table_name = ''Student''
trigger_name = ''sample_trigger''
# Creating a cursor object
cursorObj = connection.cursor()
# show trigger
show_triggers_query = "SHOW TRIGGERS"
cursorObj.execute(show_triggers_query)
result = cursorObj.fetchall()
print("Triggers in the database:")
for row in result:
    print(row)
# close the cursor and connection
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

Triggers in the database:
(''sample_trigger'', ''INSERT'', ''student'', b''BEGINn    IF NEW.Score < 0 THENn        SET NEW.Score = 0;n    END IF;nEND'', ''BEFORE'', datetime.datetime(2023, 7, 31, 11, 38, 5, 880000), {''STRICT_TRANS_TABLES'', ''NO_ENGINE_SUBSTITUTION''}, ''root@localhost'', ''utf8mb4'', ''utf8mb4_0900_ai_ci'', ''utf8mb4_0900_ai_ci'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user