MySQL Triggers - StudySection

1
MySQL Triggers

• WWW.STUDYSECTION.COM

2
MySQL Triggers

• MySQL Triggers are a set of SQL statements. It is
  a stored procedure that is invoked automatically
  in response to any event. Each Trigger is
  activated on any DML statement such as INSERT,
  UPDATE, DELETE, SELECT. Each Trigger is
  associated with the database table.
• A trigger seems to be the same as a procedure,
  the main difference is that trigger is called
  automatically when any data modification is made
  against the table. Triggers cannot be called
  directly thats why they are called special
  procedures. In contrast, stored procedures must
  be called explicitly.

3
Triggers are of 2 types according to the
SQL standard

• Row Level Trigger A row Level trigger is a
  trigger that is activated for each row by a
  triggering statement such as insert, update or
  delete. For example, if insertion, deletion or
  updation is made in a table then the triggers are
  called automatically upon the rows affected by
  the respective statement.
• Statement Level Trigger A statement Level
  trigger is a trigger that is fired once for each
  event that occurs on a table regardless of how
  many rows are inserted, updated, or deleted.

4
We need triggers for the following reasons-

• Triggers help us to validate data even before it
  is inserted.
• An Alternate way is provided by triggers to run
  the scheduled task.
• Triggers increase the performance of SQL queries
  because it does not need to be compiled each time
  the query is executed.
• Triggers save time and effort as client-side code
  is reduced.
• Triggers also help to scale our application
  across different platforms.
• Triggers are easy to maintain.

5
Limitations of using Triggers in MySql-

• MySQL triggers do not allow the use of all
  validations they only provide extended
  validations. For example, we can use the NOT
  NULL, UNIQUE, CHECK, and FOREIGN KEY constraints
  for simple validations.
• A Client application is not aware of the
  execution of triggers. Therefore, it is not easy
  to recognize what happens in the database layer.
• Triggers may increase the overall cost of the
  database server.

6
Types of triggers in MYSQL -

• Before Insert Trigger is activated before the
  insertion of data into the table.
• After Insert Trigger is activated after the
  insertion of data into the table.
• Before Update Trigger is activated before the
  data update into the table.
• After Update Trigger is activated after the data
  update into the table.
• Before Delete Trigger is activated before the
  data is removed from the table.
• After Delete Trigger is activated after the data
  deletion from the table.

7

• Naming Conventions
• A unique name is used for each trigger associated
  with each table.
• Following are the instructions to name the
  trigger.
• (BEFORE AFTER) table_name (INSERT UPDATE
  DELETE)
• Trigger Activation Time Before/After
• Trigger Event Insert/Update/Delete

8

• Statement to create the trigger
• CREATE TRIGGER trigger_name
• (AFTER BEFORE) (INSERT UPDATE DELETE)
• ON table_name FOR EACH ROW
• BEGIN
• --variable declarations
• --trigger code
• END

9
Online MySQL Certificate Exam

• MySQL Certification Exam (Foundation)
• MySQL Certification Exam (Advanced)
• MySQL Certification Exam (Expert)
• Mysql Database Developer Diploma Exam
  (Foundation)
• Mysql Database Developer Diploma Exam (Advanced)

10
About StudySection

• Welcome to StudySection - the most loved online
  platform for eCertification in several subjects
  including but not limited to Software
  Development, Quality Assurance, Business
  Administration, Project Management, English,
  Aptitude and more. From more than 70 countries
  students are StudySection Certified. If you are
  not yet StudySection certified it's not late. You
  can start right now.
• Being StudySection Certified helps you take your
  education level few notches up and have an edge
  over other candidates when you need it the most.
  Globally, our students are employed in different
  organizations and are utilizing the benefit of
  being certified with us.