MySQL Triggers - StudySection - PowerPoint PPT Presentation

About This Presentation
Title:

MySQL Triggers - StudySection

Description:

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. – PowerPoint PPT presentation

Number of Views:122
Slides: 11
Provided by: studysection

less

Transcript and Presenter's Notes

Title: 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.
Write a Comment
User Comments (0)
About PowerShow.com