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:

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

  2. 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.
  3. 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:

  1. Allows the reuse of code.
  2. Helps us increase the computational costs by reducing result times.

Syntax:

CREATE TRIGGER triggerName
BEFORE/AFTER 
INSERT/UPDATE/DELETE
ON tableName
FOR EACH ROW SET operation;

We have six different variations of triggers.

  1. BEFORE INSERT:

    In these triggers, the operation on rows is under execution before any INSERT operation on the database.

  2. AFTER INSERT:

    In these triggers, the operation on rows is under execution after any INSERT operation on the database.

  3. BEFORE UPDATE:

    In these triggers, the operation on rows is under execution before an UPDATE operation on the database.

  4. AFTER UPDATE:

    In these triggers, the operation on rows is under execution after an UPDATE operation on the database.

  5. BEFORE DELETE:

    In these triggers, the operation on rows is under execution before any DELETE operation is run on the database.

  6. AFTER DELETE:

    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.

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.

Query:

Use IBM;

SELECT * FROM IBM;

Syntax:

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:

  1. Helps us to automate the data alterations.
  2. Allows us to reuse the queries once written.
  3. Provides a method to check the data integrity of the database.
  4. Helps us to detect errors on the database level.
  5. Allows easy auditing of data.

Disadvantages of Triggers

Some of the disadvantages of triggers in SQL are as follows:

  1. Increases the overhead costs of the server.
  2. Provides only extended validations i.e. not all validations are accessible in SQL triggers.
  3. Troubleshooting errors due to triggers is a tedious job.
  4. Can cause logical errors in the application even if a slight mistake in query exists.
  5. We could lose the original data if we set a wrong trigger by mistake.


Copyright 1999- Ducat Creative, All rights reserved.

Anda bisa mendapatkan server slot online resmi dan terpercaya tentu saja di sini. Sebagai salah satu provider yang menyediakan banyak pilihan permainan.