Главная страница » Что такое триггеры в sql

Что такое триггеры в sql

  • автор:

All You Need to Know about Triggers in SQL

A trigger in SQL is a procedural code that is automatically executed in response to certain events on a specified table. It is important to understand how these small codes make such a huge difference in database performance. In this article, you will learn how to implement triggers along with examples.

The following topics will be covered in this article:

  • What is a Trigger?
  • Syntax and Example
  • Operation in Triggers
  • Advantage and Disadvantage

What is a Trigger?

Triggers are automatically executed in response to certain events on a particular table. These are used to maintain the integrity of the data. A trigger in SQL codes that are SQL works similar to a real-world trigger. For example, when the gun trigger is pulled a bullet is fired. We all know this, but how this is related to Triggers in SQL? To understand this let’s consider a hypothetical situation.

John is the marketing officer in a company. When a new customer data is entered into the company’s database he has to send the welcome message to each new customer. If it is one or two customers John can do it manually, but what if the count is more than a thousand? Well in such a scenario triggers come in handy.

Thus, now John can easily create a trigger that will automatically send a welcome email to the new customers once their data is entered into the database. So I hope you are clear with the introduction of Triggers in SQL.

Always remember that there cannot be two triggers with similar action time and event for one table. For example, we cannot have two BEFORE UPDATE triggers for a table. But we can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger.

Before we dive further into the fundamentals of triggers I would suggest you to understand the concepts of SQL Basics and Normalization so that you get a better grip on Triggers in SQL.

Syntax and Example

Lets now look at the syntax of a trigger.

Now let me break down this syntax and explain each and every part in detail.

  • Create Trigger
    These two keywords are used to specify that a trigger block is going to be declared.
  • Trigger_Name
    It specifies the name of the trigger. Trigger name has to be unique and shouldn’t repeat.
  • ( Before | AfterThis specifies when the trigger will be executed. It tells us the time at which the trigger is initiated, i.e, either before the ongoing event or after.
  • Before Triggers are used to update or validate record values before they’re saved to the database.
  • After Triggers are used to access field values that are set by the system and to effect changes in other records. The records that activate the after trigger are read-only. We cannot use the After trigger if we want to update a record because it will lead to a read-only error.
  • [ Insert | Update | Delete ]
    These are the DML operations and we can use either of them in a given trigger.
  • on [ Table_NameWe need to mention the table name on which the trigger is being applied. Don’t forget to use on keyword and also make sure the selected table is present in the database.
  • [ for each row | for each column ]
  1. Row-level trigger gets executed before or after any column value of a row changes
  2. Column Level Trigger gets executed before or after the specified column changes

So this was all about a simple trigger. But we can also create a nested trigger that can do multi-process. Also handling it and terminating it at the right time is very important. If we don’t end the trigger properly it may lead to an infinite loop.

You might be wondering in which scenario we can use the nested trigger. Rather than giving you a tailored answer let me share a scenario with you, which will help you in understanding the nested trigger in a better way. Continuing from the earlier scenario, John sent an email for every new customer that was added to the company’s database. Now, what if he wishes to keep track of the number of customers to whom the email was sent? Now John needs to create a nested trigger to keep the track of the count along with sending an email.

So that was all about the syntax of triggers, lets now try to implement an example of triggers in SQL.

Example for Trigger:

In the below trigger, we are trying to calculate the percentage of the student as soon as his details are updated to the database.

Here the “ NEW” keyword refers to the row that is getting affected.

Operations in Triggers

We can perform many operations using triggers. Some may be simple and some may be a little complex, but once if we go through the query its easy to understand.

The below code will display all the triggers that are present.

The below code will display all the triggers that are present in a particular database.

In the above example, all the triggers that are present in the database named Edureka will be displayed.

We also look at some major variants of the triggers that is Before insert and After insert. We have already seen a trigger in the example. But with the help of the table lets see how exactly this works.

As we have already understood how to create a trigger, now let’s understand the two variants of the trigger are Before inserted and After insert. in order to implement them, let’s create a student table with various columns as shown below:

Now if we execute this query we get the following table.

Let’s try to use the first variant i.e, Before Insert

Here when we insert data into the student table automatically the trigger will be invoked. The trigger will add 100 to the marks column into the student column.

Now let’s use the second variant i.e, After Insert

To use this variant we need one more table i.e, Percentage where the trigger will store the results. Use the below code to create the Percentage Table.

Now let us use the after insert trigger

That was all about the operation on triggers, lets now move ahead and look at its advantages and disadvantages.

Advantages and Disadvantages of Triggers

Advantages

  • Forcing security approvals on the table that are present in the database
  • Triggers provide another way to check the integrity of data
  • Counteracting invalid exchanges
  • Triggers handle errors from the database layer
  • Normally triggers can be useful for inspecting the data changes in tables
  • Triggers give an alternative way to run scheduled tasks. Using triggers, we don’t have to wait for the scheduled events to run because the triggers are invoked automatically before or after a change is made to the data in a table

Disadvantages

  • Triggers can only provide extended validations, i.e, not all kinds of validations. For simple validations, you can use the NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints
  • Triggers may increase the overhead of the database
  • Triggers can be difficult to troubleshoot because they execute automatically in the database, which may not invisible to the client applications

Here when we insert data to the table, total_mark trigger will store the result in the Final_mark table.

That was all about the operation on triggers. If you wish to check out more articles on the market’s most trending technologies like Artificial Intelligence, DevOps, Ethical Hacking, then you can refer to Edureka’s official site.

Do look out for other articles in this series that will explain the various other aspects of SQL.

Триггеры

Триггеры представляют специальный тип хранимой процедуры, которая вызывается автоматически при выполнении определенного действия над таблицей или представлением, в частности, при добавлении, изменении или удалении данных, то есть при выполнении команд INSERT, UPDATE, DELETE.

Формальное определение триггера:

Для создания триггера применяется выражение CREATE TRIGGER , после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.

Каждый триггер ассоциируется с определенной таблицей или представлением, имя которых указывается после слова ON .

Затем устанавливается тип триггера. Мы можем использовать один из двух типов:

AFTER : выполняется после выполнения действия. Определяется только для таблиц.

INSTEAD OF : выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений

После типа триггера идет указание операции, для которой определяется триггер: INSERT , UPDATE или DELETE .

Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.

И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.

Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:

Определим триггер, который будет срабатывать при добавлении и обновлении данных:

Допустим, в таблице Products хранятся данные о товарах. Но цена товара нередко содержит различные надбавки типа налога на добавленную стоимость, налога на добавленную коррупцию и так далее. Человек, добавляющий данные, может не знать все эти тонкости с налоговой базой, и он определяет чистую цену. С помощью триггера мы можем поправить цену товара на некоторую величину.

Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара, а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.

И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:

Триггеры в MS SQL Server

Удаление триггера

Для удаления триггера необходимо применить команду DROP TRIGGER :

Отключение триггера

Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :

А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :

Триггеры в MySQL

— Удаляем триггер
DROP TRIGGER `update_test`;
— Cоздадим еще одну таблицу,
— в которой будут храниться резервные копии строк из таблицы test
CREATE TABLE `testing`.` backup ` (
`id` INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`row_id` INT ( 11 ) UNSIGNED NOT NULL ,
`content` TEXT NOT NULL
) ENGINE = MYISAM
— триггеры
DELIMITER |
CREATE TRIGGER `update_test` before update ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD .id, content = OLD .content;
END ;

CREATE TRIGGER `delete_test` before delete ON `test`
FOR EACH ROW BEGIN
INSERT INTO backup Set row_id = OLD .id, content = OLD .content;
END * This source code was highlighted with Source Code Highlighter .

Теперь если мы отредактируем или удалим строку из test она скопируется в backup.

зы: надеюсь статья была интересной и полезной
UPD: для создания триггеров в версии до 5.1.6 требуются полномочия суперпользователя.

Trigger Warning! Setting up Database Tracking with Triggers in Postgres

SQL triggers are less common but can be a great solution for certain situations. I'll provide examples for how to use triggers to enforce data integrity and track changes to a database.

2 years ago • 9 min read

I was 12 years old when I first heard about SQL triggers. My brother, Jonathan, had just begun his software career at a startup. Jonathan came home one day frustrated by a database full of convoluted SQL triggers.

With my only programming experience being recreating my favorite video game in VB6, I had little consolation to offer.

Fast forward 16 years, and now I can see from my brother’s perspective. In the world of open source start-up style full-stack development (think Django, Rails, Javascript, PHP, MySQL, Postgres..), ORMs are very popular and features like SQL triggers are far less conventional.

But there is still value with SQL triggers. During my time working on custom ERP-like software, SQL triggers were an invaluable tool. When building highly data oriented software, especially when the data is of financial nature and accuracy is of high demand, you’re more likely to see data being manipulated at a lower level, in a more direct way.

This article contains all the information I wish I could have shared with my brother on how to effectively use SQL triggers.

Table of contents

What is a SQL Trigger?

SQL Triggers, also called Database Triggers, allow you to tell your SQL engine (for these examples, Postgres) to run a piece of code when an event happens, or even before the event.

In Postgres, you delineate the code to run by creating a function whose return type is trigger . In some other engines like MySQL, the code block is a part of and inside the trigger.

Before I discuss what the different event types are and the specific syntax for creating a trigger, why would you want to use a database trigger?

Advantages of using SQL Triggers

Maintaining data integrity

Database triggers have a variety of uses and are an excellent tool to marshal strict data integrity. Alternate solutions like Django’s model hooks may fail if you have other application servers or users accessing the database who aren’t aware of the specific business logic coded in your application.

Separating business logic

Coding critical business logic within the application code also presents problems when the business logic is updated. If you had a business requirement to multiply an incoming number by 10, and you wanted to revise this logic to multiply the number by 20, changing the logic in SQL would guarantee that every piece of data from that exact deploy time on would be affected by the new logic.

The SQL server acts as a single point of truth. If the logic is implemented on multiple application servers, you can no longer expect a clean, definitive change in behavior.

Atomic transactions

Natural atomicity is another desirable feature bundled with triggers. Since the event and the trigger function are all part of one atomic transaction, you know with absolute certainty that the trigger will fire if the event fires. They are as one, in perfect SQL matrimony.

How to Create a SQL Trigger — Postgres Syntax

Here are the components to creating a trigger for your database:

  1. Trigger Event Type
  2. Before or After the event
  3. Effect of the Trigger

Trigger event types

Database triggers will monitor tables for specific events. Here are some examples of different events that can activate a trigger:

  • Changing data: INSERT , UPDATE , DELETE

A database trigger can also list more than one of these events.

If UPDATE was one of the listed events, you can pass in a list of columns that should activate the trigger. If you don’t include this list, updating any column will activate it.

Trigger BEFORE or AFTER

A trigger can run either BEFORE or AFTER an event.

If you want to block an event like an INSERT , you will want to run BEFORE . If you need to be sure the event actually is going to occur, AFTER is ideal.

Effect of the trigger

A trigger can run either per row, or per statement. Let’s say you run a single UPDATE statement that changes 5 rows in a table.

If you specify FOR EACH ROW in the trigger, then the trigger will run 5 times. If you specified FOR EACH STATEMENT , then it would only run once.

And of course we can’t forget the actual code to run when the trigger is activated. In Postgres, is placed in a function and separated from the trigger. Separating the trigger from the code it runs creates cleaner code and allows multiple triggers to execute the same code.

Postgres Trigger Example #1: Creating a Time Clock

A time clock records when an employee comes and leaves from work and calculates his/her total hours worked. Let’s create an example time clock and look at how we can use triggers to prevent employees from inputting invalid data.

Setting up the DB schema

The design of this schema treats each punch in and out as separate events. Each event is a row in the time_punch table. Alternatively, you could also make each employee» shift» an event and store both the punch in and punch out time in one row.

Subscribe to our newsletter to get notified of a future post where I’ll do a deep dive on how to define a strong database schema.

For this example I’ve gone ahead and defined the schema for our tables. The code below creates an employee and time_punch table and inserts some time punch data for a new employee, Bear.

Bear has clocked in at 10:00am and out at 11:30am (long day at work). Let’s write a SQL query to calculate how long Bear has worked.

Take a pause here and think about how you would solve this given our schema and using only SQL.

Using SQL to calculate time worked

The solution I decided on looks at each «out» punch and matches it with its preceding «in» punch.

In this query I select all the out punches, then I join them to the closest preceding «in» punch. Subtract the timestamps, and we get how many hours Bear worked for each shift!

One of the issues with this schema is that it is possible for you to insert several «in» or «out» punches in a row. With the query we’ve created, this would introduce ambiguities that could lead to inaccurate calculations and employees getting paid more or less than they should.

SQL INSERT BEFORE trigger example — preserving data integrity

We need something to prevent the in/out pattern from being interrupted. Unfortunately check constraints only look at the row being inserted or updated and cannot factor in data from other rows.

This is a perfect situation to use a database trigger!

Let’s create a trigger to prevent an INSERT event that would break our pattern. First we’ll create the «trigger function». This function is what the trigger will execute when the event type is detected.

A trigger function is created like a regular Postgres function, except that it returns a trigger .

The new keyword represents the values of the row that is to be inserted. It also is the object you can return to allow the insert to continue. Alternatively, when null is returned this stops the insertion.

This query finds the time_punch before and ensures its in/out value is not the same as what’s being inserted. If the values are the same, then the trigger returns null and the time_punch is not recorded. Otherwise, the trigger returns new and the insert statement is allowed to continue.

Now we’ll link the function as a trigger to the time_punch table. BEFORE is crucial here. If we ran this trigger as an AFTER trigger it would run too late to stop the insertion.

Let’s try to insert another «out» punch:

We can see from the output that the trigger prevented the insertion of two subsequent out punches for the same employee.

It is also possible to raise an exception from the trigger so that your application (or person running the SQL query) receives a failure notice instead of the insert count simply being 0.

Postgres Trigger Example #2: Creating an Audit Table

Accurately storing employee punch data is critical for businesses. This type of data often ends up directly translating to an employee’s salary, and on the other end, a company’s payroll cost.

Because of the importance of this data, let’s say the company wants to be able to recreate all the historical states of the table in the event that an irregularity is discovered.

An audit table accomplishes this by keeping track of every change to a table. When a row is updated on the main table, a row will be inserted into the audit table recording its past state.

I will use our time_punch table to demonstrate how to create an automatically updating audit table using triggers.

Create the audit table

There are several ways to keep an audit or history table. Let’s create a separate table that will store the past states of time_punch.

This table stores:

  • Time the punch was updated
  • Employee who updated it
  • ID of the punch that was changed
  • Punch time before the punch was updated

Before we create our trigger, we first need to add a change_employee_id column to our time_punch table. This way the trigger will know which employee made every change to the time_punch table.

(An alternative solution without adding any columns to time_punch could be to revoke update permission on this table, and force users of this database to use a custom function like update_time_punch(id, change_user_id, . ) )

SQL UPDATE AFTER trigger example — inserting data

After an update happens to our time_punch table , this trigger runs and stores the OLD punch time value in our audit table.

The NOW() function returns the current date and time from the SQL server’s perspective. If this were linked up to an actual application, you’d want to consider passing in the exact time the user actually made the request to avoid discrepancies from latency.

During an update trigger, the NEW object represents what values the row will contain if the update is successful. You can use a trigger to «intercept» an insert or update by simply assigning your own values to the NEW object. The OLD object contains the row’s values pre-update.

Let’s see if it works! I have inserted a second user named Daniel who will be the editor of Bear’s time punches.

I’m going to run the query below twice to simulate 2 edits that increase the time by 5 minutes.

And here is the audit table, reflecting the past punch times:

Additional Considerations for Triggers

There’s a few things to be wary of with database triggers:

  1. Maintaining triggers over time
  2. Connected trigger logic
  3. Developer expertise
Maintaining triggers over time

Business logic in application code is naturally documented as it changes over time by way of git or another source control system. It’s easy for a developer to see some logic in a codebase and do a quick git log and see a list of changes.

Managing changes over time with SQL triggers and functions is more complicated, less standardized, and requires more thought and planning.

Connected trigger logic

Triggers can also set off other triggers, quickly complicating the results of what could appear to be an innocent INSERT or UPDATE . This risk is also inherent in application code with side effects.

Developer expertise

Awareness of triggers is also far lower among some developer circles so introducing them increases the investment in training that will be required for new developers to successfully work on your project.

SQL can initially be a clumsy and frustrating language to learn as many of the patterns you learn to build a query are «inside out» from how you’d extract data in a procedural language.

Using Triggers to Level Up Your SQL Game

I hope that these examples have helped you develop a better understanding of database triggers. I have had great successes using triggers for data problems where traceability, consistency, and accuracy are paramount.

Making the decision to introduce triggers to your application is one that should be made after careful thought — and I for one hope you get an opportunity to implement and explore one of SQL’s most fun and intriguing features!

If you’re interested in continuing to improve your SQL skills, consider checking out Arctype. Arctype is a modern SQL editor designed to simplify the process of working with databases and SQL. Join our growing community and download Arctype today.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *