Triggers are of 2 types according to the SQL standard.<\/h2>\n\n- Row Level Triggers<\/li>\n
- Statement Level Triggers<\/li>\n<\/ul>\n
Row Level Trigger:<\/strong> 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. <\/p>\nStatement Level Trigger:<\/strong> 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.<\/p>\nWe need triggers for the following reasons.<\/p>\n
\n- Triggers help us to validate data even before it is inserted.<\/li>\n
- An Alternate way is provided by triggers to run the scheduled task.<\/li>\n
- Triggers increase the performance of SQL queries because it does not need to be compiled each time the query is executed. <\/li>\n
- Triggers save time and effort as client-side code is reduced.<\/li>\n
- Triggers also help to scale our application across different platforms.<\/li>\n
- Triggers are easy to maintain.<\/li>\n<\/ul>\n
Limitations of using Triggers in MySql<\/h3>\n\n- 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.<\/li>\n
- A Client application is not aware of the execution of triggers. Therefore, it is not easy to recognize what happens in the database layer.<\/li>\n
- Triggers may increase the overall cost of the database server.<\/li>\n<\/ul>\n
Types of triggers in MYSQL<\/h3>\n\n- Before Insert:<\/strong> Trigger is activated before the insertion of data into the table.<\/li>\n
- After Insert:<\/strong> Trigger is activated after the insertion of data into the table.<\/li>\n
- Before Update:<\/strong> Trigger is activated before the data update into the table.<\/li>\n
- After Update:<\/strong> Trigger is activated after the data update into the table.<\/li>\n
- Before Delete:<\/strong> Trigger is activated before the data is removed from the table.<\/li>\n
- After Delete:<\/strong> Trigger is activated after the data deletion from the table.<\/li>\n<\/ul>\n
Naming Conventions<\/strong>
\nA unique name is used for each trigger associated with each table.
\nFollowing are the instructions to name the trigger.<\/p>\n(BEFORE | AFTER) table_name (INSERT | UPDATE | DELETE)<\/em><\/strong><\/p>\nTrigger Activation Time : Before\/After
\nTrigger Event : Insert\/Update\/Delete<\/em><\/p>\nStatement to create the trigger:<\/strong>
\nCREATE TRIGGER trigger_name
\n (AFTER | BEFORE) (INSERT | UPDATE | DELETE)
\n ON table_name FOR EACH ROW
\n BEGIN
\n --variable declarations
\n --trigger code
\n END;<\/code><\/p>\nLet’s Take an example to understand the Triggers<\/h3>\n
Now, we will create a Trigger that makes modifications to the employee table. Let\u2019s create a new table employee by executing the below statement:
\nCREATE TABLE employee(
\n name varchar(45) NOT NULL,
\n occupation varchar(35) NOT NULL,
\n working_date date,
\n working_hours varchar(10)
\n);
\nNext, execute the below statement to fill the records into the employee table:
\nINSERT INTO employee VALUES
\n('Sunil', 'Scientist', '2021-10-04', 12),
\n('Rahul', 'Engineer', '2021-10-04', 10),
\n('Raman', 'Teacher', '2021-10-04', 13),
\n('Mandeep', 'Doctor', '2021-10-04', 14)<\/code>
\nNext, execute the SELECT statement to verify the inserted record:<\/p>\n\n\n\nname<\/th>\n occupation<\/th>\n working_date<\/th>\n working_hours<\/th>\n<\/tr>\n<\/thead>\n \n\nSunil<\/td>\n Scientist<\/td>\n 2021-10-04<\/td>\n 12<\/td>\n<\/tr>\n \nRahul<\/td>\n Engineer<\/td>\n 2021-10-04<\/td>\n 10<\/td>\n<\/tr>\n \nRaman<\/td>\n Teacher<\/td>\n 2021-10-04<\/td>\n 13<\/td>\n<\/tr>\n \nMandeep<\/td>\n Doctor<\/td>\n 2021-10-04<\/td>\n 14<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Row Level Trigger:<\/strong> 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. <\/p>\n Statement Level Trigger:<\/strong> 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.<\/p>\n We need triggers for the following reasons.<\/p>\n Naming Conventions<\/strong> (BEFORE | AFTER) table_name (INSERT | UPDATE | DELETE)<\/em><\/strong><\/p>\n Trigger Activation Time : Before\/After Statement to create the trigger:<\/strong> Now, we will create a Trigger that makes modifications to the employee table. Let\u2019s create a new table employee by executing the below statement:\n
Limitations of using Triggers in MySql<\/h3>\n
\n
Types of triggers in MYSQL<\/h3>\n
\n
\nA unique name is used for each trigger associated with each table.
\nFollowing are the instructions to name the trigger.<\/p>\n
\nTrigger Event : Insert\/Update\/Delete<\/em><\/p>\n
\nCREATE TRIGGER trigger_name
\n (AFTER | BEFORE) (INSERT | UPDATE | DELETE)
\n ON table_name FOR EACH ROW
\n BEGIN
\n --variable declarations
\n --trigger code
\n END;<\/code><\/p>\nLet’s Take an example to understand the Triggers<\/h3>\n
\nCREATE TABLE employee(
\n name varchar(45) NOT NULL,
\n occupation varchar(35) NOT NULL,
\n working_date date,
\n working_hours varchar(10)
\n);
\nNext, execute the below statement to fill the records into the employee table:
\nINSERT INTO employee VALUES
\n('Sunil', 'Scientist', '2021-10-04', 12),
\n('Rahul', 'Engineer', '2021-10-04', 10),
\n('Raman', 'Teacher', '2021-10-04', 13),
\n('Mandeep', 'Doctor', '2021-10-04', 14)<\/code>
\nNext, execute the SELECT statement to verify the inserted record:<\/p>\n\n\n
\n \nname<\/th>\n occupation<\/th>\n working_date<\/th>\n working_hours<\/th>\n<\/tr>\n<\/thead>\n \n Sunil<\/td>\n Scientist<\/td>\n 2021-10-04<\/td>\n 12<\/td>\n<\/tr>\n \n Rahul<\/td>\n Engineer<\/td>\n 2021-10-04<\/td>\n 10<\/td>\n<\/tr>\n \n Raman<\/td>\n Teacher<\/td>\n 2021-10-04<\/td>\n 13<\/td>\n<\/tr>\n \n Mandeep<\/td>\n Doctor<\/td>\n 2021-10-04<\/td>\n 14<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
![\"SQL\"\/](\"https:\/\/studysection.com\/blog\/wp-content\/uploads\/2021\/07\/cmd-1.png\")