MYSQL Drop Trigger
MYSQL
The DROP TRIGGER statement is used to drop triggers in MySQL.
MySQL DROP TRIGGER
➔ Dropping the trigger will permanently delete it from the database.
➔ The user must have TRIGGER privilege to drop a trigger.
➔ Dropping the trigger is a permanent action and cannot be undone.
➔ Dropping a table automatically drops all associated triggers as well.
Syntax
/* Drop a trigger */
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
/* example */
/* DROP TRIGGER IF EXISTS trigger_after_insert_demo_users; */
➔ IF EXISTS: The optional IF EXISTS clause prevents an error from occurring if the trigger does not exist. Instead, MySQL will issue a warning note.
➔ schema_name: Specifies the name of the database. If omitted, the currently selected database will be used.
➔ trigger_name: Specifies the name of the trigger to delete.
Problem definition:
Problem: Create and apply a trigger to log when there are new entries in the table and finally DROP it.
SQL code:
Example
/* Create table */
CREATE TABLE demo_users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
deptId INT NOT NULL
);
/* Insert values */
INSERT INTO demo_users (username, deptId)
VALUES ("John Smith", 101);
/* Create table */
CREATE TABLE demo_audit_log (
log_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
action_type VARCHAR(50),
changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
/* Change the delimiter from the default (;) to $$ */
DELIMITER $$
CREATE TRIGGER trigger_after_insert_demo_users
AFTER INSERT ON demo_users
FOR EACH ROW
BEGIN
INSERT INTO demo_audit_log (user_id, action_type)
VALUES (NEW.id, 'User Added');
END$$
/* Reset the delimiter back to the default semicolon (;) */
DELIMITER ;
/* Insert a new user */
INSERT INTO demo_users (username, deptId) VALUES ('Mary Johnson', 102);
/* query to the log table to see the new entry */
SELECT * FROM demo_audit_log;
/* Show all trigger */
SHOW TRIGGERS;
/* Drop a trigger */
DROP TRIGGER IF EXISTS trigger_after_insert_demo_users;
Copy the code and try it out practically in your learning environment.
Show Trigger:
Example
/* Show all trigger */
SHOW TRIGGERS;
Copy the code and try it out practically in your learning environment.
Drop Trigger:
Example
/* Drop a trigger */
DROP TRIGGER IF EXISTS trigger_after_insert_demo_users;
Copy the code and try it out practically in your learning environment.