DBMS MYSQL TRIGGER IN HINDI
If we understand MySQL Trigger in simple language, then it is a system that we set on any table and as soon as any operation like Insert Update and Delete is performed on that table, it becomes Trigger Call, and which Also we have written the code in Trigger, it starts working according to it.
WHAT IS TRIGGER IN MYSQL?
If we understand the Trigger, then it is the Button of the Gun that as soon as we press the Bullet out of the Gun, in the same way as soon as any of the Insert / Update / Delete operations happen on the table, the Trigger will be executed. .
We can write Maximum 6 Triggers on a table. MySQL Trigger Before Insert
- MySQL Trigger Before Update
- MySQL Trigger Before Delete
- MySQL Trigger After Insert
- MySQL Trigger After Update
- MySQL Trigger After Delete
ADVANTAGE SQL TRIGGER
- Reduce Work: Makes the work of the programmer very easy because once written, it keeps on working automatically.
- Validation Checking: If we write Before Trigger then we can do Validation Check on it before entry in the table.
- Mobile & Web: When we use the same application in mobile and web, then Trigger comes in handy for many tasks. Because with the help of this, we transfer the common coding from PHP to the database and that coding is useful to us on both the frontend (Web and Mobile).
- Fast : Triggers are very fast, if the data of one table has to be accessed in different tables, then we can easily calculate anything and access the data in another table .
DELIMITER
To close any syntax in MySQL as well; (semicolon) is used, and also to close the trigger / function; (Semicolon) is needed only if we are at both the places; (Semicolon) then the system will not be able to find out from where the start is done and where is the end. By applying the delimiter, we tell the system that whatever syntax we are writing below, we will close it with $ $. And after coding again we get into the Delimiter; (Semicolon). And whatever we write our logic in the middle of BEGIN or END, we will have; Use semicolon only.
DROP TRIGGER <TRIGGER NAME> (DELETE TRIGGER MYSQL)
Drop Trigger MySQL : When we create First Time Trigger then it will give Syntax Error because Trigger is not yet created, but on doing Second Time Execute the error will not come because now the trigger has been created, it is written so that whenever we get If you want to change the trigger, then we can easily make changes.
CREATE TRIGGER <TRIGGER NAME>
- By whatever name we want to become a trigger, we write the name in Angle Braces.
- The name should be unique throughout the database, so always include the name of the table in the name of the trigger so that it remains unique.
- You cannot enter a space in the name of a trigger.
- Keep the names of the triggers in such a way that it is easy for you to understand and keep the names of all the triggers created in a database with a pattern so that it is easy for you to search and understand.
OUR OTHER ARTICLES
- MySQL View
- MySQL Stored Procedure
- Loop in Select Query | Loop in MySQL
- MySQL (Row to Column) & (Column to Row ) Interchange Logic
- Update Stock/Balance Using Trigger
BEFORE
- As soon as any event like Insert / Update / Delete will be performed, then if we have to execute any code before that then we use BEFORE .
- BEFORE Trigger is used for most of the validations.
- If we have to update some data in the table on the basis of a key before insertion, then we use Before.
- If we want to store the value by doing any calculation in a column or store the default value, then we use BEFORE .
- Can update value on same table.
AFTER
- When an operation is to be performed after the data is inserted/update/deleted in the table, then we use AFTER trigger.
- Mostly AFTER is used in a table to update the status on the base of this table.
- If the data is to be moved to another table, then AFTER is used.
- If any summary is to be updated based on the current data, then we use AFTER Trigger.
- With AFTER Trigger, we cannot perform any operation by doing the same table, that is, we cannot do Insert / Update / Delete on the same table on which we are writing Trigger.
Other Points
- Whenever you name the trigger, it should also include After / Before so that the unique name can be kept.
- Whenever you keep the name, it is not necessary that you can also use their short form written before or after, like A / B etc.
TRIGGER EVENT : INSERT/UPDATE/DELETE
- In MySQL we can perform only one INSERT/UPDATE/DELETE operation at a time on a trigger.
- You can use the ins/upd/del postfix when naming the trigger.
- Update Trigger in MySQL: When we write only Update Trigger, then where does it go to Update Trigger.
ON <TABLENAME>
- The name of the table on which we are writing Trigger is written here.
TRIGGER BODY : BEGIN … END
- This is the BODY of the trigger, in which you can write whatever SQL statement you want to write.
- define variables here
- any kind of condition
- Writing SQL Statements like SELECT/UPDATE/DELETE
- Can use SET and NEW/OLD
SET
We use this keyword mostly in Before Trigger. All the columns of the table that are used in Trigger come in one Buffer. And we use SET Keyword to set the value on them.
EXTENSION : OLD/NEW :
It is a kind of Buffer in which the names and values of all the columns of the table we are using are stored, in the case of Insert / Update and Delete, it works like this.
- INSERT: Only NEW buffer works in this.
- UPDATE: In this, both OLD and NEW Buffer work, the value before the value is updated in the OLD, and the updated value is stored in NEW.
- DELETE : Only OLD Buffer remains in this.
- OLD Keyword is readonly , you cannot make any changes in it.
- Changes can be made in the new keyword only in the case of BEFORE
WHAT PROBLEMS MYSQL TEAM FACE WHEN TRIGGERS CONCEPT WAS NOT THERE IN MYSQL?
- If there is no Trigger Concept in MySQL, then the developer will have to write a lot of code on the front end, let’s understand all the points.
- A lot of coding will have to be done on the front end
- The validation that can be applied at the time of entry of data, they will not be able to apply.
- The work that we could do by taking the data in Auto Table with Trigger, for that we have to write Query with the help of View or SQL which will take time to execute.
- Triggers are fast because they perform the operation in the database itself, whereas if we do the same work by writing it separately, then we may also have issues of Bandwidth and Server Speed.
- Overall we can say that if there are no triggers in the database, then the developer will have to work very hard in making the project and will also have to face the issue of efficiency and speed in the project.
WHY TRIGGERS ARE DEVELOPED WHAT WOULD HAPPEN IF TRIGGERS ARE NOT THERE IN MYSQL?
- Trigger was created to increase the performance of the database.
- The work that we used to perform in the database from outside, we do that work by staying in the database.
- As soon as we do Insert / Update / Delete , it performs the operations written on them, which saves the time of subsequent SQL execution.
- Comes handy for Trigger Validation .
- Nowadays we can also send Https request from trigger.
WHAT IS TRIGGER IN DBMS IN HINDI ? / WHAT IS TRIGGER IN SQL IN HINDI ?
The way we have written Trigger in MySQL, we can write Trigger in other DBMS also, there may be some difference in Syntax, except Syntax, all other features are same in Trigger.
Internal Link – Newtimezone
