mysql triggers

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

syntax:

          drop    trigger      table_name.trigger_name;

mysql triggers

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