Almost all developers are heard about Triggers and all knows that MySQL support triggers and triggers are adding advantages to MySQL.Triggers are the SQL statements are stored in the database.
Triggers are the SQL statements which add functionality to your tables so that they perform a certain series of actions when a some queries are executed. We can say in easy language is Triggers are some conditions performed when INSERT, UPDATE or DELETE events are made in the table without using two separate queries.A trigger is a kind of database event which is used to activate on some event and the corresponding sql statement executed immediately.
Sometimes developers prefer to use store procedures rather than triggers but triggers are one kind of store procedures which contain procedural code into the body.The difference between a trigger and a stored procedure is that a trigger is called when an event occurs in a table whereas a stored procedure must be called explicitly.You will also like to read To pass limits with store procedure in MySQL and MySQL FLUSH Commands.
SYNTAX:
1 2 3 4 5 | CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_body |
CREATE TRIGGER statement is used to create triggers.Trigger action time can be BEFORE or AFTER.You can use BEFORE when you want to process action before changes are made in the table and AFTER if you need to process action after changes are made in the table.trigger_body is the statement to execute when the trigger activates
trigger_event indicates the type of statement that activates the trigger. trigger_event can be one of the following values:
INSERT: When insert a new record into the table, trigger is activated. For example, Through INSERT, LOAD DATA, and REPLACE statements.
UPDATE: When update any of the record, activate the trigger using the UPDATE statements.
DELETE: When delete a record from the table, trigger is activated using DELETE and REPLACE statements.
Let’s start creating the trigger in MySQL with simple example.In the database, we have table customers as follows
1 2 3 4 5 6 7 8 | CREATE TABLE customers ( customer_id int(11) NOT NULL, lastName varchar(50) NOT NULL, firstName varchar(50) NOT NULL, PRIMARY KEY ( customer_id ) ) |
Now let’s create relational table for customer.you need to create a new table called customer_info_rel.
1 2 3 4 5 6 7 8 | CREATE TABLE customer_info_rel ( id int(11) NOT NULL AUTO_INCREMENT, customer_id int(11) NOT NULL, email varchar(50) NOT NULL, PRIMARY KEY (id) ) |
So now I am going to apply trigger on the basis of the relational table.If I delete user or customer from main table customers then triggers occur which delete the record from relational table customer_info_rel also.
1 2 3 4 5 6 7 8 9 10 | DELIMITER $$ CREATE TRIGGER trigger_customer BEFORE DELETE ON customers FOR EACH ROW BEGIN DELETE FROM customer_info_rel WHERE customer_id = OLD.id; END$$ DELIMITER; |
NOTE: For Trigger you need to use DELIMITER $$ before the trigger and DELIMITER ; after the trigger.
ADVANTAGES:
- Triggers are the alternate way for data integrity.
- Trigger is useful when you want to audit the changes of data in a database table.
DISADVANTAGES: (Some points Which are not supported in Trigger)
- Transaction isn’t allow in trigger.
- We can’t use return statement in trigger.
- Can’t call store procedure with trigger.
- Can’t use trigger with views.
Next we will check for stored procedures.Go ahead & use MySQL triggers in your projects. If you have any question, feel free to ask via the comment section. If you find this tutorial useful, do share it on Facebook & Twitter.
Comments (1)