Examples of triggers in ms sql server. Trigger (database). DDL triggers and their applications

trigger:

<Определение_триггера>::= (CREATE | ALTER) TRIGGER trigger_name ON (table_name | view_name) ( ( ( FOR | AFTER | INSTEAD OF ) ( [ DELETE] [,] [ INSERT] [,] [ UPDATE] ) [ WITH APPEND ] [ NOT FOR REPLICATION ] AS sql_statement[...n] ) | ( (FOR | AFTER | INSTEAD OF ) ( [,] ) [ WITH APPEND] [ NOT FOR REPLICATION] AS ( IF UPDATE(column_name) [ (AND | OR) UPDATE( column_name)] [...n] | IF (COLUMNS_UPDATES() (process_bit_operator) change_bit_mask) (comparison_bit_operator) bit_mask [...n]) sql_operator [...n] ) )

A trigger can only be created in the current database, but it is possible to access other databases within the trigger, including those located on a remote server.

Let's look at the purpose of the arguments from the CREATE | ALTER TRIGGER.

The trigger name must be unique within the database. Additionally, you can specify the owner's name.

When you specify the WITH ENCRYPTION argument, the server encrypts the trigger code so that no one, including an administrator, can access or read it. Encryption is often used to hide proprietary data processing algorithms that are the programmer's intellectual property or a trade secret.

Trigger Types

IN SQL Server There are two parameters that determine the behavior of triggers:

AFTER. The trigger is executed after the commands that called it have successfully completed. If the commands cannot be completed successfully for any reason, the trigger is not executed. It should be noted that data changes as a result of executing a user request and trigger execution are carried out in the body of one transaction: if the trigger is rolled back, then user changes will also be rejected. You can define multiple AFTER triggers for each operation (INSERT, UPDATE, DELETE). If you have multiple AFTER triggers on a table, you can use the sp_settriggerorder system stored procedure to specify which trigger will run first and which will run last. By default, in SQL Server, all triggers are AFTER triggers.
INSTEAD OF . The trigger is called instead of executing commands. Unlike the AFTER trigger, the INSTEAD OF trigger can be defined for both a table and a view. For each INSERT, UPDATE, DELETE operation, only one INSTEAD OF trigger can be defined.

Triggers are distinguished by the type of commands to which they respond.

There are three types of triggers:

INSERT TRIGGER – Triggered when an attempt is made to insert data using the INSERT command.
UPDATE TRIGGER – triggered when an attempt is made to change data using the UPDATE command.
DELETE TRIGGER – triggered when an attempt is made to delete data using the DELETE command.

Constructions [ DELETE] [,] [ INSERT] [,] [ UPDATE] And FOR | AFTER | INSTEAD OF ) ([,] determine which command the trigger will respond to. When creating it, at least one command must be specified. Allowed creating a trigger, responding to two or all three commands.

WITH APPEND allows you to create multiple triggers of each type.

At creating a trigger with the NOT FOR REPLICATION argument, it is prohibited from running while tables are being modified by replication mechanisms.

The AS sql_operator[...n] construction defines a set of SQL statements and commands that will be executed when the trigger is launched.

Note that a number of operations are not allowed inside a trigger, such as:

creating, modifying and deleting a database;
recovery backup copy database or transaction log.

These commands are not allowed to execute because they cannot be rolled back if the transaction in which the trigger is executed is rolled back. This prohibition is unlikely to in any way affect the functionality of created triggers. It is difficult to find a situation where, for example, after changing a table row, you would need to restore a transaction log backup.

Trigger programming

When executing commands to add, modify, and delete records, the server creates two special tables: inserted And deleted. They contain lists of rows that will be inserted or deleted when the transaction completes. The structure of the inserted and deleted tables is identical to the structure of the tables for which the trigger is defined. Each trigger creates its own set of inserted and deleted tables, so no other trigger can access them. Depending on the type of operation that caused the trigger to execute, the contents of the inserted and deleted tables may be different:

INSERT command – the inserted table contains all the rows that the user tries to insert into the table; there will not be a single row in the deleted table; after the trigger completes, all rows from the inserted table will be moved to the source table;
DELETE command – the deleted table will contain all rows that the user tries to delete; the trigger can check each row and determine whether it is allowed to be deleted; there will be no rows in the inserted table;
UPDATE command - when executed, the deleted table contains old row values that will be deleted upon successful completion

Last update: 11/09/2017

Triggers are a special type of stored procedure that is called automatically when a certain action is performed on a table or view, in particular, when adding, changing or deleting data, that is, when performing INSERT, UPDATE, DELETE commands.

Formal definition of a trigger:

CREATE TRIGGER trigger_name ON (table_name | view_name) (AFTER | INSTEAD OF) AS sql_expressions

To create a trigger, use the CREATE TRIGGER statement followed by the trigger name. Typically, the trigger name reflects the type of operation and the name of the table on which the operation is performed.

Each trigger is associated with a specific table or view, the name of which is indicated after the word ON .

Then the trigger type is set. We can use one of two types:

AFTER: Executed after the action has completed. Defined only for tables.

INSTEAD OF: executed instead of an action (that is, in fact, the action - adding, changing or deleting - is not performed at all). Defined for tables and views

After the trigger type comes an indication of the operation for which the trigger is defined: INSERT, UPDATE or DELETE.

For an AFTER trigger, you can use it for several actions at once, for example, UPDATE and INSERT. In this case, operations are indicated separated by commas. You can only define one action for an INSTEAD OF trigger.

And then after the word AS comes a set of SQL expressions, which actually make up the body of the trigger.

Let's create a trigger. Let's say we have a database productsdb with the following definition:

CREATE DATABASE productdb; GO USE productdb; CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

Let's define a trigger that will fire when adding and updating data:

USE productdb; GO CREATE TRIGGER Products_INSERT_UPDATE ON Products AFTER INSERT, UPDATE AS UPDATE Products SET Price = Price + Price * 0.38 WHERE Id = (SELECT Id FROM inserted)

Let's say the Products table stores data about products. But the price of a product often contains various surcharges such as value added tax, added corruption tax, and so on. The person adding the data may not know all these subtleties with the tax base, and he determines the net price. Using a trigger, we can adjust the price of a product by a certain amount.

This way, the trigger will fire on any INSERT or UPDATE operation on the Products table. The trigger itself will change the price of the product, and to get the product that was added or changed, we find this product by Id. But what value should the Id of such a product have? The fact is that when adding or changing data, it is saved in the intermediate table inserted. It is created automatically. And from it we can get data about added/changed products.

And after adding a product to the Products table, in reality the product will have a slightly higher price than the one that was determined when added:

Removing a trigger

To remove a trigger, you must use the DROP TRIGGER command:

DROP TRIGGER Products_INSERT_UPDATE

Disabling a trigger

It happens that we want to pause a trigger, but we don’t want to remove it completely. In this case, it can be temporarily disabled using the DISABLE TRIGGER command:

DISABLE TRIGGER Products_INSERT_UPDATE ON Products

And when you need a trigger, you can enable it using the ENABLE TRIGGER command:

ENABLE TRIGGER Products_INSERT_UPDATE ON Products

Trigger is a subroutine similar to a database procedure, automatically called by the DBMS when changing, deleting or adding a record in a table. Triggers cannot be accessed from a program, passed parameters to them, or received results from them. Most often, triggers are used to maintain referential integrity and cascade operations in the database. Reference specifications, which define cascading delete and update actions and are created when tables are declared, are also implemented through triggers, but the text of these triggers is not editable.

Purpose of triggers

Prevent changes (for example, prevent invoices from being changed after they have been sent out).
. Log changes (for example, keep copies of old data).
. Audit changes (for example, keep a log of users and roles involved in changes).
. Capture changes (for example, ensure that all changes are dated according to the server's clock, not the client's).
. Implementation of business rules.
. Data replication (for example, store a record of all changes that will be sent to another database at a later version).
. Increased productivity (for example, updating the balance after each transaction detail, to speed up queries).

Declaring triggers

CREATE TRIGGER {BEFORE|AFTER} {DELETE|INSERT|UPDATE [OF ]} ON REFERENCE {OLD {[ROW]|TABLE [AS] } NEW {ROW|TABLE} [AS] }] [FOR EACH {STATEMENT|ROW [WHEN ]}]
[BEGIN ATOMIC]

[END]

Keywords

. BEFORE|AFTER– trigger start time – before | after the update operation.
. DELETE|INSERT|UPDATE= trigger event.
. FOR EACH ROW– for each line (line trigger, then WHEN).
. FOR EACH STATEMENT– for the entire team (valid by default).
. REFERENCE– allows you to assign up to 4 aliases to old and | or new lines and | or tables that can be accessed by triggers.

Trigger restrictions

The trigger body cannot contain the following statements:
. Defining, deleting and changing database objects (tables, domains, etc.)
. Transaction processing (COMMIT, ROLLBACK)
. Connections and disconnections to the database (CONNECT, DISCONNECT)

Features of application
. The trigger is executed after all other (declarative) integrity checks have been applied and is useful when the test criterion is quite complex. If declarative checks reject the update operation, then triggers are not executed. The trigger operates in the context of a transaction, but the FK constraint does not.
. If a trigger causes an additional modification to its base table, then most often this will not lead to its recursive execution, but this should be clarified. SQL Server 2005 provides the ability to specify recursion up to 255 levels using the OPTION (MAXRECURSIV 3) keyword.
. Triggers are not typically executed when processing large binary columns (BLOBs).
. It should be remembered that whenever data is updated, the DBMS automatically creates so-called trigger virtual tables, which have different names in different DBMSs. In InterBase and Oracle – These are New and Old. In SQL Server - Inserted and Deleted. Moreover, when the data changes, both are created. These tables have the same number of columns, with the same names and domains as the table being updated. The SQL Server 2005 DBMS provides the ability to specify a table, including a temporary table, into which data should be inserted using the OUTPUT Inserted.ID,... INTO @ keyword.
. In a number of DBMSs, it is permissible to declare triggers for several actions simultaneously. To implement different reactions to various actions, Oracle provides predicates Deleting, Inserting, Updating, which return True for the corresponding type of update.
. In the Oracle DBMS, you can specify a list of columns (After Update Of) for Update triggers, which will ensure that the trigger is called only when the values of only these columns change.
. Multiple triggers can be declared for each trigger event (Oracle has 12 triggers per table) and usually the order in which they are fired is determined by the order in which they are created. In some DBMSs, such as InterBase, the startup order is specified using the additional POSITION keyword. In general, triggers should be executed for each command first, and then for each line.
. Triggers can be embedded within each other. Thus, SQL Server allows 32 nesting levels (you can use the @@NextLevel global variable to determine the nesting level).

Disadvantages of Triggers

Complexity. Placing some actions on data in the database complicates its design, implementation and administration.
. Stealth functionality from the user. It is difficult to modernize an application when some features are hidden.
. Impact on performance. With a small number of triggers, data processing time increases.

Editing and deleting triggers

To remove a trigger, use the DROP TRIGGER statement
. To change a trigger, use the ALTER TRIGGER... statement.
. Disabling triggers
In some cases, for example, during batch loading, triggers need to be disabled. A number of DBMSs provide corresponding capabilities. In Oracle and SQL Server the keywords are DISABLE|ENABLE, in InterBase INACTIVE|ACTIVE in the ALTER TRIGGER statement.

Features of industrial servers

1) InterBase/Firebird

CREATE TRIGGER FOR {ACTIVE|INACTIVE} {BEFORE|AFTER} {INSERT|DELETE|UPDATE} [POSITION ]
AS [DECLARE VARIABLE [()]]
BEGIN

END

Example:

CREATE TRIGGER BF_Del_Cust FOR Customer
ACTIVE BEFORE DELETE POSITION 1 AS
BEGIN
DELETE FROM Orders WHERE Orders.CNum=Customer.CNum;
END;

2) SQL Server

CREATE TRIGGER ON [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} {INSERT|UPDATE|DELETE}
AS

USE B1;
GO
CREATE TRIGGER InUpCust1 ON Customer AFTER INSERT, UPDATE
AS RAISEERROR('Customer table changed');

Additional types of triggers

Oracle and SQL Server provide the ability to create (replacement) triggers for views that are not updated. For this purpose, the INSTEAD OF keywords are provided:

CREATE TRIGGER ON INSTEAD OF INSERT AS …

You can monitor client attempts to update data using views and perform any actions, handle views that are not updated, etc.
. The SQL Server DBMS provides a rollback trigger that actually stops all actions and issues a message:

ROLLBACK TRIGGER

Lecture notes on the discipline “Databases”

Topic: Procedures and triggers

(using MS SQL Server as an example)

compiler: L. V. Shchegoleva

PetrSU, Department of Applied Mathematics and Cybernetics

Introduction........................................................ ........................................................ ......
	Description of the database structure......................................................... ..............
	Concept of procedure................................................... ....................................
	Commands for working with procedures................................................................... .............
	The concept of a trigger................................................... ........................................
	Commands for working with triggers................................................................. ....................
	Examples of trigger implementations................................................... ...............
	Example 1................................................... ...............................................
	Example 2................................................... ...............................................
	Example 3................................................... ...............................................
	Example 4................................................... ...............................................
	Example 5................................................... ...............................................

PetrSU, Department of Applied Mathematics and Cybernetics

Introduction

IN This manual provides examples of commands for creating procedures and triggers

With description of their work.

All commands are written in MS SQL Server syntax.

The examples are given for a database whose structure is described in

section 1.

PetrSU, Department of Applied Mathematics and Cybernetics

1 Description of the database structure

The tblFaculty table contains information about the faculties of the university.

Name			Description
attribute
			Faculty ID
			Faculty name
			Full name of the dean
			Dean's office number
			Dean's office phone number
			Number of faculty students

The tblStudent table contains information about university students in one academic year.

Attribute name			Description
			Student ID
			Student's name

			Scholarship

The tblGroup table contains information about university student groups in one academic year.

Attribute name			Description
			Group ID
			Headman
			Group number
			Faculty

The tblSubject table contains information about the disciplines studied by students of the faculty in one academic year.



	Attribute name			Description
				Item ID
				Item name
				Number of lecture hours
				Number of practice hours
				Faculty

	Lecture notes on the discipline “Databases” (Procedures and triggers)

PetrSU, Department of Applied Mathematics and Cybernetics

The tblRoom table contains information about the university classrooms.

The tblSchedule table contains information about the schedule of student groups.

Attribute name			Description
			Identifier
			Audience
			Day of the week



			Full name of the teacher

PetrSU, Department of Applied Mathematics and Cybernetics

2 Concept of procedure

A procedure is a program written in the internal language of the DBMS, stored in the database as an independent object. Such procedures are usually called stored,attached procedures. Procedures can be called by an application program. The procedures are executed on the database server. Procedures can contain parameters and return values, including error messages.

Benefits of using procedures:

– centralized data access control;

– application programs can call a procedure, which reduces the time of writing programs; when a procedure is modified, all programs calling it will receive new code, code optimization;

– reduces network traffic in systems“client-server” by passing only the name of the procedure and its parameters instead of exchanging data, and the procedure is executed on the server;

– hiding many features from the user specific device databases, which ensures greater data independence;

– greater data security, the user may have the right to call

procedure, but does not manipulate the data that is called by this procedure; Disadvantage: lack of standards in the implementation of procedures.

PetrSU, Department of Applied Mathematics and Cybernetics

3 Commands for working with procedures in MS SQL Server

Creating a procedure

CREATE PROCEDURE<имя процедуры>

[@<имя параметра> <тип данных> , ...]

BEGIN

<операторы>

The names of all variables in MS SQL Server must begin with the symbol

Calling a procedure

EXECUTE<имя процедуры> [{@<имя переменной> | <значение параметра>}, ...]

Removing a procedure

DROP PROCEDURE<имя процедуры>

The procedure counts the number of students enrolled in the department whose identifier is the input parameter of the @id procedure and returns this value in the @total_sum parameter.

Create Procedure prStudentsOfFaculty @id int, @total_sum int output AS

Set @total_sum = 0

Set @total_sum = (Select count(*) From tblStudent, tblGroup Where (tblStudent.GroupId = tblGroup.GroupId) and (tblGroup.FacultyId = @id)) End

PetrSU, Department of Applied Mathematics and Cybernetics

4 The concept of a trigger

A trigger (rule) is attached to a table and is automatically called by the database management system when update operations are performed on the table (adding, deleting, changing table records).

Features of implementing triggers in MS SQL Server

In MS SQL Server:

– a trigger can be called either after an operation has been performed, or instead of an operation being performed;

– the trigger is called once for all table records on which the operation must be performed;

– therefore, the records being modified are stored in two tables that are automatically created when the trigger is called:

o Inserted table – contains changed or added table records;

o Deleted table – contains records before changes were made or deleted table records;

– in the body of a trigger defined for an Insert operation, only the table is available

– in the body of the trigger defined for the Delete operation, only the table is available

– in the body of the trigger defined for the Update operation, both tables are available

Inserted and Deleted;

– Any number of triggers can be created for the same event, and they are called in a random order (possibly in the order in which they were created).

PetrSU, Department of Applied Mathematics and Cybernetics

5 Commands for working with triggers

Creation

CREATE TRIGGER<имя триггера>ON<имя таблицы>

( FOR | AFTER | INSTEAD OF )

[INSERT] [,] [UPDATE] [,] [DELETE] AS

DECLARE @<имя переменной> <тип данных>, ...

BEGIN<операторы>

Removal

DROP TRIGGER<имя триггера>

PetrSU, Department of Applied Mathematics and Cybernetics

6 Examples of trigger implementations

Subject area limitation: A student's scholarship cannot be increased by more than 5% of the previous scholarship.

CREATE TRIGGER tgrStudentGrantUpdate

ON tblStudent AFTER UPDATE

DECLARE @Grant_old float, @Grant_new float, @Id int;

Select @Grant_old = Grant from Deleted

Select @Grant_new = Grant, @Id = StudentId from Inserted

IF (@Grant_new - @Grant_old > 0.05 * @Grant_old)

UPDATE tblStudent SET Grant = 1.05 * @Grant_old

WHERE StudentId = @Id

The tgrStudentGrantUpdate trigger has been created for the tblStudent table. The trigger will fire after the data change operation is completed.

IN The trigger defines three local variables: @Grant_old (real type) to store the old student scholarship, @Grant_new (real type) to store the new student scholarship, @Id (integer type) to store the student identifier.

When a trigger is called, the DBMS creates two tables: Deleted, which contains the modified records before they are modified, and Inserted, which contains the modified records after they are modified.

IN In the body of the trigger, first of all, the value of the student's scholarship is retrieved from the Deleted table before the changes are made, i.e., the old scholarship; then the value of the student's scholarship is retrieved from the Inserted table after the changes are made, i.e., the new scholarship. Along with retrieving a new scholarship from the Inserted table, the student identifier is also retrieved. The student ID could just as easily be retrieved from the Deleted table.

Next, in the body of the trigger, the condition about the amount of change in the scholarship is checked. If the scholarship has changed by more than 5%, then the trigger makes an adjustment to the data - it increases the scholarship by only 5% compared to the student’s previous scholarship value. This action is performed by calling the Update operation on the tblStudent table for the corresponding student.

The definition of a trigger, the scope of its use, the place and role of the trigger in ensuring data integrity are given. Types of triggers are described. Operators for creating, changing, and deleting a trigger are considered. Trigger programming is illustrated with examples of creating triggers to implement integrity constraints and collect statistical data.

Trigger Definition in the SQL Language Standard

Triggers are a type of stored procedure. They are executed when a data manipulation language (DML) operator is executed on the table. Triggers are used to check data integrity and also to roll back transactions.

A trigger is a compiled SQL procedure, the execution of which is caused by the occurrence of certain events within a relational database. The use of triggers is, for the most part, very convenient for database users. Still, their use often involves additional resource costs for I/O operations. When the same results (with much less overhead) can be achieved using stored procedures or application programs, the use of triggers is not practical.

Triggers are a special SQL server tool used to maintain the integrity of data in a database. Integrity constraints, rules, and defaults may not always achieve the desired level of functionality. It is often necessary to implement complex data verification algorithms to ensure their reliability and reality. In addition, sometimes you need to monitor changes in table values so that the associated data can be modified as needed. Triggers can be thought of as a kind of filters that come into effect after all operations have been completed in accordance with rules, standard values, etc.

A trigger is a special type of stored procedure that is launched automatically by the server when an attempt is made to change data in the tables to which the triggers are associated. Each trigger is associated with a specific table. All data modifications it makes are considered as one transaction. If an error or data integrity violation is detected, the transaction is rolled back. Changes are therefore prohibited. Any changes already made by the trigger are also undone.

Only the database owner can create a trigger. This restriction allows you to avoid accidental changes to the structure of tables, ways of connecting other objects to them, etc.

The trigger is a very useful and at the same time dangerous tool. So, if the logic of its operation is incorrect, you can easily destroy an entire database, so triggers must be debugged very carefully.

Unlike a regular routine, a trigger is executed implicitly every time it occurs. trigger event, besides, it has no arguments. Actuating it is sometimes called firing a trigger. Using triggers, the following goals are achieved:

Validating the correctness of data entered and enforcing complex data integrity constraints that are difficult, if not impossible, to maintain using integrity constraints set on a table;
issuing warnings reminding you to perform certain actions when updating a table implemented in a certain way;
accumulation of audit information by recording information about the changes made and those persons who performed them;
replication support.

The basic format of the CREATE TRIGGER command is shown below:

<Определение_триггера>::= CREATE TRIGGER trigger_name BEFORE | AFTER<триггерное_событие>ON<имя_таблицы> <тело_триггера>

trigger events consist of inserting, deleting, and updating rows in a table. In the latter case for trigger event You can specify specific table column names. The trigger time is determined using keywords BEFORE (the trigger is fired before the events associated with it are executed) or AFTER (after they are executed).

The actions performed by the trigger are specified for each row (FOR EACH ROW) covered by a given event, or only once for each event (FOR EACH STATEMENT).

Designation <список_старых_или_новых_псевдонимов> refers to components such as old or new line(OLD/NEW) either old or new table(OLD TABLE / NEW TABLE). It is clear that the old values do not apply to insert events, and the new values do not apply to delete events.

When used correctly, triggers can be a very powerful mechanism. Their main advantage is that standard functions are stored within the database and are consistently activated every time it is updated. This can greatly simplify applications. However, it is worth mentioning the inherent disadvantages of the trigger:

complexity: when some functions are moved into the database, the tasks of its design, implementation and administration become more complex;
Hidden functionality: Moving some functionality into a database and storing it as one or more triggers sometimes results in some functionality being hidden from the user. Although this simplifies its operation to a certain extent, it unfortunately can cause unintended, potentially unwanted and harmful side effects, since in this case the user is not able to control all the processes occurring in the database;
performance impact: before executing each command to change the state of the database, the DBMS must check the trigger condition to determine whether a trigger should be fired for this command. Performing such calculations affects the overall performance of the DBMS, and at times of peak load, its decrease can become especially noticeable. Obviously, as the number of triggers increases, the overhead costs associated with such operations also increase.

Incorrectly written triggers can lead to serious problems, such as dead locks. Triggers can block many resources for long periods of time, so special attention should be paid to minimizing access conflicts.

Implementation of triggers in MS SQL Server environment

The MS SQL Server DBMS implementation uses the following trigger creation or modification operator:

A trigger can only be created in the current database, but it is possible to access other databases within the trigger, including those located on a remote server.

Let's look at the purpose of the arguments from the CREATE | ALTER TRIGGER.

The trigger name must be unique within the database. Additionally, you can specify the owner's name.

Trigger Types

There are two options in SQL Server that determine the behavior of triggers:

AFTER. The trigger is executed after the commands that called it have successfully completed. If the commands cannot be completed successfully for any reason, the trigger is not executed. It should be noted that data changes as a result of executing a user request and trigger execution are carried out in the body of one transaction: if the trigger is rolled back, then user changes will also be rejected. You can define multiple AFTER triggers for each operation (INSERT, UPDATE, DELETE). If you have multiple AFTER triggers on a table, you can use the sp_settriggerorder system stored procedure to specify which trigger will run first and which will run last. By default, in SQL Server, all triggers are AFTER triggers.
INSTEAD OF . The trigger is called instead of executing commands. Unlike the AFTER trigger, the INSTEAD OF trigger can be defined for both a table and a view. For each INSERT, UPDATE, DELETE operation, only one INSTEAD OF trigger can be defined.

Triggers are distinguished by the type of commands to which they respond.

There are three types of triggers:

INSERT TRIGGER – Triggered when an attempt is made to insert data using the INSERT command.
UPDATE TRIGGER – triggered when an attempt is made to change data using the UPDATE command.
DELETE TRIGGER – triggered when an attempt is made to delete data using the DELETE command.

WITH APPEND allows you to create multiple triggers of each type.

At creating a trigger with the NOT FOR REPLICATION argument, it is prohibited from running while tables are being modified by replication mechanisms.

The AS sql_operator[...n] construction defines a set of SQL statements and commands that will be executed when the trigger is launched.

Note that a number of operations are not allowed inside a trigger, such as:

creating, modifying and deleting a database;
restoring a database or transaction log backup.

Trigger programming

INSERT command – the inserted table contains all the rows that the user tries to insert into the table; there will not be a single row in the deleted table; after the trigger completes, all rows from the inserted table will be moved to the source table;
DELETE command – the deleted table will contain all rows that the user tries to delete; the trigger can check each row and determine whether it is allowed to be deleted; there will be no rows in the inserted table;
UPDATE command - when executed, the deleted table contains old row values that will be deleted upon successful completion of the trigger. The new row values are contained in the inserted table. These rows will be added to the source table after the trigger is executed successfully.

To obtain information about the number of rows that will be modified when a trigger completes successfully, you can use the @@ROWCOUNT; function. it returns the number of rows processed by the last command. It should be emphasized that the trigger is fired not when an attempt is made to change a specific line, but at the moment the change command is executed. One such command affects many rows, so the trigger must process all of these rows.

If the trigger detects that out of 100 rows being inserted, modified, or deleted, only one does not satisfy certain conditions, then no row will be inserted, modified, or deleted. This behavior is due to the requirements of the transaction - either all modifications must be performed or none.

A trigger executes as an implicitly defined transaction, so transaction control commands can be used within the trigger. Specifically, when an integrity constraint violation is detected, the ROLLBACK TRANSACTION command must be used to abort the trigger and undo any changes the user attempted to make.

You can use the COLUMNS_UPDATED() function to obtain a list of columns that were modified by the INSERT or UPDATE commands that caused the trigger to execute. It returns a binary number, each bit of which, starting with the least significant bit, corresponds to one column of the table (in the order of the columns when the table was created). If a bit is set to "1", then the corresponding column has been modified. In addition, the fact that a column has changed is determined by the UPDATE function (column_name).

For remove trigger the command is used

DROP TRIGGER (trigger_name) [,...n]

Here are examples of using triggers.

Example 14.1. Using a trigger to implementation of restrictions on value. In the entry added to the Transaction table, the quantity of the product sold must be no less than its balance from the Warehouse table.

The command for inserting a record into the Deal table could be, for example, like this:

INSERT INTO Trade VALUES (3,1,-299,"01/08/2002")

The created trigger should react to its execution in the following way: it is necessary to cancel the command if in the Warehouse table the product balance is less than the sold quantity of the product with the entered code (in the example, product code = 3). In the inserted record, the quantity of the product is indicated with a “+” sign if the product is supplied, and with a “-” sign if it is sold. The presented trigger is configured to process only one added record.

CREATE TRIGGER Trigger_ins ON Transaction FOR INSERT AS IF @@ROWCOUNT=1 BEGIN IF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.quantity<=ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "Отмена поставки: товара на складе нет" END END Example 14.1. Using a trigger to implement restrictions on a value.

Example 14.2. Using a trigger to collect statistical data.

Create a trigger to process the operation of inserting a record into the Deal table, for example, the following command:

INSERT INTO Trade VALUES (3,1,200,"01/08/2002")

Product with code 3 is supplied from a customer with code 1 in the amount of 200 units.

When selling or receiving an item, the inventory quantity must be adjusted accordingly. If the product is not yet in stock, you need to add a corresponding entry to the Warehouse table. The trigger processes only one added row.

ALTER TRIGGER Trigger_ins ON Transaction FOR INSERT AS DECLARE @x INT, @y INT IF @@ROWCOUNT=1 --a record is added to the Transaction table about the delivery of goods BEGIN --the quantity of goods sold must not be --less than its balance from the Warehouse table IF NOT EXISTS(SELECT * FROM inserted WHERE -inserted.quantity< =ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "откат товара нет " END --если записи о поставленном товаре еще нет, --добавляется соответствующая запись --в таблицу Склад IF NOT EXISTS (SELECT * FROM Склад С, inserted i WHERE С.КодТовара=i.КодТовара) INSERT INTO Склад (КодТовара,Остаток) ELSE --если запись о товаре уже была в таблице --Склад, то определяется код и количество --товара издобавленной в таблицу Сделка записи BEGIN SELECT @y=i.КодТовара, @x=i.Количество FROM Сделка С, inserted i WHERE С.КодТовара=i.КодТовара --и производится изменения количества товара в --таблице Склад UPDATE Склад SET Остаток=остаток+@x WHERE КодТовара=@y END END Example 14.2. Using a trigger to collect statistical data.

Example 14.3. Create a trigger to process the operation of deleting a record from the Deal table, for example, the following command:

For the product whose code was specified when the record was deleted, it is necessary to adjust its stock balance. The trigger processes only one record to be deleted.

CREATE TRIGGER Trigger_del ON Transaction FOR DELETE AS IF @@ROWCOUNT=1 -- one record is deleted BEGIN DECLARE @y INT,@x INT --the code and quantity of the product is determined from the record -- deleted from the Warehouse table SELECT @y=ProductCode, @ x=Quantity FROM deleted --in the Warehouse table the quantity of --item is adjusted UPDATE Warehouse SET Remaining=Remaining-@x WHERE Product Code=@y END Example 14.3. Trigger for processing the operation of deleting a record from a table

Example 14.4. Create a trigger to process the operation of changing a record in the Deal table, for example, with the following command:

in all transactions with goods with a code equal to 3, reduce the quantity of goods by 10 units.

The specified command can lead to changes in several records at once in the Deal table. Therefore, we will show how to create a trigger that processes more than one record. For each changed entry, it is necessary for the old (before the change) product code to reduce the balance of the product in the warehouse by the value of the old (before the change) quantity of the product and for the new (after the change) product code to increase its balance in the warehouse by the value of the new (after the change) value. To process all changed records, we will introduce cursors in which we will save all old (from the deleted table) and all new values (from the inserted table).

CREATE TRIGGER Trigger_upd ON Transaction FOR UPDATE AS DECLARE @x INT, @x_old INT, @y INT, @y_old INT -- cursor with new values DECLARE CUR1 CURSOR FOR SELECT Product Code, Quantity FROM inserted -- cursor with old values DECLARE CUR2 CURSOR FOR SELECT Product Code, Quantity FROM deleted OPEN CUR1 OPEN CUR2 -- move in parallel through both cursors FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN -- for the old product code it decreases --the quantity in the warehouse UPDATE Warehouse SET Remaining=Remaining-@y_old WHERE Product Code=@x_old --for a new product code, if such a product is not yet in stock, a new record is entered IF NOT EXISTS (SELECT * FROM Warehouse WHERE Product Code=@x) INSERT INTO Warehouse(Product Code, Remaining) VALUES (@x,@y) ELSE --otherwise for a new product code it increases --its quantity in stock UPDATE Warehouse SET Remaining=Remaining+@y WHERE Product Code=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Example 14.4. trigger for processing an operation to change a record in a table

In the considered trigger, there is no comparison of the quantity of goods when a transaction record is changed with its balance in the warehouse.

Example 14.5. Let's correct this shortcoming. To generate an error message, we use the MS SQL Server RAISERROR command in the body of the trigger, the arguments of which are the message text, severity level and error status.

ALTER TRIGGER Trigger_upd ON Transaction FOR UPDATE AS DECLARE @x INT, @x_old INT, @y INT, @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT Item Code, Quantity FROM inserted DECLARE CUR2 CURSOR FOR SELECT Item Code, Quantity FROM deleted OPEN CUR1 OPEN CUR2 FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @o=remaining FROM Warehouse WHERE product code=@x IF @o<-@y BEGIN RAISERROR("откат",16,10) CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR22 ROLLBACK TRAN RETURN END UPDATE Склад SET Остаток=Остаток-@y_old WHERE КодТовара=@x_old IF NOT EXISTS (SELECT * FROM Склад WHERE КодТовара=@x) INSERT INTO Склад(КодТовара,Остаток) VALUES (@x,@y) ELSE UPDATE Склад SET Остаток=Остаток+@y WHERE КодТовара=@x FETCH NEXT FROM CUR1 INTO @x, @y FETCH NEXT FROM CUR2 INTO @x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Example 14.5. Corrected version of the trigger for processing the operation of changing a record in a table

Example 14.6. In the example, all changes are canceled if it is impossible to implement at least one of them. Let's create a trigger that allows you to cancel changes to only some records and change the rest.

In this case, the trigger is not executed after the records are changed, but instead of the change command.

ALTER TRIGGER Trigger_upd ON Transaction INSTEAD OF UPDATE AS DECLARE @k INT, @k_old INT DECLARE @x INT, @x_old INT, @y INT DECLARE @y_old INT ,@o INT DECLARE CUR1 CURSOR FOR SELECT Transaction Code, Product Code, Quantity FROM inserted DECLARE CUR2 CURSOR FOR SELECT Transaction Code, Product Code, Quantity FROM deleted OPEN CUR1 OPEN CUR2 FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old WHILE @@FETCH_STATUS=0 BEGIN SELECT @ o=remaining FROM Warehouse WHERE Product Code=@x IF @o>=-@y BEGIN RAISERROR("change",16,10) UPDATE Transaction SET quantity=@y, Product Code=@x WHERE Transaction Code=@k UPDATE Warehouse SET Remaining =Remaining-@y_old WHERE Item Code=@x_old IF NOT EXISTS (SELECT * FROM Warehouse WHERE Item Code=@x) INSERT INTO Warehouse(Item Code, Remaining) VALUES (@x,@y) ELSE UPDATE Warehouse SET Remaining=Remaining+@y WHERE Item Code=@x END ELSE RAISERROR("record not changed",16,10) FETCH NEXT FROM CUR1 INTO @k,@x, @y FETCH NEXT FROM CUR2 INTO @k_old,@x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Example 14.6. A trigger that allows you to undo changes to only some records and make changes to the rest.