Skip to content

Latest commit

 

History

History
133 lines (93 loc) · 4.24 KB

019-triggers-in-sql.md

File metadata and controls

133 lines (93 loc) · 4.24 KB

Triggers In SQL

A trigger is a stored procedure in database which is automatically invoked whenever any special event occurs in the database. The event can be any event including INSERT, UPDATE and DELETE.

For eg: If you want to perfom a task after a record is inserted into the table then we can make use of triggers

Syntax for creating triggers

create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
[for each row | for each column]  
[trigger_body] 

create trigger [trigger_name] : Creates or replaces an existing trigger with the trigger_name.

[before | after] : Now we can specify when our trigger will get fired. It can be before updating the database or after updating the database.

Generally , before triggers are used to validate the data before storing it into the database.

{insert | update | delete} : Now, we specify the DML operation for which our trigger should get fired .

on [table_name] : Here, we specify the name of the table which is associated with the trigger.

[for each row] : This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.

[for each column] : This specifies a column-level trigger, i.e., the trigger will be executed after the specified column is affected.

[trigger_body] : Here, we specify the operations to be performed once the trigger is fired.

Show Trigger

If you want to see all the triggers that are present in your database.

show triggers in database_name;

Drop Trigger

if you no longer want your trigger then you may delete it.

drop trigger trigger_name;

Example :

Let us consider we have our database named library. Consider a scenario where we want a trigger which is fired everytime any particular book is inserted into the books table . The trigger should add the logs of all the books that are inserted into the books table.

We have created two tables :

  1. books : It will store all the books available in the library
  2. bookrecord : It will generate a statement a log for the inserted book
Select * from library.books;
+----------+---------------+
| book_id  | book_name     | 
+----------+---------------+
| 	   | 		   |
| 	   | 		   |
+----------+---------------+

Here, book_id is an auto-incremental field.

Select * from library.bookrecord;
+----------+---------------+-----------+
| SRNO     | bookid        | statement |
+----------+---------------+-----------+
|          |               |           |
|          | 		   |           |
+----------+---------------+-----------+

Here, SRNO is an auto-incremental field.

Now, we will create our trigger on the books table

create trigger library.addstatement
after insert 
on library.books
for each row
insert into library.bookrecord(bookid,statement) values (NEW.book_id,concat('New book named ',NEW.book_name,"  added at ",curdate()));

In MySQL, NEW is used to access the currently inserted row. We are inserting the log for the currently inserted book in our database.

Now we will insert a book and wait for the output.

insert into library.books(book_name) values ("Harry Potter and the Goblet of fire");

Output for books:

+----------+-----------------------------------------------+
| book_id  | book_name  				   | 
+----------+-----------------------------------------------+
|    1	   | 	Harry Potter and the Goblet of fire	   |
| 	   | 		  				   |
+----------+-----------------------------------------------+

Output for bookrecord:

+----------+---------------+----------------------------------------------------------------------------------+
| SRNO     | bookid        | statement									      |
+----------+---------------+----------------------------------------------------------------------------------+
|   1      |      1        |     New book named Harry Potter and the Goblet of fire  added at 2021-10-22      |
|          | 		   |          									      |
+----------+---------------+----------------------------------------------------------------------------------+

See. it worked!!

Conclusion:

Here, you learnt what are triggers and how you create them. You can create different types of triggers based on your needs and requirements.