Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-show-trigger/ below:

MySQL Show Trigger - GeeksforGeeks

SHOW TRIGGERS is the MySQL way of showing all the registered database triggers for a given database. Triggers are special kinds of rules that perform predefined actions on their own in response to some event.

SHOW TRIGGERS makes public the triggers set up with the events to which they respond and their exact configuration. In this article, We will learn about the MySQL Show Trigger in detail.

• The MySQL SHOW TRIGGERS command allows the user to obtain information about all the triggers in a database.
  
• Triggers represent automatic actions executed as a response to certain changes in the database, like insertions, updates, or deletions.
  
• The SHOW TRIGGERS command may be used to obtain information about such automated procedures, including name, table associated, trigger, or event—like before/after an update—and the SQL statements they execute.
  
• This command is essential to both manage and debug triggers for correct execution and efficiency in a working database environment.

The general syntax of the SHOW TRIGGERS command in MySQL is as follows:

SHOW TRIGGERS [FROM database_name] [LIKE 'pattern'];

where,

• FROM Database_name: This indicates which database to show the triggers from. It defaults to the current database when not indicated.
• LIKE 'pattern' - Filters on the given pattern. Use this to find triggers by name or naming convention.

1. BEFORE INSERT: This triggers a set of actions to be performed before inserting a new record into any table.
2. AFTER INSERT: It Perform a set of activities after a new record has been inserted into the table.
3. BEFORE UPDATE: It Perform a set of activities before an update of an existing record.
4. AFTER UPDATE: After the update of an existing record, execute a set of activities.
5. BEFORE DELETE: This will execute the set of actions before an existing record is deleted from the table.
6. AFTER DELECTE: This executes the set of actions after a record has been deleted from a table.
7. INSTEAD OF INSERT: This replaces the default action with the insert operation for a set of actions.
8. INSTEAD OF UPDATE: This replaces the default action with the update operation by a set of actions.
9. INSTEAD OF DELETE: This changes the standard delete action for its custom set of actions instead of delete.

In MySQL, should you issue a simple SHOW TRIGGERS statement to list all triggers in a database, this result set will return with various columns to provide an overview of information for each trigger.

Here is what the columns in the result set contain:

• Trigger: The name of the trigger.
• Event: The event that causes the trigger to activate (INSERT, UPDATE, or DELETE).
• Table: The name of the table to which the trigger is associated.
• Statement: The SQL statement that is executed when the trigger activates.
• Timing: It Indicates whether the trigger is activated BEFORE or AFTER the event.
• Created: The timestamp when the trigger was created.
• sql_mode: The SQL mode in effect when the trigger was created.
• Definer : The user who has created the trigger
• character_set_client: The Client's character set that has created the trigger
• collation_connection: Collation of the connection which has created the trigger.
• Database Collation: Collation of database it is in.

To see the triggers for a given table, you just need to filter the output of SHOW TRIGGERS on the Table column. The following example lists the triggers for the table mytabl

SHOW TRIGGERS WHERE `Table` = 'mytable';

your_user@localhost

Trigger

Event

Table

Statement

Timing

Created

sql_mode

Definer

your_trigger

INSERT

your_table_name

BEGIN ... END

BEFORE

NULL

your_user@localhost

To filter triggers by the event (INSERT, UPDATE, DELETE), you can query the information_schema.TRIGGERS table.

SELECT * FROM information_schema.TRIGGERS WHERE EVENT_MANIPULATION = 'INSERT';

Output:

TRIGGER_NAME

EVENT_MANIPULATION

EVENT_OBJECT_TABLE

ACTION_TIMING

ACTION_STATEMENT

TRIGGER_SCHEMA

To filter triggers by their timing (e.g., BEFORE or AFTER), you can query the INFORMATION_SCHEMA.TRIGGERS table:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE ACTION_TIMING = 'BEFORE';

Output:

TRIGGER_NAME

EVENT_MANIPULATION

EVENT_OBJECT_TABLE

ACTION_STATEMENT

ACTION_TIMING

TRIGGER_SCHEMA

trigger1

INSERT

your_table

BEGIN ... END

BEFORE

your_schema

trigger3

DELETE

another_table

BEGIN ... END

BEFORE

another_schema

To filter by the specific SQL statement within a trigger, you would need to use a LIKE clause in your query:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE ACTION_STATEMENT LIKE '%your_statement%';

Output Table:

TRIGGER_NAME

EVENT_MANIPULATION

EVENT_OBJECT_TABLE

ACTION_STATEMENT

ACTION_TIMING

TRIGGER_SCHEMA

trigger1

INSERT

your_table

BEGIN your_statement ... END

BEFORE

your_schema

trigger2

UPDATE

another_table

BEGIN your_statement ... END

AFTER

another_schema

You can combine multiple filters to get more specific results:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE EVENT_MANIPULATION = 'INSERT' 
  AND ACTION_TIMING = 'BEFORE' 
  AND ACTION_STATEMENT LIKE '%your_statement%';

Output Table:

TRIGGER_NAME

EVENT_MANIPULATION

EVENT_OBJECT_TABLE

ACTION_STATEMENT

ACTION_TIMING

TRIGGER_SCHEMA

trigger1

INSERT

your_table

BEGIN your_statement ... END

BEFORE

your_schema

To view triggers in MySQL Workbench:

• Connect to your MySQL database.
• In the Navigator panel, expand the schema containing the table you are interested in.
• Expand the "Tables" node and then expand the specific table.
• You will see a "Triggers" sub-node under the table. Clicking on this will list all triggers for that table.
• Right-click on a trigger and select "Alter Trigger..." to view its detail

The SHOW TRIGGERS of MySQL may be used with additional conditions or clauses to provide results and narrow the search for specific triggers. Here is how it can be used with various options:

This clause names the database from which you want to display triggers. If not given, the default is the currently selected database.

SHOW TRIGGERS FROM my_database;

Shows all triggers from the my_database database.

This clause filters the results by giving back a trigger that matches a given pattern. The pattern may include wildcard characters like %.

SHOW TRIGGERS LIKE 'before_%';

Displays triggers whose names start with before_

The following are a few examples of how the SHOW TRIGGERS is used in MySQL, together with descriptions of what the typical output looks like:

SHOW TRIGGERS;

Explanation:

Lists all triggers in the current database, together with their names, what events trigger them, the tables they are associated with, what SQL statements they execute, and when they fire and were created.

Output:

Trigger Name

Event

Table

Statement

Timing

Created

trg_before_insert

INSERT

employees

INSERT INTO audit_log (action) VALUES ('inserted')

BEFORE

2024-07-01 10:00:00

trg_after_update

UPDATE

employees

UPDATE audit_log SET action='updated' WHERE id=NEW.id

AFTER

2024-07-01 10:05:00

SHOW TRIGGERS FROM my_database;

It will give all the triggers within your databases. Replace the "my_database" with your database name.

Trigger Name

Event

Table

Statement

Timing

Created

trg_before_delete

DELETE

orders

INSERT INTO audit_log (action) VALUES ('deleted')

BEFORE

2024-07-01 11:00:00

trg_after_insert

INSERT

orders

UPDATE inventory SET quantity=quantity-1 WHERE id=NEW.item_id

AFTER

2024-07-01 11:10:00

SHOW TRIGGERS LIKE 'trg_after%';

Explanation:

Lists triggers whose names match the pattern trg_after%. This is useful to find triggers that are fired after some significant event.

Output:

Trigger Name

Event

Table

Statement

Timing

Created

trg_after_update

UPDATE

employees

UPDATE audit_log SET action='updated' WHERE id=NEW.id

AFTER

2024-07-01 10:05:00

Finally, one of the greatest features of MySQL is the SHOW TRIGGERS command, which makes it easier than ever for a database administrator or developer to manipulate and debug triggers within their databases. This command comes in handy when showing details of the trigger name, the table to which it relates, the events, and the timing of its execution—everything one would want to know about when and how a trigger is fired. If you want to see all triggers in your database, filter for specific patterns, or see detailed metadata using the information_schema, it makes it very convenient to monitor and manage automated actions within a database.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4