End to End Technology Solutions
A trigger is a stored program that gets invoked automatically whenever any event such as insertion, update, or deletion occurs in a table.
For instance, whenever we add a new row or delete a row, we can define a trigger that gets triggered automatically through which we can be assured of the proper happenings of those events. Each trigger is associated with a table.
The trigger is a special procedure but not a stored procedure. The key difference between both is that a stored procedure needs to be called explicitly while the special procedure(trigger) gets called automatically when the event linked to it happens.
Use of triggers:
- Data can be validated even before they are inserted or updated with the help of triggers.
- A log of records can be kept using triggers.
- Triggers provide an alternative way to check the integrity of data.
- Triggers also provide an alternative way to run the scheduled task.
- The performance of SQL queries can be improved with triggers because it does not need to compile each time the query is executed.
Limitations of triggers:
- There will be an increase in the overhead of the database server with the usage of triggers.
- Users can not troubleshoot what happens in the database layer as they get invoked automatically and invisibly.
How to create triggers:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TRIGGER trigger_name (AFTER | BEFORE) (INSERT | UPDATE | DELETE) ON table_name FOR EACH ROW BEGIN -declarations of variables -code for the trigger END; |
Example:
Step1: Creating a table named person
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE person( name varchar(45) NOT NULL, occupation varchar(35) NOT NULL, date_of_working date, working_hours varchar(10) ); |
Step2. Code for trigger creation
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> DELIMITER // mysql> Create Trigger before_insertion BEFORE INSERT ON employee FOR EACH ROW BEGIN IF NEW.working_hours < 0 THEN SET NEW.working_hours = 0; END IF; END // |
Step3: Inserting values into the table named person
INSERT INTO person VALUES
|
1 2 3 4 5 6 7 |
('Rahul', 'Astronaut', '2022-03-03', 12), ('Raju', 'Engineer', '2022-03-03', 10), ('Ravi', 'Painter', '2022-04-04', 13), ('Rajesh', 'Doctor', '2022-04-04', 14) |
Here, we are changing the delimiter to // so that it will not be terminated for the default termination character(;). With the help of this trigger, conditions like entering negative
values can be checked and acted upon by replacing them with values like 0 or any other value.
Example2:
Step1: First create a table named test_trigger as follows:
|
1 |
CREATE TABLE test_trigger(message VARCHAR(100)); |
Step2: Creating a table named person
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE employee( name varchar(45) NOT NULL, occupation varchar(35) NOT NULL, sex varchar(5) ); |
Step3: Create the trigger
|
1 2 3 |
mysql> DELIMITER // mysql> Create Trigger my_trigger |
BEFORE INSERT ON employee FOR EACH ROW
BEGIN
|
1 2 3 4 5 6 7 8 9 10 11 |
IF NEW.sex = "M" THEN INSERT INTO test_trigger VALUES("Added male employee"); ELSE INSERT INTO test_trigger VALUES("Added female employee"); END IF; END // |
Step4. Inserting values into a table named person
INSERT INTO employee VALUES
|
1 2 3 |
('Rahul', 'Astronaut', 'M'), ('Raju', 'Engineer', 'F') |
The above trigger adds the value based on what the user inserts into the gender column of the employee table. If we execute the “SELECT * FROM test_trigger” it will show the message column with the corresponding values depending on the INSERT operation on the employee table
To check all the triggers available in a database and associated with a table, the following statement works:
|
1 |
mysql> SHOW TRIGGERS FROM mydb WHERE table = 'person'; |
To drop a particular trigger, the following statement works:
|
1 |
mysql> DROP TRIGGER IF EXISTS mydb.before_insertion; |
In a similar fashion, triggers for conditions like before_updation, after_updation, before_deletion, and after_deletion can be created and used.