- MySQL Home
- MySQL tutorial
- MySQL Installation
- MySQL Statements
- MySQL Data Types
- Mysq CreateDatabase
- MySQL Create Table
- MySQL Constraints
- MySQL Primary key
- MySQL Foreign key
- Auto Increment Field
- MySQL Default Cons
- MySQL Insert Into
- MySQL Update
- MySQL Drop
- MySQL Delete
- MySQL Select
- MySQL Where
- MySQL Group By
- MySQL Having clause
- MySQL Order By
- MySQL Alter table
- MySQL Alias
- MySQL Create Index
- MySQL Subqueries
- MySQL Operators
- Mysql IsNull IsNotNull
- MySQL And or
- MySQL Between
- MySQL In Operator
- MySql Like Operator
- Mysql Union Operator
- Mysql Storage Engine
- Mysql Stored Procedure
- Mysql if Statement
- Mysql Case Statement
- MySQL Loops
- MySQL Cursor
definition :-a trigger or database trigger is a stored program that is executed automatically to respond to a specific event associated with table e.g. insert, update or delete.it is powerful tool for protecting the integrity of the data in you mysql databases.it is useful to operations such as auditing and logging.
note :- mysql triggers are activated by sql statement only.
a sql trigger is a set of sql statements stored in the database catalog.trigger is a special type of stored procedure this is called automatically when a data modification event is made against a table.
advantages of using sql triggers
- sql triggers provide an alternative way to check the integrity of data
- sql triggers can catch errors in business logic in the database layer
- sql triggers are very useful to audit the changes of data in tables
disadvantages of using sql triggers
- sql triggers may increase the overhead of the database server
- sql triggers only can provide an extended validation and they ca't replace all the validations
mysql trigger syntax
to create mysql trigger you can use the create trigger statement.
note : mysql triggers are activated by sql statements only.
MySQL Create Trigger syntax:
create trigger trigger_name trigger_time trigger_event on table_name for each row begin trigger_body end
the sql statements are placed between begin and end block
the old and new keywords are very handy.the new keyword refers to the new row after you change the data and the old keyword refers to the existing record before you change the data.
triggers for a table are also dropped if you drop the table.
you cannot associate a trigger with a temporary table or view.
triggers names exist in the schema namespace, meaning that all triggers must have unique names within a schema.triggers in different schemas can have the same name.
you cannot have two triggers for a table that have the same activation time and activation event
mysql trigger example
the after update trigger is almost identical:
delimiter $$ create trigger 'blog_after_insert' after insert on 'blog' for each row begin if new.deleted then set @changetype = 'delete' ; else set @changetype = 'new'; end if; insert into audit (blog_id,changetype) values (new.id, @changetype); end$$ delimiter;
mysql drop trigger
to remove a trigger, you can use drop trigger statement
drop trigger table_name.trigger_name;
in mysql, a trigger is a set of sql statements that is invoked automatically when a change is made to the data on the associated table.
trigger can be invoked either before or after the data is changed by insert, update or delete statements.
the mysql allows you to define maximum triggers for each table.
- before insert - activated before data is inserted into the table
- before update - activated before data in the table is updated
- before delete - activated before data is removed from the table
- after insert - activated after data is inserted into the table
- after update - activated after data in the table is updated
- after delete - activated after data is removed from the table
note :- when you use a statement that makes changes to the table but does not use delete, insert or update statement, the trigger is not invoked.
mysql trigger limitations
- mysql triggers can't call a stored procedure or stored function
- mysql triggers can't use dynamic sql statements
- mysql triggers can't use show, load data, load table, backup database, restore, return statements