Generally, a Trigger is defined as a response to an event. In MySQL, a trigger is a special stored procedure that resides in the system catalogue, that is executed automatically (without being called explicitly like regular stored procedures) whenever an event is performed. These events include statements like INSERT, UPDATE and DELETE etc.

To run a MySQL trigger, the user must have admin/superuser privileges.

As per the SQL standard, triggers are usually divided into two categories −

• Row-level Trigger: Triggers that are only executed when each row is either inserted, updated or deleted in a database table. MySQL only supports these type of triggers.

• Statement-level Trigger: Triggers like these are executed on the transaction level, once, no matter how many rows are modified in a table. MySQL does not support these trype of triggers.

Types of Triggers in MySQL

There are six types of row-level triggers in MySQL. They are:

• Before Insert Trigger

• After Insert Trigger

• Before Update Trigger

• After Update Trigger

• Before Delete Trigger

• After Delete Trigger

Before Insert Trigger

The Before Insert Trigger is performed before any value is inserted into the table. Whenever an INSERT statement is executed, the Before Insert trigger goes off, followed by the insertion transaction.

After Insert Trigger

The After Insert Trigger works opposite to the Before Insert Trigger. As implied by its name, it is performed after any value is inserted into the table. Whenever an INSERT statement is executed, the value is inserted into the table first followed by the execution of the trigger.

Before Update Trigger

The Before Update Trigger is performed before any value is updated or modified in the table. Whenever an UPDATE statement is executed, the Before Update trigger goes off, followed by the update transaction.

After Update Trigger

The After Update Trigger works opposite to the Before Update Trigger. As implied by its name, it is performed after any value is updated in the table. Whenever an UPDATE statement is executed, the value is updated in the table first followed by the execution of the trigger.

Before Delete Trigger

The Before Delete Trigger is performed before any value is deleted from the table. Whenever a DELETE statement is executed, the Before Delete trigger goes off, followed by the deletion transaction.

After Delete Trigger

The After Delete Trigger works opposite to the Before Delete Trigger. As implied by its name, it is performed after any value is deleted from the table. Whenever an DELETE statement is executed, the value is deleted from the table first followed by the execution of the trigger.

Advantages of Triggers

Triggers hold a lot of advantages in MySQL database. They are listed as follows −

• Triggers help the database to maintain the integrity of the data stored.

• Triggers are also a means to handle errors from the database layer itself.

• As triggers are invoked automatically without being called explicitly, you don”t have to wait for the scheduled events to run.

• Triggers can be useful to track the data changes made in the tables, by logging the events.

• MySQL Triggers can also prevent invalid transactions from being executed.

Disadvantages of Triggers

However, there are disadvantages of using triggers in a MySQL database. Some of them are listed as follows −

• Triggers cannot replace all validations, and only provide extended validations. For simple validations, you can use the NOT NULL, UNIQUE, CHECK and FOREIGN KEY constraints.

• As triggers are invisible to the client application, it is impossible to understand what goes on in the database layer. Hence, making it difficult to troubleshoot.

• Triggers are not beneficial for use with high-velocity data i.e. the data when a number of events per second are high.

• Triggers may increase the overhead of the MySQL Server.

Restrictions on Triggers

Following are some of the restrictions that apply to MySQL triggers −

• One trigger for each event − Each table can have only one trigger for each event combination, i.e. you can”t define two same triggers for the same table.

• RETURN statement is not permitted − As triggers don”t return any values, the RETURN statement is not permitted.

• Foreign key restriction − Triggers are not activated by foreign key actions.

• Outdated metadata − Suppose, if a trigger is loaded into cache, it is not automatically reloaded when the table metadata changes. In this case, a trigger can operate using outdated metadata.

• Cannot use ”CALL” statement − We cannot use the CALL statement in triggers.

• Cannot create a TEMPORARY table or a view − We cannot create a view for a temporary table or a view.

• Not activated by changes in INFORMATION_SCHEMA − Actually, triggers are not activated by changes made in INFORMATION_SCHEMA or performance_schema tables. It is because these tables are views and triggers are not permitted on views.

The MySQL INT Data Type

The MySQL INT data type is used to store whole numbers without the decimal places (integers).

However, MySQL provides various integer data types, such as TINYINT, SMALLINT, MEDIUMINT, and BIGINT to cater to different ranges of whole numbers.

The following table illustrates the characteristics of different integer types in MySQL, including storage in bytes, minimum value, and maximum value for both signed and unsigned options −

We have to choose the data types based on the kind (type) of data being stored. If possible, we need to use smaller data types to minimize the database size. TINYINT can be used for small numbers, while INT or BIGINT is used for large numbers like phone numbers in a country.

Auto Increment with MySQL INT

In MySQL, you can use the AUTO_INCREMENT attribute with an INT column to automatically generate unique values for that column. Here”s how it works −

• Initial Value − When you create a table with an AUTO_INCREMENT INT column, the sequence starts with 1.

• Inserting NULL or 0 − When you insert a record with a NULL or 0 value for the AUTO_INCREMENT column, MySQL sets the value to the next sequence value. This means it assigns the next available integer starting from 1.

• Inserting Non-NULL Values − If you insert a non-NULL value into the AUTO_INCREMENT column, MySQL accepts that value and continues the sequence based on the new value inserted.

Example

First of all, we are creating a table named STUDENTS with an AUTO_INCREMENT INT column named “ID” −

CREATE TABLE STUDENTS (
   ID int auto_increment,
   NAME varchar(20),
   primary key (ID)
);

When we insert records into this table without specifying values for the “ID” column, MySQL automatically generates unique values for “ID” starting from 1.

Here, we are inserting three rows into the STUDENTS table using the below INSERT query −

INSERT INTO STUDENTS (NAME) VALUES
(''Tilak''), (''Akash''), (''Surya''), (''Deepak'');

The STUDENTS table created is as follows −

Now, let us insert a row where we provide an explicit value for the “ID” column −

INSERT INTO STUDENTS (ID, NAME) VALUES (15, ''Arjun'');

Following is the output obtained −

Query OK, 1 row affected (0.01 sec)

Since we specified the “ID” as 15, MySQL resets the sequence to 16. If we insert a new row without specifying the “ID,” MySQL will use 16 as the next AUTO_INCREMENT value −

INSERT INTO STUDENTS (NAME) VALUES (''Dev'');

The output obtained is as follows −

Query OK, 1 row affected (0.01 sec)

Now, let us retrieve the records from the “STUDENTS” table −

SELECT * FROM STUDENTS;

The table produced is −

From MySQL 5.1 version and onwards, the AUTO_INCREMENT column accepts only positive values and does not allow negative values.

MySQL INT UNSIGNED

In MySQL, when you define an UNSIGNED INT on a column, that column is restricted to storing only non-negative values (i.e., positive values). Negative values are not allowed in such columns.

Example

Let us create a table with the name EMPLOYEES using the following query −

CREATE TABLE EMPLOYEES (
   ID int auto_increment,
   NAME varchar(30) not null,
   AGE int UNSIGNED,
   Primary key(ID)
);

Following is the output obtained −

Query OK, 0 rows affected (0.04 sec)

Now, let us insert a row with a non-negative value into the “AGE” column −

INSERT INTO EMPLOYEES (NAME, AGE) VALUES (''Varun'', 32);

The above query will execute successfully since the value provided for the “AGE” column is non-negative.

Query OK, 1 row affected (0.01 sec)

However, if we attempt to insert a negative value into the “AGE” column, MySQL will generate an error −

INSERT INTO EMPLOYEES (NAME, AGE) VALUES (''Sunil'', -10);

MySQL will issue an error as shown below −

ERROR 1264 (22003): Out of range value for column ''AGE'' at row 1

MySQL INT with Display Width Attribute

In MySQL, you can specify a display width for the INT data type by using parentheses after the INT keyword. For instance, using INT(5) sets the display width to five digits.

It”s important to note that the display width attribute for INT in MySQL doesn”t affect the range of values that can be stored in the column. It formats integer values in applications, and is included as metadata in the result set.

For example, if you insert the value 12345 into the id column of the EMPLOYEES table, it will be stored as is. When you retrieve it, some applications may choose to pad it with leading zeros to ensure it is displayed as five digits (e.g., 012345).

MySQL INT with ZEROFILL Attribute

In MySQL, the ZEROFILL attribute is a non-standard attribute that can be applied to numeric data types. It adds leading zeros to the displayed values, making sure the number is displayed with a fixed width, especially useful for numerical codes.

Example

Let us create a table with the name ZEROFILL_TABLE with ZEROFILL applied to INT columns using the query below −

CREATE TABLE ZEROFILL_TABLE (
   col1 int(4) ZEROFILL,
   col2 int(6) ZEROFILL,
   col3 int(8) ZEROFILL
);

The output obtained is as follows −

Query OK, 0 rows affected, 6 warnings (0.02 sec)

Now, we are inserting a new row into the above-created table −

INSERT INTO ZEROFILL_TABLE (col1, col2, col3)
VALUES (1, 7, 3);

Following is the output of the above code −

Query OK, 1 row affected (0.00 sec)

Now, let us display the records from the ZEROFILL_TABLE table −

SELECT * FROM ZEROFILL_TABLE;

We can see in the output below, the values are displayed with the specified width, and leading zeros are added to maintain that width, as determined by the ZEROFILL attribute −

INT Datatype Using a Client Program

We can also create column of the INT datatype using the client program.

Syntax

$sql = ''CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )
$mysqli->query($sql);

sql = "CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )";
con.query(sql);

String sql = "CREATE TABLE students(Id INT AUTO_INCREMENT primary key not null, reg_No INT)";
statement.execute(sql);

sql = ''CREATE TABLE students (ID int auto_increment, NAME varchar(20), primary key (ID) )''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
//create table with boolean column
$sql = ''CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )
$result = $mysqli->query($sql);
if ($result) {
   printf("Table created successfully...!n");
}
//insert data into created table
$q = "INSERT INTO students (reg_No) VALUES (101), (102)";
if ($res = $mysqli->query($q)) {
   printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT Id, reg_No FROM students";
if ($r = $mysqli->query($s)) {
   printf("Table Records: n");
   while ($row = $r->fetch_assoc()) {
      printf(" ID: %d, Reg: %s", $row["Id"], $row["reg_No"]);
      printf("n");
   }
} else {
   printf(''Failed'');
}
$mysqli->close();

Table Records:
ID: 1, Reg: 101
ID: 2, Reg: 102

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);

   //create a students table, that accepts one column of int type.
   sql = "CREATE TABLE students (Id INT AUTO_INCREMENT primary key not null, reg_No INT )";
   con.query(sql);

   //insert data into created table
   sql = "INSERT INTO students (reg_No) VALUES (101), (102)";
   con.query(sql);

   //select datatypes of salary
   sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''students'' AND COLUMN_NAME = ''reg_No''`;
   con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
   });
});

[ { DATA_TYPE: ''int'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Int {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Int data types...!;
         String sql = "CREATE TABLE students(Id INT AUTO_INCREMENT primary key not null, reg_No INT)";
         statement.execute(sql);
         System.out.println("column of a INT type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE students");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
column of a INT type created successfully...!
Id int
reg_No int

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
   host=''localhost'',
   user=''root'',
   password=''password'',
   database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with int column
sql = ''''''CREATE TABLE students (
ID int auto_increment,
NAME varchar(20),
primary key (ID)
)''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (1, ''Tilak''),
    (2, ''Akash''),
    (3, ''Surya''),
    (4, ''Deepak''),
    (15, ''Arjun''),
    (16, ''Dev'')
]
# Insert data into the created table
insert_query = "INSERT INTO STUDENTS (ID, NAME) VALUES (%s, %s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM Students"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Table Data:
(1, ''Tilak'')
(2, ''Akash'')
(3, ''Surya'')
(4, ''Deepak'')
(15, ''Arjun'')
(16, ''Dev'')

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

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

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

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

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();

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);
 });
});

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();
      }
   }
}

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()

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'')

The MySQL TINYINT Data Type

The MySQL TINYINT data type is used to store integer values within a very small range. It occupies just 1 byte (8 bits) of storage and can hold values from -128 to 127 for signed TINYINT or 0 to 255 for unsigned TINYINT.

When you define a TINYINT column in MySQL, by default it is considered as SIGNED. This means it can hold both positive and negative numbers within a specific range. Additionally, you can use either “TINYINT” or “INT1” to define such a column because they work the same way.

Syntax

Following is the syntax of the MySQL TINYINT data type −

TINYINT(M) [SIGNED | UNSIGNED | ZEROFILL]

Example

First, let us create a table with the name tinyint_table using the below query −

CREATE TABLE tinyint_table (
   col1 TINYINT,
   col2 TINYINT UNSIGNED,
   col3 TINYINT ZEROFILL
);

Following is the output obtained −

Query OK, 0 rows affected, 1 warning (0.03 sec)

Now, let us try to insert some values (128, 128, 128) into these columns as shown below −

INSERT INTO tinyint_table VALUES (128, 128, 128);

An error is generated for the value in col1 because the value we inserted is out of range −

ERROR 1264 (22003): Out of range value for column ''col1'' at row 1

Next, if we try to insert a negative value into the TINYINT UNSIGNED column (“col2”), it will result in an error because UNSIGNED values cannot be negative −

INSERT INTO tinyint_table VALUES (127, -120, 128);

The error message displayed is as follows −

ERROR 1264 (22003): Out of range value for column ''col2'' at row 1

Similarly, if we insert -128 into the TINYINT ZEROFILL column (“col3”), an error will be generated −

INSERT INTO tinyint_table VALUES (127, 128, -128);

The output is as shown below −

ERROR 1264 (22003): Out of range value for column ''col3'' at row 1

However, if we insert values within the valid range, the insertion will succeed as shown below −

INSERT INTO tinyint_table VALUES (127, 128, 128);

Following is the output of the above code −

Query OK, 1 row affected (0.01 sec)

Finally, we can retrieve all the records present in the table using the following SELECT query −

SELECT * FROM tinyint_table;

This query will display the following result −

TINYINT Datatype Using a Client Program

We can also create column of the TINYINT datatype using the client program.

Syntax

$sql = ''CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )
$mysqli->query($sql);

sql = "CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )";
con.query(sql);

String sql = "CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )";
statement.execute(sql);

sql = ''CREATE TABLE tinyint_table (col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL)''
cursorObj.execute(sql)

Example

Following are the programs −

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

$sql = ''CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
// insert data into created table
$q = " INSERT INTO tinyint_table (col1, col2, col3) VALUES (100, 105, 110)";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM tinyint_table";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" Col_1: %s, Col_2: %s, Col_3: %s", $row["col1"], $row["col2"], $row["col3"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
 Col_1: 100, Col_2: 105, Col_3: 110

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);

  //create a tinyint_table table, that accepts one column of tinyint type.
  sql = "CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )";
  con.query(sql);

  //insert data into created table
  sql =
    "INSERT INTO tinyint_table (col1, col2, col3) VALUES (100, 105, 110)";
  con.query(sql);

  //select datatypes of salary
  sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''tinyint_table'' AND COLUMN_NAME = ''col2''`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

[ { DATA_TYPE: ''tinyint'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class TinyInt {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //TinyInt data types...!;
         String sql = "CREATE TABLE tinyint_table ( col1 TINYINT, col2 TINYINT UNSIGNED, col3 TINYINT ZEROFILL )";
         statement.execute(sql);
         System.out.println("column of a TINYINT type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE tinyint_table");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
column of a TINYINT type created successfully...!
col1 tinyint
col2 tinyint unsigned
col3 tinyint(3) unsigned zerofill

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# Create table with Tinyint column
sql = ''''''
CREATE TABLE tinyint_table (
col1 TINYINT,
col2 TINYINT UNSIGNED,
col3 TINYINT ZEROFILL
)''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Insert data into the created table
insert_query = "INSERT INTO tinyint_table (col1, col2, col3) VALUES (127, 128, 128);"
cursorObj.execute(insert_query)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM tinyint_table"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Table Data:
(127, 128, 128)

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.

Triggers are, in fact, written to be executed in response to any of the following events −

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

You can delete a trigger using the DROP TRIGGER Statement.

Dropping Trigger in MySQL

The DROP TRIGGER statement in MySQL will drop a trigger from a database, and all its information.

Syntax

Following is the syntax of the MySQL DELETE TRIGGER Statement.

DROP TRIGGER [IF EXISTS] trigger_name

Where, trigger_name is the name of the trigger you need to delete.

Example

Assume we have created a table with name student as shown below −

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

Following query creates a trigger named sample_trigger on this table. This trigger will set the score value 0 if you enter a value that is less than 0 as score.

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 ;

Now, let us use the following query to drop the trigger we created in the previous step −

DROP TRIGGER sample_trigger;

Verification

To verify if the trigger has been dropped, let us display the trigger information using the following query −

SHOW TRIGGERSG

Since have deleted the trigger created, we get an empty set −

Empty set (0.11 sec)

With IF EXISTS clause

If you try to drop a trigger that doesn”t exist an error will be generated as shown below −

DROP TRIGGER demo;

Following is the output −

ERROR 1360 (HY000): Trigger does not exist

If you use the IF EXISTS clause along with the DROP TRIGEGR statement as shown below, the specified trigger will be dropped and if a trigger with the given name, doesn”t exist the query will be ignored.

DROP TRIGGER IF EXISTS demo;

Dropping Trigger Using a Client Program

In addition to create or show a trigger, we can also drop a trigger using a client program.

Syntax

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

sql = "DROP TRIGGER testTrigger";
con.query(sql);

String sql = "DROP TRIGGER sample_trigger";
statement.execute(sql);

drop_trigger_query = "DROP TRIGGER sample_trigger"
cursorObj.execute(drop_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.
'');
$sql = "DROP TRIGGER testTrigger";
if($mysqli->query($sql)){
   printf("Trigger dropped successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Trigger dropped successfully...!

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 = "DROP TRIGGER testTrigger";
 con.query(sql);
 console.log("Drop trigger query executed successfully..!");
 console.log("Triggers: ");
 sql = "SHOW TRIGGERS";
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});

Drop trigger query executed successfully..!
Triggers:
[]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class DropTrigger {
   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 = "DROP TRIGGER sample_trigger";
            st.execute(sql);
            System.out.print("Triggerd dropped successfully...!");
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

   Triggerd dropped successfully...!

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()
# drop trigger
drop_trigger_query = "DROP TRIGGER sample_trigger"
cursorObj.execute(drop_trigger_query)
print("Trigger is dropped successfully")
connection.commit()
# close the cursor and connection
cursorObj.close()
connection.close()

Trigger is dropped successfully

A Trigger is simply defined as a response to an event. In MySQL, a trigger is a special stored procedure that resides in the system catalogue, that is executed whenever an event is performed. These events include SQL statements like INSERT, UPDATE and DELETE etc.

It is called a special stored procedure as it does not require to be invoked explicitly like other stored procedures. The trigger acts automatically whenever the desired event is fired.

MySQL After Insert Trigger

The After Insert Trigger is a row-level trigger supported by the MySQL database. As its name suggests, the After Insert Trigger is executed right after a value is inserted into a database table.

A row-level trigger is a type of trigger that goes off every time a row is modified. Simply, for every single transaction made in a table (like insertion, deletion, update), one trigger acts automatically.

To elaborate, whenever an INSERT statement is executed in the database, the value is inserted into the table first followed by the trigger execution. Hence, one cannot update a newly inserted row using the AFTER INSERT trigger.

Syntax

Following is the syntax to create the AFTER INSERT trigger in MySQL −

CREATE TRIGGER trigger_name
AFTER INSERT ON table_name FOR EACH ROW
BEGIN
   -- trigger body
END;

Example

Let us see an example demonstrating the AFTER INSERT trigger. In here, we are creating a new table named USERS, which contains the details of users of an application, using the following query −

CREATE TABLE USERS(
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(100) NOT NULL,
   AGE INT NOT NULL,
   BIRTH_DATE VARCHAR(100),
   PRIMARY KEY(ID)
);

Now, let us create another table ”PUSH_NOTIFICATIONS” that is used to store messages to send as push notifications to the users on their birthdays −

CREATE TABLE PUSH_NOTIFICATIONS(
   ID INT AUTO_INCREMENT,
   BIRTH_DATE VARCHAR(100),
   NOTIFICATIONS VARCHAR(255) NOT NULL,
   PRIMARY KEY(ID)
);

Using the following CREATE TRIGGER statement, create a new trigger after_trigger on the USERS table to insert values into the PUSH_NOTIFICATIONS table −

DELIMITER //
CREATE TRIGGER after_trigger AFTER INSERT ON USERS FOR EACH ROW
BEGIN
   IF NEW.BIRTH_DATE IS NOT NULL THEN
   INSERT INTO PUSH_NOTIFICATIONS VALUES
   (new.ID, new.BIRTH_DATE, CONCAT(''Happy Birthday, '', NEW.NAME, ''!''));
END IF;
END //
DELIMITER ;

Insert values into the USERS table using the regular INSERT statement as shown below −

INSERT INTO USERS (NAME, AGE, BIRTH_DATE) VALUES
(''Sasha'', 23, ''24/06/1999'');
(''Alex'', 21, ''12/01/2001'');

Verification

To verify if the trigger has been executed, display the PUSH_NOTIFICATIONS table using the SELECT statement −