SQL & My SQL Tutorial
- What is SQL?
- SQL Microsoft
- SQL Management Tools
- SQL Developer
- Docker Commands
- Composite Key in SQL
- SQL Constraints
- Transactions in SQL Server
- SQL Server Data Types
- SQL Update Join
- SQL Operators
- SQL Clauses
- SQL Commands
- SQL Alter Command
- Distinct Keyword in SQL
- SQL Statements
- SQL Index
- SQL Injections
- Wildcards in SQL
- Alter and Truncate Command in SQL
- SQL Null Functions – ISNULL, IFNULL, Combine, & NULLIF
- SQL Sequence
- How to Find Duplicate Records in SQL
- Primary and Foreign Key in SQL with Examples
- COUNT Function
- SUM Function in SQL
- Dynamic SQL
- Database Tuning
- Pseudocolumn in Oracle SQL
- Triggers in SQL
- Embedded SQL in DBMS
Triggers in SQL
What are SQL Triggers?
Triggers are programs that are available in the memory, with unique names made up of SQL queries which we need to fire on our database on and off.
We can also say “Triggers are programs in the memory with unique names and help us to reuse the queries once written and kept safe in the memory”.
Triggers can be made to insert, update and delete statements in SQL. We have two types of triggers:
Row Level Triggers
In a row-level trigger, the changes are put on all the rows on which the insert, delete, or update transaction is performed.
- If we have 5000 rows in a database and a delete operation is being run on them, then the trigger would also run 5000 times automatically. It is accessible in MySQL.
Statement Level Triggers
In the statement-level triggers, the operation is under execution only once no matter how many rows are involved in the operation. These triggers are not accessible by MySQL.
Why do we need Triggers in SQL?
We have seen what are triggers, now let us try to understand the need for triggers in SQL. Some of the aspects of using triggers in SQL database transactions are as follows:
- Allows the reuse of code.
- Helps us increase the computational costs by reducing result times.
CREATE TRIGGER triggerName BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation;
We have six different variations of triggers.
In these triggers, the operation on rows is under execution before any INSERT operation on the database.
In these triggers, the operation on rows is under execution after any INSERT operation on the database.
In these triggers, the operation on rows is under execution before an UPDATE operation on the database.
In these triggers, the operation on rows is under execution after an UPDATE operation on the database.
In these triggers, the operation on rows is under execution before any DELETE operation is run on the database.
In these triggers, the operation on rows is under execution after any DELETE operation on the database.
Let us now look at how we use triggers with the help of an example.
Let us create a trigger on our table IBM whenever an update operation is run on our database.
Let us first initially view the contents of the IBM database.
SELECT * FROM IBM;
Use IBM; -- Trigger being created CREATE TRIGGER increase_experience AFTER UPDATE ON IBM FOR EACH ROW SET @experience = @experience + 1; -- Trigger put to use UPDATE IBM SET experience = 1 WHERE experience = 0; -- View the result of Trigger SELECT * FROM IBM;
Advantages of Triggers in SQL
Some of the prominent advantages of triggers are as follows:
- Helps us to automate the data alterations.
- Allows us to reuse the queries once written.
- Provides a method to check the data integrity of the database.
- Helps us to detect errors on the database level.
- Allows easy auditing of data.
Disadvantages of Triggers
Some of the disadvantages of triggers in SQL are as follows:
- Increases the overhead costs of the server.
- Provides only extended validations i.e. not all validations are accessible in SQL triggers.
- Troubleshooting errors due to triggers is a tedious job.
- Can cause logical errors in the application even if a slight mistake in query exists.
- We could lose the original data if we set a wrong trigger by mistake.