Как создать триггер на transact-sql

Example of Trigger in SQL Server

Let us understand how we can work with triggers in the SQL Server. We can do this by first creating a table named ‘Employee’ using the below statements:

CREATE TABLE Employee
(
Id INT PRIMARY KEY,
Name VARCHAR(45),
Salary INT,
Gender VARCHAR(12),
DepartmentId INT
)

Next, we will insert some record into this table as follows:

INSERT INTO Employee VALUES (1,’Steffan’, 82000, ‘Male’, 3),
(2,’Amelie’, 52000, ‘Female’, 2),
(3,’Antonio’, 25000, ‘male’, 1),
(4,’Marco’, 47000, ‘Male’, 2),
(5,’Eliana’, 46000, ‘Female’, 3)

We can verify the insert operation by using the SELECT statement. We will get the below output:

SELECT * FROM Employee;

We will also create another table named ‘Employee_Audit_Test’ to automatically store transaction records of each operation, such as INSERT, UPDATE, or DELETE on the Employee table:

CREATE TABLE Employee_Audit_Test
(
Id int IDENTITY,
Audit_Action text
)

Now, we will create a trigger that stores transaction records of each insert operation on the Employee table into the Employee_Audit_Test table. Here we are going to create the insert trigger using the below statement:

CREATE TRIGGER trInsertEmployee
ON Employee
FOR INSERT
AS
BEGIN
Declare @Id int
SELECT @Id = Id from inserted
INSERT INTO Employee_Audit_Test
VALUES (‘New employee with Id = ‘ + CAST(@Id AS VARCHAR(10)) + ‘ is added at ‘ + CAST(Getdate() AS VARCHAR(22)))
END

After creating a trigger, we will try to add the following record into the table:

INSERT INTO Employee VALUES (6,’Peter’, 62000, ‘Male’, 3)

If no error is found, execute the SELECT statement to check the audit records. We will get the output as follows:

We are going to create another trigger to store transaction records of each delete operation on the Employee table into the Employee_Audit_Test table. We can create the delete trigger using the below statement:

CREATE TRIGGER trDeleteEmployee
ON Employee
FOR DELETE
AS
BEGIN
Declare @Id int
SELECT @Id = Id from deleted
INSERT INTO Employee_Audit_Test
VALUES (‘An existing employee with Id = ‘ + CAST(@Id AS VARCHAR(10)) + ‘ is deleted at ‘ + CAST(Getdate() AS VARCHAR(22)))
END

After creating a trigger, we will delete a record from the Employee table:

DELETE FROM Employee WHERE Id = 2;

If no error is found, it gives the message as below:

Finally, execute the SELECT statement to check the audit records:

In both the triggers code, you will notice these lines:

SELECT @Id = Id from inserted
SELECT @Id = Id from deleted

Here inserted and deleted are special tables used by the SQL Server. The inserted table keeps the copy of the row when you insert a new row into the actual table. And the deleted table keeps the copy of the row you have just deleted from the actual table.

SQL Server audit logging triggers

To capture the INSERT, UPDATE, and DELETE DML statements, we need to create three database triggers that are going to insert records in the table.

SQL Server AFTER INSERT audit logging trigger

To intercept the INSERT statements on the table, we will create the trigger:

CREATE TRIGGER TR_Book_Insert_AuditLog ON Book
FOR INSERT AS 
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = CAST(SESSION_CONTEXT(N'loggedUser') AS varchar(255))
    
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
    
    INSERT INTO BookAuditLog (
        BookId,
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Inserted),
        null,
        (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        'INSERT',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

The trigger is executed every time a record is inserted in the table.

The virtual table references the record values that just got inserted, so we can use it to extract the column values.

Only the column is set since this is a new record that got inserted, so it doesn’t have a previous state to be saved in column.

The SQL Server feature allows us to create a JSON object from a query result set.

The column is set to the value of , and the value is set to the .

The column is set to the value of the SQL Server session variable, that’s set by the application with the currently logged user:

Session session = entityManager.unwrap(Session.class);

Dialect dialect = session.getSessionFactory()
    .unwrap(SessionFactoryImplementor.class)
    .getJdbcServices().getDialect();
    
String loggedUser = ReflectionUtils.invokeMethod(
    dialect,
    "escapeLiteral",
    LoggedUser.get()
);

session.doWork(connection -> {
    update(
        connection,
        String.format(
            "EXEC sys.sp_set_session_context @key = N'loggedUser', @value = N'%s'", 
            loggedUser
        )
    );
});

SQL Server AFTER UPDATE audit logging trigger

To capture the UPDATE statements on the records, we will create the following trigger:

CREATE TRIGGER TR_Book_Update_AuditLog ON Book
FOR UPDATE AS
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = cast(SESSION_CONTEXT(N'loggedUser') as varchar(255))
    
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
    
    DECLARE @oldRecord nvarchar(1000)
    DECLARE @newRecord nvarchar(1000)
    
    SET @oldRecord = (SELECT * FROM Deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    SET @newRecord = (SELECT * FROM Inserted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)
    
    IF @oldRecord != @newRecord
        INSERT INTO BookAuditLog (
            BookId,
            OldRowData,
            NewRowData,
            DmlType,
            DmlTimestamp,
            DmlCreatedBy,
            TrxTimestamp
        )
        VALUES(
            (SELECT id FROM Inserted),
            @oldRecord,
            @newRecord,
            'UPDATE',
            CURRENT_TIMESTAMP,
            @loggedUser,
            @transactionTimestamp
        );
END

Every time a record is updated, the trigger is executed, and a row will be created to capture both the old and the new state of the modifying record.

SQL Server AFTER DELETE audit logging trigger

To intercept the DELETE statements on the table rows, we will create the following trigger:

CREATE TRIGGER TR_Book_Delete_AuditLog ON Book
FOR DELETE AS 
BEGIN
    DECLARE @loggedUser varchar(255)
    SELECT @loggedUser = CAST(SESSION_CONTEXT(N'loggedUser') AS varchar(255))
    
    DECLARE @transactionTimestamp datetime = SYSUTCdatetime()
    
    INSERT INTO BookAuditLog (
        BookId,
        OldRowData,
        NewRowData,
        DmlType,
        DmlTimestamp,
        DmlCreatedBy,
        TrxTimestamp
    )
    VALUES(
        (SELECT id FROM Deleted),
        (SELECT * FROM Deleted FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
        null,
        'DELETE',
        CURRENT_TIMESTAMP,
        @loggedUser,
        @transactionTimestamp
    );
END

As you can see, only the column is set since there is no new record state.

Триггеры DDL и области их применения

Ранее мы рассмотрели триггеры DML, которые задают действие, предпринимаемое сервером при изменении таблицы инструкциями INSERT, UPDATE или DELETE. Компонент Database Engine также позволяет определять триггеры для инструкций DDL, таких как CREATE DATABASE, DROP TABLE и ALTER TABLE. Триггеры для инструкций DDL имеют следующий синтаксис:

Соглашения по синтаксису

Как можно видеть по их синтаксису, триггеры DDL создаются таким же способом, как и триггеры DML. А для изменения и удаления этих триггеров используются те же инструкции ALTER TRIGGER и DROP TRIGGER, что и для триггеров DML. Поэтому в этом разделе рассматриваются только те параметры инструкции CREATE TRIGGER, которые новые для синтаксиса триггеров DDL.

Первым делом при определении триггера DDL нужно указать его область действия. Предложение DATABASE указывает в качестве области действия триггера DDL текущую базу данных, а предложение ALL SERVER — текущий сервер.

После указания области действия триггера DDL нужно в ответ на выполнение одной или нескольких инструкций DDL указать способ запуска триггера. В параметре event_type указывается инструкция DDL, выполнение которой запускает триггер, а в альтернативном параметре event_group указывается группа событий языка Transact-SQL. Триггер DDL запускается после выполнения любого события языка Transact-SQL, указанного в параметре event_group. Ключевое слово LOGON указывает триггер входа.

Кроме сходства триггеров DML и DDL, между ними также есть несколько различий. Основным различием между этими двумя видами триггеров является то, что для триггера DDL можно задать в качестве его области действия всю базу данных или даже весь сервер, а не всего лишь отдельный объект. Кроме этого, триггеры DDL не поддерживают триггеров INSTEAD OF. Как вы, возможно, уже догадались, для триггеров DDL не требуются таблицы inserted и deleted, поскольку эти триггеры не изменяют содержимого таблиц.

В следующих подразделах подробно рассматриваются две формы триггеров DDL: триггеры уровня базы данных и триггеры уровня сервера.

Триггеры DDL уровня базы данных

В примере ниже показано, как можно реализовать триггер DDL, чья область действия распространяется на текущую базу данных:

Триггер в этом примере предотвращает удаление любого триггера для базы данных SampleDb любым пользователем. Предложение DATABASE указывает, что триггер trigger_PreventDrop является триггером уровня базы данных. Ключевое слово DROP_TRIGGER указывает предопределенный тип события, запрещающий удаление любого триггера.

Триггеры DDL уровня сервера

Триггеры уровня сервера реагируют на серверные события. Триггер уровня сервера создается посредством использования предложения ALL SERVER в инструкции CREATE TRIGGER. В зависимости от выполняемого триггером действия, существует два разных типа триггеров уровня сервера: обычные триггеры DDL и триггеры входа. Запуск обычных триггеров DDL основан на событиях инструкций DDL, а запуск триггеров входа — на событиях входа.

В примере ниже демонстрируется создание триггера уровня сервера, который является триггером входа:

Здесь сначала создается имя входа SQL Server loginTest, которое потом используется в триггере уровня сервера. По этой причине, для этого имени входа требуется разрешение VIEW SERVER STATE, которое и предоставляется ему посредством инструкции GRANT. После этого создается триггер trigger_ConnectionLimit. Этот триггер является триггером входа, что указывается ключевым словом LOGON.

С помощью представления sys.dm_exec_sessions выполняется проверка, был ли уже установлен сеанс с использованием имени входа loginTest. Если сеанс уже был установлен, выполняется инструкция ROLLBACK. Таким образом имя входа loginTest может одновременно установить только один сеанс.

Remarks Remarks

Функция UPDATE() возвращает TRUE независимо от того, была ли попытка применить операторы INSERT или UPDATE удачной. UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.

Чтобы проверить действие операторов INSERT или UPDATE для нескольких столбцов, укажите отдельно предложение UPDATE(column), следующее за первым предложением. To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. Несколько столбцов также могут быть проверены на действие INSERT или UPDATE при помощи COLUMNS_UPDATED. Multiple columns can also be tested for INSERT or UPDATE actions by using COLUMNS_UPDATED. В результате возвращается битовый шаблон, который указывает на то, какие столбцы были вставлены или обновлены. This returns a bit pattern that indicates which columns were inserted or updated.

IF UPDATE возвращает значение TRUE по действиям оператора INSERT, так как столбцы содержат либо явные вставленные значения, либо неявные вставленные значения (NULL). IF UPDATE returns the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.

Функции предложения IF UPDATE(column) аналогичны предложениям IF, IF. ELSE или WHILE и могут использовать блок BEGIN. END. The IF UPDATE(column) clause functions the same as an IF, IF. ELSE, or WHILE clause and can use the BEGIN. END block. Дополнительные сведения см. в разделе Язык управления потоком (Transact-SQL). For more information, see Control-of-Flow Language (Transact-SQL).

UPDATE(column) может применяться в любой части текста триггера Transact-SQL Transact-SQL . UPDATE(column) can be used anywhere ins >Transact-SQL Transact-SQL trigger.

Если триггер применяется к столбцу, значение UPDATED будет возвращаться в виде true или 1 , даже если значение столбца остается неизменным. If a trigger applies to a column, the UPDATED value will return as true or 1 , even if the column value remains unchanged. Это нормальное поведение, и триггер должен реализовывать бизнес-логику, которая определяет, допустимы ли операции вставки, обновления и удаления. This is by-design, and the trigger should implement business logic that determines if the insert/update/delete operation is permissible or not.

3.4.5. Как работают триггеры?

В данной главе мы более глубоко рассмотрим, как работают различные типы триггеров. Для этого мы напишем множество примеров, максимально приближенных к реальности, а заодно получим хорошую практику программирование на языке Transact-SQL и создания триггеров.

Триггер INSERT

Что происходит, когда срабатывает триггер добавления записей? Давайте рассмотрим выполняемые сервером шаги:

• Пользователем выполняется оператор INSERT для добавления записей;
• Сервер сохраняет информацию о запросе в журнале транзакций;
• Вызывается триггер;
• Подтверждение изменений и физическое изменение данных.

Во время вызова триггера, физического изменения в базе еще не произошло. В теле триггера вы можете увидеть добавляемые записи в виде таблицы inserted. Нет, такой таблицы в базе данных не существует, inserted – это логическая таблица, которая содержит копию строк, которые должны быть вставлены в таблицу. Если быть точнее, она содержит журнал активности оператора INSERT. Вы можете использовать данные из этой таблицы для определения вставляемых данных. Строки из таблицы inserted всегда дублируют одну или несколько строк таблицы триггера.

Вся активность по изменению данных записываются в журнал, но информация в журнале транзакций не читаема. Однако таблица inserted позволяет вам ссылаться и определить изменения.

Таблица inserted всегда содержит такую же структуру, что и у таблицы, на которую установлен триггер.

Давайте запретим с помощью триггера добавление записей, в которых имя работника равно Вася. Пример такого триггера можно увидеть в листинге 3.4.

Листинг 3.4. Использование таблицы inserted

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples
FOR INSERT 
AS
 DECLARE @Name varchar(50)
 SELECT @Name=vcName
 FROM inserted
 IF @Name='ВАСЯ'
  BEGIN 
   PRINT 'ОШИБКА'
   ROLLBACK TRANSACTION
  END

В данном примере мы создаем триггер на добавление записей. Внутри триггера мы объявляем переменную @Name типа varchar длиной в 50 символов. В эту переменную мы сохраняем содержимое поля «vcName» таблицы inserted. Далее проверяем, если имя равно Вася, то сообщаем об ошибке и откатываем транзакцию. Иначе, строка будет удачно добавлена.

Давайте для закрепления материала, напишем триггер, который запретит нулевые значения для поля «vcName». Код такого триггера можно увидеть в листинге 3.5.

Листинг 3.5. Запрет нулевых значений в поле с помощью триггера

CREATE TRIGGER i_tbPeoples ON dbo.tbPeoples
FOR INSERT 
AS
 IF EXISTS (SELECT *
            FROM inserted
            WHERE vcName is NULL)
  BEGIN 
   PRINT 'ОШИБКА, вы должны заполнить поле vcName'
   ROLLBACK TRANSACTION
  END

В этом примере мы проверяем, если в таблице inserted есть записи с нулевым значением поля «vcName», то откатываем попытку добавления.

Шаг 3 — Создание триггера After Insert

Триггеры выполняются после успешной вставки записей в таблицу. Эта функция может использоваться для автоматического запуска других бизнес-логик. Например, в банковских приложениях триггер может закрывать кредитный счет, когда клиент завершает выплату кредита. Триггер может отслеживать все платежи, внесенные в таблицу транзакций, и автоматически закрывать кредит, как только кредитный баланс будет равен нулю.

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

Для создания триггера введите следующие команды:

Таким образом вы инструктируете MySQL сохранить еще одну запись в таблицу , как только происходит вставка новой клиентской записи в таблицу .

Теперь вставьте новую запись в таблицу , чтобы убедиться, что код триггера вызывается:

После успешной вставки записи убедитесь, что запись нового статуса была добавлена в таблицу :

Вывод подтверждает успешную работу триггера.

Триггер полезен для мониторинга жизненного цикла клиента. В производственной среде учетные записи клиентов могут проходить различные этапы, например открытие, приостановка и закрытие счета.

На следующем этапе вы будете работать с триггерами .

Another Example of SQL Trigger

If we want to start with it, we will use the CUSTOMERS table. See the following customer’s table with its columns and values.

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+ 

The following program creates the row-level trigger for the customer’s table that would fire for the INSERT or UPDATE or DELETE operations performed on the CUSTOMERS. The trigger will display the salary difference between old values and new values.

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > ) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := NEW.salary  - OLD.salary; 
   dbms_output.put_line('Old salary: ' || OLD.salary); 
   dbms_output.put_line('New salary: ' || NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 

When the above code is executed at the SQL prompt, it produces the following output.

Trigger created.

The following points need to be considered here.

1. OLD and NEW references are unavailable for the table-level triggers; instead, you can use them for the record-level triggers.

2. If you want to query a table in the same trigger, you should use an AFTER keyword because the triggers can query a table or change it again only after an initial change is applied and the table is back in a consistent state.

3. The above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on a table, but you can write your trigger on single or multiple operations. For example, BEFORE DELETE will fire whenever a record is deleted using the DELETE operation on the table.

Простой триггер SQL Server DML

CREATE TABLE Employees
    (
      EmployeeID integer NOT NULL IDENTITY(1, 1) ,
      EmployeeName VARCHAR(50) ,
      EmployeeAddress VARCHAR(50) ,
      MonthSalary NUMERIC(10, 2)
      PRIMARY KEY CLUSTERED (EmployeeID)
    )
GO

CREATE TABLE EmployeesAudit
    (
      AuditID INTEGER NOT NULL IDENTITY(1, 1) ,
      EmployeeID INTEGER ,
      EmployeeName VARCHAR(50) ,
      EmployeeAddress VARCHAR(50) ,
      MonthSalary NUMERIC(10, 2) ,
      ModifiedBy VARCHAR(128) ,
      ModifiedDate DATETIME ,
      Operation CHAR(1) 
      PRIMARY KEY CLUSTERED ( AuditID )
    )
GO

INSERT INTO dbo.Employees
        ( EmployeeName ,
          EmployeeAddress ,
          MonthSalary
        )
SELECT 'Mark Smith', 'Ocean Dr 1234', 10000
UNION ALL
SELECT 'Joe Wright', 'Evergreen 1234', 10000
UNION ALL
SELECT 'John Doe', 'International Dr 1234', 10000
UNION ALL
SELECT 'Peter Rodriguez', '74 Street 1234', 10000
GO

CREATE TRIGGER TR_Audit_Employees ON dbo.Employees
    FOR INSERT, UPDATE, DELETE
AS
    DECLARE @login_name VARCHAR(128) 
    SELECT  @login_name = login_name
    FROM    sys.dm_exec_sessions
    WHERE   session_id = @@SPID
    IF EXISTS ( SELECT 0 FROM Deleted )
        BEGIN
            IF EXISTS ( SELECT 0 FROM Inserted )
                BEGIN
                    INSERT  INTO dbo.EmployeesAudit
                            ( EmployeeID ,
                              EmployeeName ,
                              EmployeeAddress ,
                              MonthSalary ,
                              ModifiedBy ,
                              ModifiedDate ,
                              Operation
                            )
                            SELECT  D.EmployeeID ,
                                    D.EmployeeName ,
                                    D.EmployeeAddress ,
                                    D.MonthSalary ,
                                    @login_name ,
                                    GETDATE() ,
                                    'U'
                            FROM    Deleted D
                END
            ELSE
                BEGIN
                    INSERT  INTO dbo.EmployeesAudit
                            ( EmployeeID ,
                              EmployeeName ,
                              EmployeeAddress ,
                              MonthSalary ,
                              ModifiedBy ,
                              ModifiedDate ,
                              Operation
                            )
                            SELECT  D.EmployeeID ,
                                    D.EmployeeName ,
                                    D.EmployeeAddress ,
                                    D.MonthSalary ,
                                    @login_name ,
                                    GETDATE() ,
                                    'D'
                            FROM    Deleted D
                END  
        END
    ELSE
        BEGIN
            INSERT  INTO dbo.EmployeesAudit
                    ( EmployeeID ,
                      EmployeeName ,
                      EmployeeAddress ,
                      MonthSalary ,
                      ModifiedBy ,
                      ModifiedDate ,
                      Operation
                    )
                    SELECT  I.EmployeeID ,
                            I.EmployeeName ,
                            I.EmployeeAddress ,
                            I.MonthSalary ,
                            @login_name ,
                            GETDATE() ,
                            'I'
                    FROM    Inserted I
        END
GO

BEGIN TRANSACTION
SELECT  *
FROM    dbo.Employees
WHERE   EmployeeID = 1
UPDATE  Employees
SET     EmployeeName = 'zzz'
WHERE   EmployeeID = 1 
SELECT  *
FROM    dbo.Employees
WHERE   EmployeeID = 1
SELECT  *
FROM    dbo.EmployeesAudit
ROLLBACK TRANSACTION			

BEGIN TRANSACTION
INSERT  INTO dbo.Employees
        ( EmployeeName ,
          EmployeeAddress ,
          MonthSalary
        )
        SELECT  'zz' ,
                'dsda' ,
                10000
        UNION ALL
        SELECT  'Markus Rubius' ,
                'dsda' ,
                6000
SELECT  *
FROM    dbo.Employees
SELECT  *
FROM    dbo.EmployeesAudit
ROLLBACK TRANSACTION

BEGIN TRANSACTION
SELECT  *
FROM    dbo.Employees
WHERE   EmployeeID = 1
DELETE  FROM dbo.Employees
WHERE   EmployeeID = 1
SELECT  *
FROM    dbo.EmployeesAudit
SELECT  *
FROM    dbo.Employees
WHERE   EmployeeID = 1
ROLLBACK TRANSACTION			

Начало: структура таблиц, инструменты и заметки

В статье мы будем работать с выдуманной системой для корзины покупок, каждый элемент которой будет иметь цену. Структура данных будет проста, насколько это возможно с целью продемонстрировать процедуры работы с триггерами. Наименования таблиц и столбцов придуманы с целью облегчения понимания, а не для реальной работы. Также используется TIMESTAMPS для облегчения учебного процесса. Таблицы имеют имена carts, cart_items, cart_log, items, items_cost.

Также будут использоваться очень простые запросы. Нет связи между переменными и не используется никакого ввода данных. Запросы подготавливались так, чтобы быть как можно более простыми и понятными для чтения.

Для определения времени выполнения использовался  Particle Tree PHP Quick Profiler. Для иллюстрации эффектов на базе данных использовался Chive. Chive предназначен только для MySQL 5+ и очень похож на PHPMyAdmin. Он имеет более выразительный интерфейс, но содержит значительно больше ошибок на текущий момент. Использование Chive обусловлено желанием представить более выразительные скрин шоты запросов.

Вам также может понадобиться поменять разделитель MySQL при создании триггеров. Оригинальный разделитель MySQL — это  ; , но так как мы будем использовать разделитель для добавленных запросов, то может понадобиться явно указать разделитель, чтобы создавать запросы из командной линии. При использование Chive нет необходимости менять разделитель.

Чтобы изменить разделитель, нужно выполнить команду перед командой триггера:

А после команды триггера надо ввести:

Преимущества триггеров SQL

1) Они помогают поддерживать ограничения целостности в таблицах базы данных, особенно когда не определены ограничения первичного ключа и внешнего ключа.

2) Иногда они помогают сохранить короткие и простые коды SQL, как показано на примере из реальной жизни.

3) Помогают поддерживать отслеживание всех изменений (обновление, удаление и вставка), происходящих в таблицах, путем вставки значений изменений в таблицы аудита.

4) Иногда, если код плохо управляется, он может помочь в поддержании ограничений базы данных, определенных для таблиц, для которых определен триггер. Например, предположим, что есть ситуация, когда существует онлайновая система обучения, в которой пользователь может зарегистрироваться в нескольких курсах.

Предположим, что организация хочет определить ограничение: определено, что пользователь не может быть удален до тех пор, пока он или она не пройдет весь курс, в котором он зарегистрирован, или пользователь не должен сначала самостоятельно пройти из всех незавершенных или неудачных курсов.

Поскольку код плохо управляется, а код для удаления пользователя определяется как встроенный запрос на многих страницах .net и в нескольких хранимых процедурах (это не очень хорошая вещь, но бывает), нужно написать код для применение этого ограничения ко всем этим .net-файлам и хранимым процедурам, которые занимают так много времени, и если новый разработчик не выполняет это ограничение, он забывает включить код принудительного применения, который повреждает базу данных. В этом случае мы можем определить триггер вместо таблицы, который проверяет каждый раз, когда пользователь удаляется, и, если условие вышеуказанного ограничения не выполняется, вместо удаления пользователя отображается сообщение об ошибке.

SQL Server trigger after insert send email

• For example, there is a Student table.
• You want to send an email from SQL Server whenever a new student record is inserted.
• You can create the trigger on the Student table as:

• The sp_send_dbmail is a built in stored procedure in the msdb database that we use to send mails. We specify various options as the input parameters in this stored procedure.
  • @profile_name: This is the name of the mail profile that you will use to send the mail. A profile contains the mail server information.
  • @recipients: This is the recipients email address i.e. to whom you want to send the mail.
  • @subject: The subject of the mail to be sent.
  • @body: The body of the mail to be sent.
• In the above example, we are notifying a Database Administrator that a new student record is added.

See Also

ALTER TABLE (Transact-SQL)ALTER TRIGGER (Transact-SQL)COLUMNS_UPDATED (Transact-SQL)CREATE TABLE (Transact-SQL)DROP TRIGGER (Transact-SQL)ENABLE TRIGGER (Transact-SQL)DISABLE TRIGGER (Transact-SQL)TRIGGER_NESTLEVEL (Transact-SQL)EVENTDATA (Transact-SQL)sys.dm_sql_referenced_entities (Transact-SQL)sys.dm_sql_referencing_entities (Transact-SQL)sys.sql_expression_dependencies (Transact-SQL)sp_help (Transact-SQL)sp_helptrigger (Transact-SQL)sp_helptext (Transact-SQL)sp_rename (Transact-SQL)sp_settriggerorder (Transact-SQL)UPDATE() (Transact-SQL)Get Information About DML TriggersGet Information About DDL Triggerssys.triggers (Transact-SQL)sys.trigger_events (Transact-SQL)sys.sql_modules (Transact-SQL)sys.assembly_modules (Transact-SQL)sys.server_triggers (Transact-SQL)sys.server_trigger_events (Transact-SQL)sys.server_sql_modules (Transact-SQL)sys.server_assembly_modules (Transact-SQL)

3.4.7. Дополнительно о триггерах

Вы можете использовать триггеры для обеспечения комплексной целостности ссылок с помощью:

• Выполнения действий или каскадного обновления или удаления. Целостность ссылок может отличаться при использовании ограничений FOREIGN KEY и REFERENCE в операторе CREATE TABLE. Но триггер выгоден для гарантирования необходимых действий, когда должны быть произведены каскадные удаления или обновления, потому что триггеры более мощные. Если ограничение существует для таблицы с триггером, оно проверяется до выполнения триггера. Если ограничение нарушено, то триггер не работает. Если ограничение не сработает, то с помощью триггера можно реализовать более сложные проверки, которые уж точно будут гарантировать, что данные не нарушат целостность и пользователь внесет только те данные, которые разрешены;
• Вы должны учитывать, что в таблицу может вставляться сразу несколько строк. Вы должны учитывать это при написании триггеров, как мы это делали при создании примеров с использованием INSTEAD OF;
• Ограничения, правила и значения по умолчанию могут генерировать только стандартные системные ошибки. Если вам нужны собственные сообщения, вы должны использовать триггеры.

При разработке триггеров, вы должны учитывать, что таблицы могут иметь несколько триггеров для любого действия. Каждый триггер может быть объявлен для нескольких или одного действия. Например, в следующем примере обрабатывается два события INSERT и UPDATE:

CREATE TRIGGER iu_tbPeoples ON dbo.tbPeoples
FOR INSERT, UPDATE
AS
 Действие

Если на одно действие назначено несколько триггеров, чтобы не конфликтовали имена можно к имени добавить слово, которое будет описывать выполняемые действия или назначение.

Владелец таблицы может указывать первый и последний триггеры. Когда несколько триггеров помещены на таблицу, владелец может использовать процедуру sp_settriggerorder (о хранимых системных таблицах мы будем говорить в следующей главе) для указания первого выполняемого триггера и последнего. Порядок остальных триггеров не может устанавливаться.

Владельцы таблицы не могут создавать триггеры на просмотрщики и временные таблицы. Однако триггеры могут ссылаться на просмотрщики и временные таблицы.
Триггеры не должны возвращать результирующих наборов, хотя не запрещается что-то выводить на печать с помощью оператора PRINT, но вы должны отдавать себе отчет, что пользователь увидит это только при откате транзакции. Таким образом, можно сообщить только об ошибке, но не об удачном выполнении, хотя, в большинстве случаем этого нам достаточно.

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

• расположены на сервере и не требуют для своего выполнения сетевых обращений, если только в самом коде триггера нет обращений по сети;
• таблицы Insert и Deleted расположены в кэше, поэтому обращение к ним происходит достаточно быстро, если только они не содержат множества строк и обращения к таблицам не содержат сложных связей с другими таблицами.

Используйте триггеры только там, где это необходимо. Старайтесь возложить основные операции по обеспечению целостности на ограничения. Если нельзя найти другого выхода, то для повышения производительности сервера делайте объявление операторов триггеров простыми, на сколько это возможно. Так как триггер является частью транзакции, блокировки сохраняются, пока транзакция не завершится, поэтому здесь скорость обработки наиболее важна.