1 / 10

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.<br><br>

StudySection
Download Presentation

MySQL Triggers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 that’s 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.

More Related