Writing sql triggers. Features of industrial servers


Adapted from Robert Marda's article on sqlservercentral.com: Auditing Through Triggers

In this article, Robert provides code examples for several triggers installed on tables to audit user actions on MS records SQL Server 7.0/2000.

For an explanation of how triggers work in general and how they work in SQL Server 7.0 and SQL Server 2000, you can refer to the following articles written by Brian Kelley:

The first article explains the purpose of special tables for inserted and deleted Tables.
The following examples will work on SQL Server 2000, however they have only been tested on SQL Server 7.0.
First we must create the tables necessary for further work. Run the below script in Query Analyzer:

CREATE TABLE (
IDENTITY (1, 1) NOT NULL ,




NULL
(35) NULL
) ON
GO

CREATE TABLE (
NOT NULL
(25) NULL
(25) NULL
(75) NULL
(50) NULL
NULL
(35) NULL
) ON
GO

Trigger that tracks delete operations

If you want to capture deletions from a table, you can use an example trigger that will insert a row into ComponentsDeleted whenever a row is deleted from the table

Components: CREATE TRIGGER deletedby ON dbo.Components
FOR DELETE
AS
INSERT INTO ComponentsDeleted(Iden, ComponentName, SerialNumber,
Comments,
UserName, DeletedDate, DeletedBy)
SELECT Iden, ComponentName, SerialNumber, Comments, UserName, getdate(),
SYSTEM_USER
FROM deleted

Remove one or two rows from the Components table. Now look at the ComponentsDeleted table and you will see the rows you deleted there with the date and time of when they were deleted.

Setting up a simple auditing system using triggers, examples of which are presented in this article, can be useful in cases where you need to know who performed the actions monitored by triggers in your database and when.

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 defining cascade actions when deleting and updating and created when declaring tables, 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 that return True for the corresponding type of update.
. IN Oracle DBMS You can specify a list of columns for Update triggers (After Update Of), 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

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 intellectual property programmer or trade secret.

Trigger Types

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

  • AFTER. The trigger is executed after successful implementation the commands that called it. 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;
  • restoring a database or transaction log backup.

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

It's very likely that you know what a database trigger is, at least in general terms. There's even a chance that you know that MySQL supports triggers and has practice working with them. But most likely, most of you, even armed with knowledge, have no idea what advantages MySQL triggers hide. This is a must-have tool because triggers can completely change the way you work with data.

Introduction : what is a trigger

“Even as applications become more and more complex, we can abstract the application layer to manage them and improve the usability of the development process.”

For those who don't know, a trigger is a rule that you place on a table, and when a DELETE, UPDATE, or INSERT is executed, it performs additional actions. For example, we can make a journal entry about a change. But instead of writing two separate queries (one to change the data, the other to write a log entry), you can write a trigger that contains the rule: “Whenever a row changes, create a new row in another table to indicate that changes have been made.” changes." This approach creates some redundancy in the main query, but now there are no two different packets having to go through to your database server to do two different things, which improves performance overall.

Triggers have been introduced into MySQL since version 5.0.2. The trigger syntax is somewhat foreign. MySQL uses the ANSI SQL:2003 standard for procedures and other functions. If you work with programming languages, then it will not be difficult to understand. The specification is not publicly available, so we will try to use simple structures and explain what happens in a trigger. The same structures will be used as in any programming language.

As mentioned above, triggers are executed as procedures on UPDATE, DELETE and INSERT events. They can be executed either before or after the event is defined. This way you can define a trigger that will be executed before DELETE or after DELETE, and so on. This means that you can have one trigger that will execute before the INSERT and a completely different one that will execute after the INSERT, which is a very powerful tool.

Getting Started: Table Structure, Tools, and Notes

In this article we will work with a fictitious system for a shopping cart, each item of which will have a price. The data structure will be as simple as possible in order to demonstrate the procedures for working with triggers. Table and column names are designed to make things easier to understand, not to actually work. TIMESTAMPS is also used to facilitate the learning process. The tables have names carts, cart_items, cart_log, items, items_cost.

Will also be used very simple queries. There is no relationship between variables and no data input is used. Queries were prepared to be as simple and easy to read as possible.

PHP Quick Profiler was used to determine the execution time. Chive was used to illustrate the effects on the database. Chive is only for MySQL 5+ and is very similar to PHPMyAdmin. It has a more expressive interface, but contains significantly more bugs at the moment. The use of Chive is driven by the desire to provide more expressive screenshots of queries.

You may also need to change the MySQL delimiter when creating triggers. The original MySQL delimiter is; , but since we will be using a delimiter for added queries, we may need to explicitly specify the delimiter in order to create queries from the command line. When using Chive there is no need to change the separator.

To change the separator, you need to run the command before the trigger command:

DELIMITER $$

And after the trigger command you need to enter:

DELIMITER ;

Simple Trigger: Data Integrity

If you want to perform even minor normalization of the database structure, you may need to delete a master data source that has fragments participating in the overall data flow. For example, you might have a cart_id that references two or three tables without foreign keys, especially when using the MyISAM engine, which does not support them.

For such a case, you may have previously performed the following operations:

$sql = "DELETE FROM no_trigger_cart_items WHERE cart_id = 1";
$rs = $this->db->query($sql);
$sql = "DELETE FROM no_trigger_carts WHERE cart_id = 1";
$rs = $this->db->query($sql);

Now, depending on how organized you are yourself, you may have one API or method that clears out your recycle bins. If this is your case, then you will have an isolated function that makes two requests. If self-organization is not your thing, then you will have to always remember to empty the Trash items when you delete a specific Trash. Not difficult, but if you forget, you will lose the integrity of the data.

Let's get back to triggers. Let's create a simple trigger that, when deleting a cart, will delete all cart items that have the same cart_id:

CREATE TRIGGER `tutorial`.`before_delete_carts`
BEFORE DELETE ON `trigger_carts` FOR EACH ROW
BEGIN
DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;
END

Very simple syntax. Let's look at the trigger in detail.

The first line is “CREATE TRIGGER `tutorial`.`before_delete_carts`”. This is a command for MySQL to create a trigger for the “tutorial” database, which will be named “before_delete_carts”. We will use the naming scheme for triggers “When_What_Table”.

The second line specifies the MySQL trigger definition “BEFORE DELETE ON `trigger_carts` FOR EACH ROW”. We're telling MySQL that it needs to do something for each row before it can delete from a given table. What needs to be done is explained further between BEGIN and END. “DELETE FROM trigger_cart_items WHERE OLD.cart_id = cart_id;” For MySQL, it is specified that before deleting from trigger_carts, you need to take OLD.cart_id and also delete from trigger_cart_items. The OLD syntax defines a variable. It will be discussed in the next section where OLD and NEW will be combined.

The benefit of using a trigger is that the integrity of your data moves from the logic layer to the data layer, where it belongs. There is also some increase in system performance.

Two requests:

One request with trigger:

As you can see, there is a slight performance boost that is to be expected. The example database uses the same server as the client. But if the database server is located in a different location, then you should expect a more significant difference, since the time for executing queries will be added to the time of data transfer between servers. It should also be noted that the first time the trigger executes can be much slower than the next time.

Moving data logic to the data layer is similar to moving styling from the markup layer to the presentation layer, which is known to the world as CSS.

Wonderful simple trigger: logging and auditing

The next example we'll look at is related to event logging. For example, we want to monitor every item that is placed in a cart. Perhaps we want to track the purchase rating of products. Perhaps we just want to have a copy of each item placed in the cart, not necessarily for selling purposes, but for analyzing customer behavior. Whatever the reasons, let's look at the INSERT trigger, which opens up the possibility of logging or auditing our data.

Before using a trigger, we probably did something similar:

Now we can create a very simple trigger for the logging process:

CREATE TRIGGER `after_insert_cart_items`
AFTER INSERT ON `trigger_cart_items` FOR EACH ROW
BEGIN
INSERT INTO trigger_cart_log (cart_id, item_id)
VALUES(NEW.cart_id, NEW.item_id);
END

The first line is “CREATE TRIGGER `after_insert_cart_items`”. For MySQL, the command is given to create a trigger with the name “after_insert_cart_items”. The name could be “Foo” or “BullWinkle” or something else, but it is better to use the previously described trigger naming scheme. Next comes “AFTER INSERT ON `trigger_cart_items` FOR EACH ROW”. Again we are saying that after something is inserted into trigger_cart_items, operations between BEGIN and END need to be performed for each row.

The line “INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);” is a standard query using two variables. This uses NEW values ​​and inserts them into the cart_items table.

Once again, our query execution is faster:

To check that the trigger works, let's look at the values ​​in the table:

More complex trigger: business logic

From now on we will stop considering old way using multiple queries and comparing them with the technique of using triggers. Let's look at some more advanced examples of using triggers.

Business logic is where errors breed. Despite the care and attention to organizing the process, something always goes wrong. The UPDATE trigger allows you to somewhat mitigate this situation. We have the ability in the trigger to calculate the OLD value and set the NEW value based on the evaluation. For example, we want to always set the price of a product with a 30% premium to the cost. This leads to the fact that when we change (UPDATE) the cost, we must change (UPDATE) the price. Let's use a trigger.

CREATE TRIGGER `after_update_cost`
AFTER UPDATE ON `trigger_items_cost` FOR EACH ROW
BEGIN
UPDATE trigger_items
SET price = (NEW.cost * 1.3)
WHERE item_id = NEW.item_id;
END

We are changing the product table with prices based on NEW.cost * 1.3. If you enter a cost of $50, then the price should be $65.

This trigger works great.

Let's look at a more complex example. We already have a rule that changes the price of an item based on cost. Now we want to establish some tiering in prices. If the price is less than $50, then the current value will be $50. If the price is more than $50 but less than $100, then the current value will be $100.

To solve the problem, we will work with UPDATE again, but this time the trigger will be executed before the query is executed. An IF expression will also be used.

Here is the trigger text:

CREATE TRIGGER `before_update_cost`
BEFORE UPDATE ON `trigger_items_cost` FOR EACH ROW
BEGIN
IF NEW.cost< 50 THEN
SET NEW.cost = 50;
ELSEIF NEW.cost > 50 AND NEW.cost< 100 THEN
SET NEW.cost = 100;
ENDIF;
END

This is not a query, but an overlapping of values. If the price is less than $50, then we set it to $50. If the price lies between $50 and $100, then we set it to $100. If it is higher, then we just leave it as it is. The syntax is no different from other server-side languages. You need to close the IF expression with END IF.

Let's check the operation of our trigger. If you enter a cost value of $30, then the price should be $50:

For a value of $85:

To check that the AFTER UPDATE trigger is still working, the price should be $130:

Conclusion

In this article we have only scratched the surface of the iceberg of triggers in MySQL. They allow you to transfer rules for working with data from the application logic level to the data level.
Maybe using triggers on a one-page site is a hassle that takes time and effort. But complex web applications can be completely transformed by using triggers.

163

The preceding syntax applies only to DML triggers. DDL triggers have a slightly different form of syntax, which will be shown later.

Here, the schema_name parameter specifies the name of the schema to which the trigger belongs, and the trigger_name parameter specifies the name of the trigger. The table_name parameter specifies the name of the table for which the trigger is created. (Triggers for views are also supported, as indicated by the presence of the view_name parameter.)

You can also set the trigger type using two additional parameters: AFTER and INSTEAD OF. (The FOR parameter is a synonym for the AFTER parameter.) AFTER type triggers are called after the action that fires the trigger is executed, and INSTEAD OF type triggers are executed instead of the action that fires the trigger. AFTER triggers can only be created on tables, while INSTEAD OF triggers can be created on both tables and views.

The INSERT, UPDATE, and DELETE parameters specify the trigger action. Trigger action refers to the Transact-SQL statement that fires the trigger. Any combination of these three instructions is allowed. The DELETE statement is not allowed if the IF UPDATE option is used.

As you can see in the syntax of the CREATE TRIGGER statement, the trigger action (or actions) are specified in the AS sql_statement specification.

The Database Engine allows you to create multiple triggers per table and per action (INSERT, UPDATE, and DELETE). By default, there is no specific order of execution of multiple triggers for a given modifying action.

Only the database owner, DDL administrators, and the owner of the table on which the trigger is defined have permission to create triggers for the current database. (Unlike permissions for other types of CREATE statements, this permission is not transferable.)

Changing the Trigger Structure

Transact-SQL also supports the statement ALTER TRIGGER, which modifies the trigger structure. This instruction is typically used to change the body of a trigger. All clauses and parameters of the ALTER TRIGGER statement have the same meaning as the same clauses and parameters of the CREATE TRIGGER statement.

To remove triggers in the current database, use the statement DROP TRIGGER.

Using deleted and inserted virtual tables

When you create a trigger action, you typically need to specify whether it references a column value before or after it is changed by the action that fires the trigger. For this reason, two specially named virtual tables are used to test the effect of the statement that fires the trigger:

    deleted - contains copies of rows deleted from the table;

    inserted - contains copies of rows inserted into the table.

The structure of these tables is equivalent to the structure of the table for which the trigger is defined.

Table deleted is used if the CREATE TRIGGER statement specifies a DELETE or UPDATE clause, and if the statement specifies an INSERT or UPDATE clause, then table inserted. This means that for every DELETE statement executed in a trigger action, a deleted table is created. Similarly, for each INSERT statement executed in a trigger action, an inserted table is created.

The UPDATE statement is treated as a DELETE statement followed by an INSERT statement. Therefore, for each UPDATE statement executed in a trigger action, both a deleted table and an inserted table are created (in that order).

The inserted and deleted tables are implemented using row versioning, which was discussed in the previous article. When a DML statement (INSERT, UPDATE, or DELETE) is executed on a table with appropriate triggers, row versions are always created for all changes to that table. When a trigger needs information from the deleted table, it accesses the data in the row version store. In the case of an inserted table, the trigger accesses the most recent versions of the rows.

The row versioning engine uses the tempdb system database as the row version store. For this reason, if the database contains big number frequently used triggers, we should expect a significant increase in the volume of this system base data.

Areas of application of DML triggers

Such triggers are used to solve a variety of problems. In this section, we'll look at several uses of DML triggers, particularly the AFTER and INSTEAD OF triggers.

AFTER triggers

As you already know, AFTER triggers are fired after the action that fires the trigger is executed. An AFTER trigger is specified using the AFTER or FOR keyword. AFTER triggers can only be created on base tables. Triggers of this type can be used to perform, among other things, the following operations:

    creating a log of action logs in database tables;

    implementation of business rules;

    enforcement of referential integrity.

Creating a Log Log

In SQL Server, you can perform change data capture using the CDC change data capture system. This problem can also be solved using DML triggers. The example below shows how triggers can be used to create a log of activity logs in database tables:

USE SampleDb; /* The AuditBudget table is used as a log of activity logs in the Project table */ GO CREATE TABLE AuditBudget (ProjectNumber CHAR(4) NULL, UserName CHAR(16) NULL, Date DATETIME NULL, BudgetOld FLOAT NULL, BudgetNew FLOAT NULL); GO CREATE TRIGGER trigger_ModifyBudget ON Project AFTER UPDATE AS IF UPDATE(budget) BEGIN DECLARE @budgetOld FLOAT DECLARE @budgetNew FLOAT DECLARE @projectNumber CHAR(4) SELECT @budgetOld = (SELECT Budget FROM deleted) SELECT @budgetNew = (SELECT Budget FROM inserted) SELECT @projectNumber = (SELECT Number FROM deleted) INSERT INTO AuditBudget VALUES (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew) END

This example creates an AuditBudget table that stores all changes to the Budget column of the Project table. Changes to this column will be written to this table using the trigger_ModifyBudget trigger.

This trigger is fired for every change to the Budget column using an UPDATE statement. When this trigger is executed, the deleted and inserted table row values ​​are assigned to the corresponding @budgetOld, @budgetNew, and @projectNumber variables. These assigned values, along with the username and current date, will then be inserted into the AuditBudget table.

This example assumes that only one row will be updated at a time. Therefore, this example is a simplification of the common case where a trigger handles multi-row updates. If you do following instructions Transact-SQL:

then the contents of the AuditBudget table will be like this:

Implementation of business rules

Using triggers, you can create business rules for applications. The creation of such a trigger is shown in the example below:

USE SampleDb; -- The trigger_TotalBudget is an example of using -- a trigger to implement a business rule GO CREATE TRIGGER trigger_TotalBudget ON Project AFTER UPDATE AS IF UPDATE (Budget) BEGIN DECLARE @sum_old1 FLOAT DECLARE @sum_old2 FLOAT DECLARE @sum_new FLOAT SELECT @sum_new = (SELECT SUM( Budget) FROM inserted) SELECT @sum_old1 = (SELECT SUM(p.Budget) FROM project p WHERE p.Number NOT IN (SELECT d.Number FROM deleted d)) SELECT @sum_old2 = (SELECT SUM(Budget) FROM deleted) IF @sum_new > (@sum_old1 + @sum_old2) * 1.5 BEGIN PRINT "Budget unchanged" ROLLBACK TRANSACTION END ELSE PRINT "Budget change completed" END

Here you create a rule to control the modification of project budgets. The trigger_TotalBudget checks every budget change and executes only those UPDATE statements that increase the sum of all budgets by no more than 50%. Otherwise, the UPDATE statement is rolled back using a ROLLBACK TRANSACTION statement.

Enforcing Integrity Constraints

Database management systems use two types of constraints to ensure data integrity: declarative constraints, which are defined using the CREATE TABLE and ALTER TABLE language statements; procedural integrity constraints that are implemented through triggers.

IN ordinary situations Declarative constraints should be used to ensure integrity because they are supported by the system and do not require user implementation. The use of triggers is recommended only in cases for which there are no declarative constraints to ensure integrity.

The example below shows how to enforce referential integrity using triggers on the Employee and Works_on tables:

USE SampleDb; GO CREATE TRIGGER trigger_WorksonIntegrity ON Works_on AFTER INSERT, UPDATE AS IF UPDATE(EmpId) BEGIN IF (SELECT Employee.Id FROM Employee, inserted WHERE Employee.Id = inserted.EmpId) IS NULL BEGIN ROLLBACK TRANSACTION PRINT "The row was not inserted/modified" END ELSE PRINT "The row was inserted/modified" END

The trigger_WorksonIntegrity in this example checks referential integrity for the Employee and Works_on tables. This means that every change to the Id column in the reference Works_on table is checked and any violation of this constraint will not allow the operation to proceed. (The same applies to inserting new values ​​into the Id column.) The ROLLBACK TRANSACTION statement in the second BEGIN block rolls back the INSERT or UPDATE statement if a constraint is violated to ensure referential integrity.

In this example, the trigger checks for first and second case referential integrity problems between the Employee and Works_on tables. And the example below shows a trigger that checks for referential integrity problems in the third and fourth cases between the same tables (these cases were discussed in the article "Transact-SQL - creating tables"):

USE SampleDb; GO CREATE TRIGGER trigger_RefintWorkson2 ON Employee AFTER DELETE, UPDATE AS IF UPDATE (Id) BEGIN IF (SELECT COUNT(*) FROM Works_on, deleted WHERE Works_on.EmpId = deleted.Id) > 0 BEGIN ROLLBACK TRANSACTION PRINT "The row was not inserted/modified " END ELSE PRINT "The row was inserted/modified" END

Triggers INSTEAD OF

Offer trigger INSTEAD OF replaces the corresponding action that triggered it. This trigger runs after the corresponding inserted and deleted tables have been created, but before integrity checks or any other actions are performed.

INSTEAD OF triggers can be created for both tables and views. When a Transact-SQL statement references a view that has an INSTEAD OF trigger defined, the database system executes that trigger instead of taking any action on any table. This type The trigger always uses the information in the inserted and deleted tables created for the view to generate any statements required to create the requested event.

The column values ​​provided by the INSTEAD OF trigger must meet certain requirements:

    values ​​cannot be set for calculated columns;

    values ​​cannot be set for columns of the timestamp data type;

    values ​​cannot be set for columns with the IDENTITY property unless IDENTITY_INSERT is set to ON.

These requirements apply only to INSERT and UPDATE statements that reference base tables. An INSERT statement that references views with an INSTEAD OF trigger must provide values ​​for all columns of that view that do not allow nulls NULL values. (The same applies to an UPDATE statement. An UPDATE statement that references a view with an INSTEAD OF trigger must provide values ​​for all columns of a view that does not allow nulls and is referenced in the SET clause.)

The example below shows the difference in behavior when inserting values ​​into calculated columns using a table and its corresponding view:

USE SampleDb; CREATE TABLE Orders (OrderId INT NOT NULL, Price MONEY NOT NULL, Quantity INT NOT NULL, OrderDate DATETIME NOT NULL, Total AS Price * Quantity, ShippedDate AS DATEADD (DAY, 7, orderdate)); GO CREATE VIEW view_AllOrders AS SELECT * FROM Orders; GO CREATE TRIGGER trigger_orders ON view_AllOrders INSTEAD OF INSERT AS BEGIN INSERT INTO Orders SELECT OrderId, Price, Quantity, OrderDate FROM inserted END

This example uses an Orders table that contains two calculated columns. The view_AllOrders contains all the rows of this table. This view is used to set a value in its column that maps to a calculated column in the base table on which the view is created. This allows you to use an INSTEAD OF trigger, which in the case of an INSERT statement is replaced by a batch that inserts values ​​into the base table via the view_AllOrders view. (An INSERT statement that accesses the base table directly cannot set a value to a calculated column.)

Triggers first and last

The Database Engine allows you to create multiple triggers for each table or view and for each operation (INSERT, UPDATE, and DELETE) on them. In addition, you can specify the execution order for multiple triggers defined for a specific operation. Using a system procedure sp_settriggerorder You can specify that one of the AFTER triggers defined for a table will be executed first or last for each action processed. This system procedure has an @order parameter that can be assigned one of three values:

    first - indicates that the trigger is the first AFTER trigger executed to modify the action;

    last - indicates that this trigger is the last AFTER trigger executed to initiate an action;

    none - indicates that there is no specific execution order for the trigger. (This value is typically used to reset the trigger's previously set order of execution as first or last.)

Changing the structure of a trigger using the ALTER TRIGGER statement reverses the trigger's execution order (first or last). The following example shows the use of the sp_settriggerorder system procedure:

USE SampleDb; EXEC sp_settriggerorder @triggername = "trigger_ModifyBudget", @order = "first", @stmttype="update"

You can define only one first and only one last AFTER trigger for a table. The remaining AFTER triggers are executed in an unspecified order. You can find out the order in which a trigger is executed using the system procedure sp_helptrigger or OBJECTPROPERTY functions.

The result set returned by the sp_helptrigger system procedure contains an order column that specifies the order in which the specified trigger is executed. When you call the objectproperty function, its second parameter is ExeclsFirstTrigger or ExeclsLastTrigger, and its first parameter is always the ID number of the database object. If the property specified in the second parameter is true, the function returns the value 1.

Because an INSTEAD OF trigger executes before changes are made to its table, triggers of this type cannot be specified to run in first or last order.

DDL triggers and their applications

Previously, we looked at DML triggers, which specify the action the server takes when a table is modified by an INSERT, UPDATE, or DELETE statement. The Database Engine also allows you to define triggers for DDL statements such as CREATE DATABASE, DROP TABLE, and ALTER TABLE. Triggers for DDL statements have the following syntax:

CREATE TRIGGER trigger_name ON (ALL SERVER | DATABASE ) (FOR | AFTER ) ( event_group | event_type | LOGON) AS (batch | EXTERNAL NAME method_name) Syntax conventions

As you can see from their syntax, DDL triggers are created in the same way as DML triggers. And to modify and delete these triggers, the same ALTER TRIGGER and DROP TRIGGER statements are used as for DML triggers. Therefore, this section discusses only those parameters of the CREATE TRIGGER statement that are new to the DDL trigger syntax.

The first step when defining a DDL trigger is to specify its scope. DATABASE clause specifies the current database as the scope of the DDL trigger, and ALL SERVER offer- current server.

After specifying the scope of a DDL trigger, you must specify how the trigger will fire in response to the execution of one or more DDL statements. The event_type parameter specifies the DDL statement that, when executed, fires the trigger, and the event_group alternative parameter specifies the Transact-SQL event group. A DDL trigger fires after the execution of any Transact-SQL event specified in the event_group parameter. Keyword LOGON indicates the entry trigger.

Besides the similarities between DML and DDL triggers, there are also several differences between them. The main difference between these two types of triggers is that a DDL trigger can be scoped to the entire database or even the entire server, rather than just separate object. Additionally, DDL triggers do not support INSTEAD OF triggers. As you may have guessed, DDL triggers do not require inserted and deleted tables because these triggers do not change the contents of the tables.

The following subsections discuss in detail the two forms of DDL triggers: database-level triggers and server-level triggers.

Database Level DDL Triggers

The example below shows how you can implement a DDL trigger whose scope extends to the current database:

USE SampleDb; GO CREATE TRIGGER trigger_PreventDrop ON DATABASE FOR DROP_TRIGGER AS PRINT "Before you can delete a trigger, you must disable "trigger_PreventDrop"" ROLLBACK

The trigger in this example prevents any user from deleting any trigger on the SampleDb database. The DATABASE clause specifies that trigger_PreventDrop is a database-level trigger. Keyword DROP_TRIGGER specifies a predefined event type that prevents any trigger from being deleted.

Server Level DDL Triggers

Server-level triggers respond to server-side events. A server-level trigger is created by using the ALL SERVER clause in the CREATE TRIGGER statement. Depending on the action the trigger performs, there are two different types of server-level triggers: regular DDL triggers and login triggers. The firing of regular DDL triggers is based on DDL statement events, while the firing of entry triggers is based on entry events.

The example below demonstrates creating a server-level trigger that is a login trigger:

USE master; GO CREATE LOGIN loginTest WITH PASSWORD = "12345!", CHECK_EXPIRATION = ON; GO GRANT VIEW SERVER STATE TO loginTest; GO CREATE TRIGGER trigger_ConnectionLimit ON ALL SERVER WITH EXECUTE AS "loginTest" FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= "loginTest" AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = "loginTest") > 1 ROLLBACK ; END;

Here, the SQL Server loginTest is first created, which is then used in a server-level trigger. For this reason, this login requires VIEW SERVER STATE permission, which is granted to it through the GRANT statement. After this, the trigger_ConnectionLimit is created. This trigger is an input trigger, which is indicated by keyword LOGON.

Using View sys.dm_exec_sessions a check is made to see if a session has already been established using the loginTest login. If the session has already been established, the ROLLBACK statement is executed. Therefore, the loginTest login can only establish one session at a time.

Triggers and the CLR

Like stored procedures and user-defined functions, triggers can be implemented using the Common Language Runtime (CLR). Triggers in the CLR are created in three steps:

    The trigger source code is created in C# or Visual Basic, which is then compiled using the appropriate compiler into object code.

    The object code is processed with the CREATE ASSEMBLY statement, creating a corresponding executable file.

    The CREATE TRIGGER statement creates a trigger.

The following examples demonstrate all three steps of creating a CLR trigger. Below is an example source code C# programs for the trigger from the first example in the article. Before creating a CLR trigger in the following examples, you must first remove the trigger_PreventDrop trigger and then remove the trigger_ModifyBudget trigger, using the DROP TRIGGER statement in both cases.

Using System; using System.Data.SqlClient; using Microsoft.SqlServer.Server; public class Triggers ( public static void ModifyBudget() ( SqlTriggerContext context = SqlContext.TriggerContext; if (context.IsUpdatedColumn(2)) // Budget column ( float budget_old; float budget_new; string project_number; SqlConnection conn = new SqlConnection("context connection =true"); conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT Budget FROM DELETED"; budget_old = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Budget FROM INSERTED"; budget_new = (float)Convert.ToDouble(cmd.ExecuteScalar()); cmd.CommandText = "SELECT Number FROM DELETED"; project_number = Convert.ToString(cmd.ExecuteScalar()); cmd. CommandText = @"INSERT INTO AuditBudget (@projectNumber, USER_NAME(), GETDATE(), @budgetOld, @budgetNew)"; cmd.Parameters.AddWithValue("@projectNumber", project_number); cmd.Parameters.AddWithValue("@budgetOld ", budget_old); cmd.Parameters.AddWithValue("@budgetNew", budget_new); cmd.ExecuteNonQuery(); ) ) )

The Microsoft.SQLServer.Server namespace contains all the client classes that a C# program might need. Classes SqlTriggerContext And SqlFunction are members of this namespace. In addition, the System.Data.SqlClient namespace contains the SqlConnection and SqlCommand classes, which are used to establish connection and interaction between the client and the database server. The connection is established using the connection string "context connection = true".

Next, the Triggers class is defined, which is used to implement triggers. The ModifyBudget() method implements a trigger of the same name. The context instance of the SqlTriggerContext class allows an application to access the virtual table created when the trigger executes. This table stores the data that caused the trigger to fire. The IsUpdatedColumn() method of the SqlTriggerContext class allows you to find out whether the specified table column has been updated.

This program contains two other important classes: SqlConnection and SqlCommand. An instance of the SqlConnection class is typically used to establish a connection to a database, and an instance of the SqlCommand class allows you to execute SQL statements.

This example program can be compiled using the csc compiler, which is built into Visual Studio. The next step is to add a reference to the compiled assembly in the database:

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE

The CREATE ASSEMBLY statement takes managed code as input and creates a corresponding object from which to create a CLR trigger. The WITH PERMISSION_SET clause in the example specifies that access permissions are set to SAFE.

Finally, in the example below, a trigger_modify_budget is created using the CREATE TRIGGER statement:

USE SampleDb; GO CREATE TRIGGER trigger_modify_budget ON Project AFTER UPDATE AS EXTERNAL NAME CLRStoredProcedures.Triggers.ModifyBudget

The CREATE TRIGGER instruction in the example differs from the same instruction in the previous examples in that it contains EXTERNAL NAME parameter. This option specifies that the code is generated by the common language runtime. The name in this parameter consists of three parts. The first part specifies the name of the corresponding assembly (CLRStoredProcedures), the second specifies the name of the public class defined in the example above (Triggers), and the third specifies the name of the method defined in this class (ModifyBudget).


2024 gtavrl.ru.