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