Already many articles on the Internet have about SQL triggers about SQL, but I will add one more with adequate examples to consolidate the material for those who are "in the subject" and what would be better to understand the material to those who just started comprehending "Zen SQL". At the same time, and create a discussion on the topic.
Immediately make a reservation that my opinion is only my opinion, it is sometimes very categorically. Due to a number of reasons, it is necessary to work with high-loaded sites and complex web applications.
From the work on them, one valuable experience was carried out - follow priorities and statistics. What does it mean? Everything is simple: if you have a blog and he has 2-3-4-10012 million visitors per day, and the articles are written only 1-2-3435 times a day (an order of magnitude less than the number of views), then the speed of preserving the article ( and the complexity of this) relative to the indication speed of the article can be proportionally less. The more show, the show is the show, and not the preservation of the article / page / table. What does not mean that it is possible to relax. Preservation of the article for 3-5-10 seconds in the blog is within the framework of adequacy, but the generation of the page for more than 2 seconds (+ while scripts and styles with pictures will load) - it is on the verge of "what brazed site, I read something other" , And worse, I'll go buy in another place. "
If we take the average site with the voting / karma, comments, a page meter, etc., then many developers come to mind the designs like SELECT COUNT (*) from Comment Where Comment.page \u003d Page_ID. Well, think about each article to calculate the amount of the rating, the amount of comments. And we have on our main articles from each section. When attendance at 10 people per second, on average VPS, you can allow yourself 60-100 SQL queries per page (hello, Bitrix).
But to the line the lyrics (pulled already, probably). Naked data:
Table Blog.
CREATE TABLE IF Not Exists `Blog` (id` Int (11) not null auto_increment,` title` Varchar (128) Not Null, `Text` Text Not Null,` Creation` DateTime Not Null, `modification` DateTime Not Null , 'img` Varchar (128) not null default "default.png", `status` Tinyint (4) not null default" 2 ",` user_id` Int (11) Not Null, `Rate` Int (11) not null , `relax_type` Tinyint (4) Not Null,` Timers` TimeStamp Not Null Default Current_TimeStamp, `contest` Tinyint (1) Not Null Default" 0 ",` Views` Int (11) Not Null Default "0", `comment `int (11) Not Null,` URL` Varchar (128) Not Null, Primary Key (`id`), Unique Key` URL` (`URL`), Key` Country_id` (`country_id`), key` user_id `(` user_id`), Key `Status` (` Status`)) Engine \u003d Innodb Default Charset \u003d UTF8 AUTO_INCREMENT \u003d 1456435;
COMMENTS table
CREATE TABLE IF Not EXists `Comments` (` owner_name` Varchar (50) Not Null, `owner_id` Int (12) Not Null,` Int (12) Not Null AUTO_Increment, `Parent_id` Int (12) Default NULL, `user_id` Int (12) Default Null,` Text`te, `Creation` TimeStamp Null Default Current_TimeStamp,` Status` Int (1) Not Null Default "0", Primary Key (`id`), Key` owner_name` ( `owner_name`,` owner_id`), Key `Parent_id` (` Parent_id`)) Engine \u003d Myisam Default Charset \u003d UTF8 AUTO_INCREMENT \u003d 243254252;
As you can see, in the blog table, each article has a comments counter (Comment field).
Simple practice:
1. Added a comment - increased the counter for the blog
2. Removed / hid a comment - reduced the counter.
Do this in the code is convenient and habitual, but there is a more convenient tool - triggers.
And so, we have 2 events (in fact 3): Creating a comment and deletion (third event is a change in its status ("Delete", Ban, etc.).
Consider only the creation and deletion, and let the status change be a homework.
In the example there is one feature: comments may be to several types of articles.
Creating a comment:
CREATE TRIGGER `add_count_comment` After insert on` Comments` for Each Row Begin // User in the Personal Account I consider how much it comments wrote Update user set user.countcomment \u003d user.countcomment + 1 where user.id \u003d new.user_id; // Determine what the comment refers and immediately increase the counter in these tables Case New.`owner_name` What "blog" then update `blog` set` blog`.`comment` \u003d `blog`comment` + 1 WHERE` BLOG `.id \u003d new.`owner_id`; When "Article" Then Update`rticle` set `article`. `comment` \u003d` article`. `comment` + 1 Where `article`.`id` \u003d NEW.`id` When "Populateplace" Then Update` populate_place` set `populate_place`. `comment` \u003d` populate_place`. `comment` + 1 Where `populate_place`.`id` \u003d new.`owner_id`; END CASE; // Here we facilitate your job with news ribbons // URL articles you immediately write, so that then do not make samples of unnecessary Case New.`owner_name` WHEN "BLOG" THEN SETERURL \u003d (SELECT URL FROM `Blog` Where` Blog`. id \u003d new.`owner_id`); WHEN "ARTICLE" THEN SET UPERURL \u003d (Select Url from `article` Where article.id \u003d new.`owner_id`); WHEN "Populateplace" then set userurl \u003d ``; END CASE; // The name of the article is immediately writing, so if not to make a sample of Case new.`owner_name` What "blog" then set usertitle \u003d (select title from `blog`wer_id`); When "Article" Then Set UserTitle \u003d (Select Title from `Article` Where article.id \u003d new.`owner_id`); WHEN "Populateplace" Then set usertitle \u003d ``; END CASE; INSERT INTO User_HAS_EVENTS VALUES (new.user_id, new.id, "Comments", now (), userurl, usertitle); End.
Similarly and removal comment:
CREATE TRIGGER `del_count_comment` After Delete On` Comments` For Each Row Begin Update User Set user.countcomment \u003d user.countcomment -1 WHERE User.ID \u003d old.user_id; Case Old.`owner_name` What "blog" then update `blog` set` blog`.`comment` (`blog`. `comment`-1 WHERE` Blog`id`id` \u003d old.`owner_id`; When "Article" Then Update` of Article` set `article`. `comment` (` Article`. `Comment`-1 Where `article`.`id` \u003d old.`id` When "Populateplace" Then Update` populate_place` set `populate_place`.`comment` \u003d` populate_place`. `comment`-1 WHERE `Populate_Place`.`id` \u003d old.`owner_id`; END CASE; End.
And so what got:
1. When inserting a comment, we are automatically used by the SQL server, the amount of comments on a particular comment object (article, page, notes)
2. We have formed news feed (hello to all social networks, etc.)
3. When a comment is deleted, we have deduction of all data.
4. We did not use the Framework.
5. The sample of all necessary data occurs quickly (only 1 request when the page is displayed, with the exception of other "left" data on it.)
And we have a Sphinx that periodically makes the sample of articles that have changed in the last minute. To do this, the blog has a modification field.
Added trigger:
Create Trigger `Ins_Blog` Before Insert On` Blog` // Make the insertion before saving information by "substitution" of data. For Each Row Begin Set New.Modification \u003d Now (); End.
Now making the sample for the last minute we will get all the documents that have been added for the last minute.
Create Trigger `Ins_Blog` Before Update On` Blog` // Make an insertion before saving information by" substitution "of data. For Each Row Begin Set New.Modification \u003d Now (); End.
When changing data, I will update the search index too.
Usually, an average project, all that can be transferred to the SQL server side is transferred. The SQL server itself makes such operations faster and with smaller resources than can be done through the programming language used.
UPD: Cholivar devoted to the feasibility of complication of the DB structure is declared open.
Trigger:<Определение_триггера>:: \u003d (CREATE | ALTER) TRIGGER_TRIGGER name ON (Name_Table | Print name) ((for | after | instead of) ([delete] [,] [insert] [,] [Update]) [WITH APPEND] [NOT FOR Replication] AS SQL_ Operator [... n]) | ((for | after | instead of) ([,]) [with Append] [Not for Replication] [(IF UPDATE) UPDATE (and | Range_name)] [... n] | if (columns_updates () (operator_BIT_PURPACE) bit_Maska_imament) (Operator_BIT_SOB) BIT_MASK [... N]) SQL_ Operator [... n]))
The trigger can be created only in the current database, but it is allowed to circulate within the trigger to other databases, including located on a remote server.
Consider the appointment of arguments from the CREATE | Alter Trigger.
The trigger name must be unique within the database. Additionally, you can specify the name of the owner.
When specifying the WITH ENCRYPTION argument, the server encrypts the trigger code so that no one, including administrator, cannot access it and read it. Encryption is often used to hide the copyright data processing algorithms, which are the intellectual property of a programmer or a commercial secret.
Types of triggers
In SQL Server, there are two parameters that determine the behavior of triggers:
- After. The trigger is performed after the successful execution of his commands. If the commands for any reason cannot be successfully completed, the trigger is not executed. It should be noted that data changes as a result of the execution of the user's request and the execution of the trigger is carried out in the body of a single transaction: if a trigger rollback occurs, and user changes will be rejected. You can define several AfterTriggers for each operation (INSERT, UPDATE, DELETE). If the table is provided for the execution of several AfterTriggers, using the SP_SETTRIGGERORDER system stored procedure, you can specify which one will be performed first and what is the last. By default in SQL Server, all triggers are after-triggers.
- Instead of. The trigger is called instead of execution of commands. In contrast to Afteradrigger, Instead of -trigger can be defined both for the table and for the presentation. For each INSERT, UPDATE operation, you can define only one instead of -trigger.
Triggers distinguish the type of commands to which they react.
There are three types of triggers:
- INSERT TRIGGER - start when you try to insert data using the INSERT command.
- Update Trigger - Start when you try to change data using the UPDATE command.
- Delete Trigger - Start when trying to delete data using the Delete command.
Designs [Delete] [,] [insert] [,] [Update] and For | After | Instead of) ([,] Determine which command to react trigger. When it creates it must be specified at least one team. Allowed creating a triggerresponding to two or all three teams.
The WITH APPEND argument allows you to create several triggers of each type.
For creation of trigger With the Not for Replication argument, it is prohibited to start it during the execution of table modification of replication mechanisms.
The design AS SQL_ operator [... n] defines a set of SQL operators and commands that will be executed when the trigger is started.
It should be noted that a number of operations are not allowed inside the trigger, such as:
- creation, change and delete database;
- restoring a backup database or transaction log.
The execution of these commands is not permitted, since they cannot be canceled if the transaction roll back in which the trigger is performed. This ban is unlikely to somehow affect the functionality of the triggers created. It is difficult to find such a situation where, for example, after changing the row of the table, you will need to restore the backup of the transaction log.
Trigger programming
When performing adding commands, change and delete records, the server creates two special tables: inserted. and deleted.. They contain lines of strings that will be inserted or deleted upon completion of the transaction. The structure of the inserted and deleted table is identical to the table structure for which the trigger is determined. For each trigger, a set of tables inserted and deleted is created, so no other trigger can access them. Depending on the type of operation that caused the trigger execution, the contents of the inserted and deleted tables can be different:
- the insert command - in the inserted table contains all the lines that the user tries to insert into the table; There will be no lines in the Deleted table; After the trigger is completed, all rows from the Inserted table will move to the source table;
- dELETE command - in the Deleted table will contain all the lines that the user will try to remove; The trigger can check each line and determine whether its removal is allowed; Inserted table will not be any line;
- update command - When executing it in the Deleted table, there are old rows values \u200b\u200bthat will be deleted upon successful completion.
Trigger (databases)
Trigger (eng. trigger.) - This is the stored procedure of a special type that the user does not cause directly, and the execution of which is due to the action by modifying the data: by adding Insert, delete a delete line in a specified table, or by changing the UPDATE data in a specific column of a specified relational database table. Triggers are applied to ensure the integrity of the data and the implementation of complex business logic. Trigger runs the server automatically when you try to change the data in the table with which it is connected. All data modifications produced by them are considered as performed in the transaction in which the action has been performed that caused the triggering of the trigger. Accordingly, in the case of a detection of an error or disruption of data integrity, this transaction rollback may occur.
The moment of trigger starts is determined using the Before keywords (trigger starts to perform the associated event; for example, before adding recording) or After (after the event). In case the trigger is called before the event, it can make changes to the event modified by the event (of course, provided that the event is not to delete the record). Some DBMS impose restrictions on operators that can be used in a trigger (for example, it may be forbidden to make changes to the table on which "hanging" trigger, etc.)
In addition, the triggers can be tied to the table, but to the representation (view). In this case, with their help, the mechanism of "updated representation" is implemented. In this case, the BEFORE and AfterTer keywords only affect the sequence of trigger calls, since the event itself (delete, insert or update) does not occur.
In some servers, triggers can be called not for each modifiable record, but once to change the table. Such triggers are called tablet.
Example (Oracle):
/ * Trigger at the table level * / CREATE OR REPLACE TRIGGER DISTRICTUPDATTTRIGGER AFTER UPDATE ON DISTIRT BEGIN INSERT INTO INFO VALUES ( "Table" District "Has Changed"); End;
In this case, for the differences in tabular triggers, additional keywords are introduced from the lowercase when describing the line triggers. In Oracle, this is a phrase for Each Row.
/ * Trigger at string level * / CREATE OR REPLACE TRIGGER DISTRICTUPDATTTRIGGER AFTER UPDATE ON DISTRICT FOR EACH ROW BEGIN INSERT INTO INFO VALUES ( "One String In Table" District "Has Changed"); End;
Wikimedia Foundation. 2010.
- Household
- Spectroscopy
Watch what is "trigger (databases)" in other dictionaries:
Presentation (databases) - This term has other meanings, see the presentation. Presentation (eng. View, more consonant is not the standard name "View", in Slane programmers is often used as a borrowing from the British "Bind", "Blind") ... ... Wikipedia
Hierarchical databases - The database hierarchical model consists of objects with pointers from parent objects to descendants, connecting together related information. Hierarchical databases can be represented as a tree consisting of objects of various levels. ... ... Wikipedia
Relational databases - Relational database Database based on a relational data model. The word "relational" comes from the English. Relation. Relational databases are used to work with relational databases. The use of relational databases was ... ... Wikipedia
Index (databases) - This term has other values, see the index. Index (English Index) The database object created in order to improve data search performance. Tables in the database may have a large number of lines that are stored in ... Wikipedia
Cursor (database) - This term has other meanings, see the cursor (values). Cursor Reference to the context memory area [Source is not specified 126 days]. In some implementations of the information logical language SQL (Oracle, ... ... Wikipedia
Trigger (values) - trigger (eng. Trigger in the meaning of a noun "doggy, a latch, a trigger in a general sense, leading to an element in action"; in the meaning of the verb "acting"): In Russian, the term is initial from the region ... ... Wikipedia
Database refactoring - (eng. Database Refactoring) This is a simple change in the database schema, which helps to improve its project while maintaining functional and information semantics. In other words, the consequence of the database refactoring can not be ... ... Wikipedia
Database - Request "BD" is redirected here; See also other values. Database presented in an objective form A combination of independent materials (articles, calculations, regulations, court decisions and other similar materials), ... ... Wikipedia
Database Design - The process of creating a database schema and determining the necessary integrity restrictions. Contents 1 Basic Database Design Tasks ... Wikipedia
Data model - In the classic database theory, the data model is a formal theory of presentation and data processing in the database management system (DBMS), which includes at least three aspects: 1) aspect of the structure: methods describing types and ... ... Wikipedia
Trigger Definition in SQL Language Standard
Triggers are one of the varieties of stored procedures. Their execution occurs when it is executed for the table of any data manipulation language operator (DML). Triggers are used to verify the integrity of the data, as well as to roll back transactions.
The trigger is a compiled SQL procedure, the execution of which is due to the onset of certain events within the relational database. The use of triggers is mostly convenient for database users. And yet their use is often associated with additional resource costs on I / O operations. In the event that the same results (with much less non-production costs of resources) can be achieved using stored procedures or application programs, the use of triggers is inappropriate.
Triggers - a special SQL server tool used to maintain the integrity of data in the database. With the help of integrity restrictions, rules and default values, it is not always possible to achieve the desired level of functionality. It is often necessary to implement complex data verification algorithms that guarantee their accuracy and reality. In addition, it is sometimes necessary to track changes in the table values \u200b\u200bto correctly change the associated data. Triggers can be considered as a kind of filters entering into action after performing all operations in accordance with the rules, standard values, etc.
The trigger is a special type of stored procedures running the server automatically when you try to change data in tables with which triggers are connected. Each trigger is tied to a specific table. All data modifications produced by them are treated as one transaction. In case of error detection or disruption of data integrity, this transaction roll back. Thereby making changes prohibited. All changes already made by trigger are canceled.
Creates a trigger only the database owner. This limitation allows you to avoid accidental change in the structure of tables, communication methods with other objects, etc.
The trigger is a very useful and at the same time a dangerous agent. Thus, with incorrect logic of its work, you can easily destroy a whole database, so the triggers need to be very carefully debugged.
Unlike the usual subprogramme, the trigger is imposed implicitly in each case trigger Event, moreover, he has no arguments. Bringing it into action is sometimes called the trigger launch. With the help of triggers, the following goals are achieved:
- checking the correctness of the entered data and performing complex data integrity restrictions that are difficult if it is generally possible to maintain with the help of the integrity constraints installed for the table;
- issuance of warnings that resemble the need to perform certain actions when updating the table, implemented in a certain way;
- accumulation of audit information by fixing information about changes and those who have completed them;
- replication support.
The main format of the CREATE TRIGGER command is shown below:
<Определение_триггера>:: \u003d CREATE TRIGGER Name_Trigger Before | After<триггерное_событие> ON.<имя_таблицы> <тело_триггера>
trigger events Consist from inserting, deleting and updating strings in the table. In the latter case for trigger Event You can specify the specific names of the table columns. The trigger start time is determined using the Before keywords (trigger starts to perform the events associated) or After (after their execution).
The actions performed by the trigger are set for each row (for Each Row) covered by this event, or only once for each event (for Each Statement).
Designation <список_старых_или_новых_псевдонимов> Refers to such components as an old or new line (Old / New) or an old or new table (Old Table / New Table). It is clear that the old values \u200b\u200bare not applicable for insert events, and new ones - for deletion events.
Subject to proper use, triggers can become a very powerful mechanism. The main advantage of their advantage is that standard functions are stored inside the database and are consistently activated each time it is updated. It can significantly simplify applications. Nevertheless, the disadvantages inherent in the trigger should be mentioned:
- difficulty: When moving some functions, the tasks of its design, implementation and administration are complicated;
- hidden functionality: transfer of part of functions to the database and save them in the form of one or more triggers sometimes leads to hiding from the user of some functionality. Although it simplifies it to a certain extent, but, unfortunately, it may cause unplanned, potentially unwanted and harmful side effects, since in this case the user is not able to control all processes occurring in the database;
- impact on performance: Before performing each command to change the status of the DBMS database, you must check the trigger condition in order to find out the need to start the trigger for this command. The execution of such calculations affects the overall performance of the DBMS, and at the moments of the peak load, its decrease can be particularly noticeable. Obviously, with an increase in the number of triggers, overhead costs associated with such operations increase.
Incorrectly written triggers can lead to serious problems, such as the appearance of "dead" locks. Triggers are capable of blocking many resources for a long time, so special attention should be paid to minimizing access conflicts.
Implementation of triggers in MS SQL Server MS
In the implementation of the MS SQL Server DBMS, the following operator of creating or change trigger is used:
<Определение_триггера>:: \u003d (CREATE | ALTER) TRIGGER_TRIGGER name ON (name_name | View_name) ((for | after | instead of) ([delete] [,] [insert] [,] [update]) [with Append] [NOT FOR Replication] AS SQL_ Operator [... n]) | ((for | after | instead of) ([,]) [with Append] [Not for Replication] [(IF UPDATE) UPDATE (and | Range_name)] [... n] | if (columns_updates () (operator_BIT_PURPACE) bit_Maska_imament) (Operator_BIT_SOB) BIT_MASK [... N]) SQL_ Operator [... n]))
The trigger can be created only in the current database, but it is allowed to circulate within the trigger to other databases, including located on a remote server.
Consider the appointment of arguments from the CREATE | Alter Trigger.
The trigger name must be unique within the database. Additionally, you can specify the name of the owner.
When specifying the WITH ENCRYPTION argument, the server encrypts the trigger code so that no one, including administrator, cannot access it and read it. Encryption is often used to hide the copyright data processing algorithms, which are the intellectual property of a programmer or a commercial secret.
Types of triggers
In SQL Server, there are two parameters that determine the behavior of triggers:
- After. The trigger is performed after the successful execution of his commands. If the commands for any reason cannot be successfully completed, the trigger is not executed. It should be noted that data changes as a result of the execution of the user's request and the execution of the trigger is carried out in the body of a single transaction: if a trigger rollback occurs, and user changes will be rejected. You can define several AfterTriggers for each operation (INSERT, UPDATE, DELETE). If the table is provided for the execution of several AfterTriggers, using the SP_SETTRIGGERORDER system stored procedure, you can specify which one will be performed first and what is the last. By default in SQL Server, all triggers are after-triggers.
- Instead of. The trigger is called instead of execution of commands. In contrast to AfterTer -Trigger, Instead of -trigger can be defined both for the table and for viewing. For each INSERT, UPDATE operation, you can define only one instead of -trigger.
Triggers distinguish the type of commands to which they react.
There are three types of triggers:
- INSERT TRIGGER - start when you try to insert data using the INSERT command.
- Update Trigger - Start when you try to change data using the UPDATE command.
- Delete Trigger - Start when trying to delete data using the Delete command.
Designs [Delete] [,] [insert] [,] [Update] and For | After | Instead of) ([,] Determine which command to react trigger. When it creates it must be specified at least one team. Allowed creating a triggerresponding to two or all three teams.
The WITH APPEND argument allows you to create several triggers of each type.
For creation of trigger With the Not for Replication argument, it is prohibited to start it during the execution of table modification of replication mechanisms.
The design AS SQL_ operator [... n] defines a set of SQL operators and commands that will be executed when the trigger is started.
It should be noted that a number of operations are not allowed inside the trigger, such as:
- creation, change and delete database;
- restoring a backup database or transaction log.
The execution of these commands is not permitted, since they cannot be canceled if the transaction roll back in which the trigger is performed. This prohibition is unlikely to somehow affect the functionality of the triggers created. It is difficult to find such a situation where, for example, after changing the row of the table, you will need to restore the backup of the transaction log.
Trigger programming
When performing adding commands, change and delete records, the server creates two special tables: inserted. and deleted.. They contain lines of strings that will be inserted or deleted upon completion of the transaction. The structure of the inserted and deleted table is identical to the table structure for which the trigger is determined. For each trigger, a set of tables inserted and deleted is created, so no other trigger can access them. Depending on the type of operation that caused the trigger execution, the contents of the inserted and deleted tables can be different:
- the insert command - in the inserted table contains all the lines that the user tries to insert into the table; There will be no lines in the Deleted table; After the trigger is completed, all rows from the Inserted table will move to the source table;
- dELETE command - in the Deleted table will contain all the lines that the user will try to remove; The trigger can check each line and determine whether its removal is allowed; Inserted table will not be any line;
- update command - When executing it in the Deleted table, there are old rows values \u200b\u200bthat will be deleted when the trigger is successfully completed. New rows values \u200b\u200bare contained in the inserted table. These lines will be added to the source table after the successful execution of the trigger.
For information on the number of rows, which will be changed when the trigger is successfully completed, you can use the @@ ROWCOUNT function; It returns the number of rows treated with the last command. It should be emphasized that the trigger is launched without trying to change the specific string, and at the time of executing the change command. One such team affects the set of lines, so the trigger should process all these lines.
If the trigger found that from 100 inserted, variable or deleted strings, only one does not satisfy with one or another, then no string will be inserted, changed or deleted. Such behavior is due to the requirements of the transaction - either all modifications or any one must be performed.
The trigger is performed as an implicitly defined transaction, so the transaction control commands are allowed inside the trigger. In particular, when a violation of integrity constraints for interrupting the trigger execution and the cancellation of all changes that tried to execute the user, you must use the Rollback Transaction command.
To obtain a list of columns, modified when performing Insert or Update commands that caused the trigger execution, you can use the columns_updated () function. It returns a binary number, each bit of which, starting with the younger, corresponds to one table column (in order of the columns when creating a table). If the bit is set to "1", the corresponding column has been changed. In addition, the fact of changing the column determines and the Update feature (Library_name).
For trigger removal Used command
Drop Trigger (NameTrigger) [, ... n]
We give examples of using triggers.
Example 14.1. Using a trigger for implementation of limitations. In the record added to the table, the number of sold goods sold should be no less than its residue from the warehouse table.
The entry command in the Table of the transaction may be, for example, such:
INSERT INTO Transaction Values \u200b\u200b(3.1, -299, "01/08/2002")
The trigger created must respond to its execution as follows: It is necessary to cancel the command if the value of the goods remained less than the goods sold with the code entered (in the example of the product code \u003d 3). In the insertion record, the number of goods is indicated with the "+" sign, if the goods are supplied, and with the sign "-", if it is sold. The trigger presented is configured to process only one recording added.
CREATE TRIGGER trigger_ins ON deal for insert as if @@ rowcount \u003d 1 begin if not exists (select * from inserted where -inserted.4<=ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "Отмена поставки: товара на складе нет" END END Example 14.1. Using a trigger to implement the limitations on the value.
Example 14.2. Using a trigger to collect statistical data.
Create a trigger to process an entry insert operation to a transaction table, for example, such a command:
INSERT INTO VALUES transaction (3,1,200, "01/08/2002")
completed goods with code 3 from the client with code 1 in the amount of 200 units.
When selling or obtaining goods, it is necessary to change the amount of its warehouse stock. If there is no product in stock yet, you need to add the appropriate entry to the warehouse table. The trigger processes only one added string.
Alter trigger trigger_ins ON transaction for insert as declare @x int, @y int if @@ rowcount \u003d 1 - in the Table Transaction adds recording - by the delivery of goods Begin - Nity of the sold goods must be not - simply than his balance From the table Warehouse IF Not Exists (Select * from Inserted Where -inserted.Number< =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 Остаток=остаток[Email Protected] Where Codtowder [Email Protected] End End. Example 14.2. Using a trigger to collect statistical data.
Example 14.3. Create a trigger for processing a recording deletion operation from a transaction table, for example, such a command:
For the product, the code of which is specified when removing the record, it is necessary to adjust its residue in the warehouse. The trigger processes only one remote entry.
CREATE TRIGGER TRIGGER_DEL ON DELETE AS IF @@ ROWCOUNT \u003d 1 - one Begin Declare @y int, @ x int - determines the code and number of goods from the table from the table SELECT @ Y \u003d Codovar, @ x \u003d Quantity from deleted - in the table warehouse adjusts the number of --ovar Update store SET residue \u003d residue [Email Protected] Where Codtowder [Email Protected] End. Example 14.3. Trigger for processing a removal of recording from the table
Example 14.4. Create a trigger to process the recording change operation in the Table of Transaction, for example, by such a command:
in all transactions with a product that has a code equal to 3, reduce the amount of goods by 10 units.
The specified command can lead to a change in several entries at the Table of Transaction. Therefore, we show how to create a trigger processing not one record. For each changed recording, it is necessary to reduce the balance of the goods in stock by the value of the value of the goods in stock and for the new (after change) of the product to the value of the product and for the new (after change) of the product code to increase its balance in the magnitude of the new (after change). To handle all the changed records, enter cursors in which you save all old (from the Deleted table) and all new values \u200b\u200b(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 \u200b\u200bof Declare Cur1 Cursor for Select Codovar, quantity from inserted - cursor with old DECLARE CUR2 CURSOR FOR Select Codovar, Quantity from Deleted Open Cur1 Open CUR2 - Move in parallel on both Fetch Next from Cur1 into @x cursors, @y fetch next from cur2 into @x_old, @y_old while @@ fetch_status \u003d 0 begin - for the old product code It decreases its - acquirement in stock Update warehouse SET residue \u003d residue [Email Protected]_old WHERE Kodtower [Email Protected]_old - for a new product code, if there is no such product in stock, a new record If Not Exists is entered (Select * from Where Kodtovar Warehouse [Email Protected]) INSERT INTO warehouse (codovar, residue) Values \u200b\u200b(@ x, @ y) else --iner for a new product code increases the number in stock update warehouse SET residue \u003d residue [Email Protected] Where Codtowder [Email Protected] Fetch Next From Cur1 Into @x, @y fetch next from cur2 inte @x_old, @y_old end close Cur1 Close Cur2 Deallocate Cur1 Deallocate CUR2 Example 14.4. Trigger for processing recording changes in the table
In the considered trigger, there is no comparison of the number of goods when a change in the record of the transaction with its residue in the warehouse.
Example 14.5. Let's fix this flaw. To generate an error message, use the MS SQL Server Raiserror command in the trigger body, the arguments of which are the text of the message, the 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, @ @ @ @y_old int, @ int declare Cur1 Cursor for Select Codovar, Quantity from Inserted Declare Cur2 Cursor for Select Codovar, Number 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 \u003d 0 begin select @ o \u003d residue from Where Kodtovar [Email Protected] If @o.<[Email Protected] Begin Raiserror ("Rangered", 16.10) Close Cur1 Cur2 DEALLOCATE CUR1 DEALLOCATE CUR22 ROLLBACK TRAN RETURN END UPDATE WALKE SET PALE \u003d residue [Email Protected]_old WHERE Kodtower [Email Protected]_old If Not Exists (Select * from Warehouse Whery Codovar [Email Protected]) Insert Into Warehouse (Codewood, Residue) Values \u200b\u200b(@ X, @ Y) Else Update SET Warehouse Residue \u003d Residue [Email Protected] Where Codtowder [Email Protected] Fetch Next From Cur1 Into @x, @y fetch next from cur2 inte @x_old, @y_old end close Cur1 Close Cur2 Deallocate Cur1 Deallocate CUR2 Example 14.5. Corrected trigger version for processing recording changes in the table
Example 14.6. In the example, the cancellation of all changes occurs if it is impossible to realize at least one of them. Create a trigger that allows you to cancel changing only some records and perform the change in the rest.
In this case, the trigger is executed not after changing the records, but instead of the change command.
Alter Trigger Trigger_Upd ON TRANSFER 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 Codes, Kodtovar, Quantity from Inserted Declare CUR2 CURSOR FOR SELECT Codes, Kodtovar, 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 \u003d 0 begin select @ o \u003d residue from wovel Kodtovar [Email Protected] If @o\u003e [Email Protected] Begin Raiserror ("Change", 16.10) Update Transaction SET Quantity [Email Protected], Product code [Email Protected] Wheredelka [Email Protected] Update Warehouse SET residue \u003d residue [Email Protected]_old WHERE Kodtower [Email Protected]_old If Not Exists (Select * from Warehouse Whery Codovar [Email Protected]) Insert Into Warehouse (Codewood, Residue) Values \u200b\u200b(@ X, @ Y) Else Update SET Warehouse Residue \u003d Residue [Email Protected] Where Codtowder [Email Protected] 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 cancel changing only some records and perform the change in the rest.
IT APPLIES TO:SQL Server (starting in 2008) SQL Azure Pack SQL AzureParallel Data Warehouse Database
Creates a data processing trigger, DDL or input. The trigger is a special kind of stored procedure performed automatically when an event occurs on the database server. Data processing triggers are run by events caused by a user attempt to change data using a data processing language. DML events are INSERT, UPDATE or DELETE procedures applied to the table or representation. These triggers are triggered when you start any valid event, regardless of whether it affects any rows of the table.
DDL triggers are triggered in response to a number of data description languages \u200b\u200b(DDL). These events primarily comply with TRANSACT-SQL CREATE, ALTER, DROP instructions and some system stores that perform similar to DDL operations. Input triggers can be triggered in response to the Logon event that occurs during the installation of custom sessions. Triggers can be created directly from Transact-SQL instructions or methods of assemblies created in Microsoft .NET Framework Companies (CLR) and transmission to an instance of SQL Server. SQL Server allows you to create multiple triggers for any instruction.
SQL Server Syntax - Trigger On An Insert, Update, OR Delete Statement to A Table or View (DML Trigger) Create [OR ALTER] TRIGGER [SCHEMA_NAME. ] TRIGGER_NAME ON (Table | View) [WITH
SQL Server Syntax - Trigger On An Insert, Update, Or Delete Statement to A Table (DML Tables) Create [OR ALTER] TRIGGER [SCHEMA_NAME. ] TRIGGER_NAME ON (Table) [WITH
Trigger ON CREATE, ALTER, DROP, GRANT, DENY, REVOKE OR UPDATE STATEMENT (DDL TRIGGER) CREATE [OR ALTER] TRIGGER TRIGGER_NAME ON (ALL Server | Database) [WITH
Trigger On A Logon Event (Logon Trigger) CREATE [OR ALTER] TRIGGER TRIGGER_NAME ON ALL SERVER [WITH
Windows Azure SQL Database Syntax - TRIGGER ON AN INSERT, UPDATE, OR DELET STATEMENT TO A TABLE OR VIEW (DML TRIGGER) CREATE [OR ALTER] TRIGGER [SCHEMA_NAME. ] TRIGGER_NAME ON (Table | View) [With
Windows Azure SQL Database Syntax - TRIGGER ON CREATE, ALTER, DROP, GRANT, DENY, REVOKE, OR UPDATE STATISTICS STATEMENT (DDL TRIGGER) CREATE [OR ALTER] TRIGGER TRIGGER_NAME ON (Database) [With
Condifly changes the trigger only if it already exists.
schema_Name.
The name of the diagram belongs to the DML trigger. The DML trigger action is limited to the table area of \u200b\u200bthe table or representation for which they are created. schema_Name. Cannot be specified for DDL triggers or input.
name_Trigger
The name of the trigger. An object name_Trigger must comply with the rules for, except that name_Trigger can't start with characters # or ##.
table | vIEW.
A table or representation in which the DML trigger is performed, sometimes indicated as a trigger table or a trigger representation. Note the specified table name or submission is not mandatory. Only the Instead of Triger may refer to the presentation. DML triggers cannot be described in local or global temporary tables.
Database
Applies DDL trigger scope to the current database. If specified, the trigger works whenever event_Type. or event_group occurs in the current database.
Applies a DDL trigger scope or entry trigger to the current server. If specified, the trigger works whenever event_Type. or event_group There is any place on the current server anywhere.
Darkens the text of the CREATE TRIGGER instruction. Using the WITH Encryption parameter does not allow you to publish a trigger as part of the SQL Server replication. The WITH Encryption parameter cannot be specified for CLR triggers.
Execute As.
Specifies the security context in which the trigger is performed. Allows you to manage the user account used by the SQL Server instance to verify permission to any database objects referred to the trigger.
For more information, see section.
Native_Compilation
Indicates that the trigger is compiled in its own code.
This parameter is mandatory for triggers in tables optimized for memory.
Schembinding.
It ensures that the tables referenced by the trigger cannot be removed or changed.
This parameter is mandatory for triggers in tables optimized for memory and is not supported for triggers in conventional tables.
For | After
The AFTER type indicates that the DML trigger is triggered only after successfully executing all operations in the SQL instruction, launched by a trigger. All cascade actions and checks for the limitations on which there is a link must be successfully completed before the trigger will work.
If the only specified keyword is for, the Aftermage argument is used by default.
AFTER triggers cannot be defined on ideas.
Instead Of.
Indicates that the DML trigger is performed instead Running the SQL statement, therefore, override the action of the starting instructions. The instead of argument cannot be specified for DDL triggers or entry triggers.
For each Insert, Update or Delete instruction in the table or view, no more than one trigger instead of may be determined. However, it is possible to determine the views on the views where each view has its own TRIGER INSTEAD OF.
Using Instead of Triggers is not allowed in supporting submissions that use the WITH CHECK OPTION parameter. SQL Server causes an error if the Instead of Trigger is added to the support support with the WITH CHECK OPTION parameter. The user must delete this parameter using the Alter View instruction before determining the ASTEAD OF TRIGGEG.
([DELETE] [,] [INSERT] [,] [UPDATE])
Defines the data change instructions for which the DML trigger is triggered if it is applied to the table or representation. You must specify at least one instruction. In definition of the trigger, any of their combinations in any order are allowed.
For the Instead of Instead of the Delete parameter is not allowed in tables having a reference connection with a cascading action on Delete. Similarly, the UPDATE parameter is not allowed in tables that have a reference connection with a cascade action on UPDATE.
Indicates that an existing type trigger is required. The WITH APPEND argument cannot be used for instead of triggers or with explicitly specifying the Afterter trigger. The WITH APPEND argument can only be used when specifying the FOR parameter without instead of or after for reasons of backward compatibility support. The WITH APPEND argument cannot be specified if the External Name parameter is specified (in the case of a CLR trigger).
event_Type.
The name of the TRANSACT-SQL language event, which after execution causes the trigger DDL trigger. Valid events for DDL triggers are listed in.
event_group
The name of the standard TRANSACT-SQL language event group. DDL trigger is triggered after performing any transact-SQL language events to which belongs event_group. The list of events groups for DDL triggers are listed in.
After completing the work, the CREATE TRIGGER instructions event_group Also functions as a macro, adding the events of the corresponding types to the SYS.TRIGGER_EVENTS directory view.
Not for Replication
Indicates that the trigger cannot be executed if the replication agent changes the table used by the trigger.
sQL_STATEMENT.
Conditions and actions of the trigger. The trigger conditions indicate additional criteria that determine which events - DML, DDL, or input event - cause triggering trigger.
The actions of the trigger specified in the TRANSACT-SQL language instructions come into force after trying to use the operation.
Triggers may contain any number of TRANSACT-SQL languages \u200b\u200bof any type, for some exceptions. For more information, see the "Notes" subsection. Triggers are designed to control or change data based on modification or data definition instructions; They do not return any data to the user. Transact-SQL often contain instructions in trigger flow control language.
DML triggers use logical (conceptual) tables DELETED and INSERED. By its structure, they are similar to the table on which a trigger is defined, that is, the table to which the user action applies. The DELETED and INSERED tables contain old or new rows values \u200b\u200bthat can be changed by user actions. For example, you can use the instructions to request all values \u200b\u200bof the DELETED table:
SELECT * from deleted;
For more information, see section.
DDL triggers and logon collection of information on events running using the function. For more information, see section.
SQL ServeRells update text, nTEXT., or picture The column trigger with the help of instead of for tables or representations.
For triggers in tables optimized for the unique sQL_STATEMENT. Allowed at the top level of the atomic unit. It is allowed inside the Atomic T-SQL block, it is limited to inside the T-SQL's own procedure.
< method_specifier >
Indicates the assembly method for binding to the CLR-trigger. This method should not receive arguments and return Void values. class_name. Must be a valid SQL Server identifier and exist as a class in assembly assembly. If the class has a name containing points (.) To separate the namespace parts, the class name must be enclosed in square brackets () or double quotes (""). Class can not be embedded.
DML triggers are often used to apply business rules and data integrity. SQL Server Declarative reference integrity restriction is provided by the ALTER TABLE and CREATE TABLE instructions. However, the declarative limitation of reference integrity does not provide reference integrity between databases. Limiting reference integrity implies the execution of communication rules between the primary and external keys of the tables. To enable reference integrity restrictions, use PRIMARY KEY and FORGN KEY in the ALTER Table and Create Table Table. If the restrictions apply to the trigger table, they are checked after the ASTEAD OF TRIGGEER is triggered and before executing the Afterer trigger. In case of violation of the restriction, the actions of the Trigger Instead of, and the after trigger does not work.
The first and last AFTER triggers, which will be performed in the table, can be defined using the sp_settriggerorder procedure. For a table, you can define only one first and one last trigger for each of the INSERT, UPDATE and DELETE operations. If there are other AFTER triggers in the table, they will be performed randomly.
If the alter trigger manual changes the first or last trigger, the first or last set of attributes of the modified trigger is deleted, and the sorting order must be set to the SP_SETTRIGGERORDER procedure.
The after trigger is performed only after the trigger calling the SQL instruction has been successfully completed. Successful implementation also implies the completion of all reference cascade actions and verification of restrictions related to modified or remote objects. The AFTER trigger does not cause the confursive triggering trigger instead of in the same table.
If the Instead of the Instead of defined for the table performs in relation to the table any instruction that again caused the trigger response to Instead of, the trigger is not recursively called. Instead, the instruction is processed as if the table had no configuration of the Instead of, and the sequence of restrictions begins and executing the Afterer trigger. For example, if a trigger is defined as an Instead of Insert trigger for a table and executes Insert instructions for the same table, the Insert instruction does not cause a new trigger operation. The insert command performed by the trigger begins the process of applying restrictions and platform of all AFTER insert triggers defined for this table.
If the Instead of the Instead of defined for the presentation performs with respect to the view any instruction that the Instead Ofa's trigger responds again, the trigger is not recursively called. Instead, the instruction makes a change in the basic tables on which the view is based. In this case, the definition should satisfy all restrictions set for updated representations. Definition of updated views, see section.
For example, if the trigger is defined as an instead of update to represent and executes the Update instruction for the same view, the UPDATE instruction executed by the trigger does not cause a new trigger operation. Update instruction performed in the trigger processes the idea as if the presentation had no instead of the trigger. Columns, modified using the UPDATE instruction, should belong to one basic table. Each modification of the basic table causes the use of a sequence of restrictions and the platoon of the response after defined for this table.
Check actions Update or Insert instructions on specified columns
Transact-SQL trigger can be constructed to perform specific actions based on changing certain columns using UPDATE or INSERT instructions. Use or in the trigger body for this purpose. The UPDATE () design checks the UPDATE or INSERT instructions on one column. Using the columns_updated design, the UPDATE or INSERT instructions, conducted on several columns, are checked, and a bit template is returned, showing which columns are inserted or updated.
Trigger restrictions
The CREATE TRIGGER instruction should be the first instruction in the package and can only be applied to one table.
The trigger is created only in the current database, but may, nevertheless, contain references to objects outside the current database.
If the diagram name is specified to refine the trigger, the table name must be clarified in the same way.
The same trigger action can be defined by more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER instruction.
Instead of Delete / Update Triggers cannot be defined for a table that has an external key defined for a cascading execution of the DELETE / UPDATE operation.
Inside the trigger, any SET instruction can be used. The selected set parameter remains in force during the execution of the trigger, after which the settings are returned to the previous state.
During trigger trigger, the results are returned by the calling application as well as in the case of stored procedures. To prevent the resulting triggering of the trigger, the return results return should not include the SELECT instructions that return the result, or the instructions that are performed in the trigger assignment of variables. Trigger containing either SELECT instructions that return the results to the user or instructions that assign variables requires special circulation; These returned results must be overwritten into all applications in which the trigger table changes are allowed. If a variable assignments in the trigger, you should use the SET NOCOUNT statement at the beginning of the trigger to prevent any result sets.
Although the TRUNCATE TABLE instruction is in its essence is the Delete instruction, it does not activate the trigger, since the operation does not record the removal of individual lines. However, it is necessary to worry about the random breakage of the DELETE trigger in this way only users with permissions for executing the TRUNCATE TABLE instruction.
Instruction WriteText (with logging and without it) does not start triggers.
The following TRANSACT-SQL language instructions are not allowed in DML triggers:
DDL triggers, as well as standard triggers, perform stored procedures in response to any event. Unlike standard triggers, they are not triggered in response to the execution of UPDATE, INSERT or Delete instructions relative to the table or representation. Instead, triggers are triggered first in response to data definition language instructions (DDL). These are instructions Create, Alter, Drop, Grant, Deny, Revoke and Update Statistics. System stored procedures performing operations such as DDL operations can also run DDL triggers.
For more information about DDL triggers, see section.
DDL triggers are not triggered in response to events affecting local or global temporary tables and stored procedures.
In contrast to DML triggers, DDL triggers are not limited to the schema area. Therefore, to request metadata about DDL triggers, you cannot use such functions as Object_ID, Object_name, ObjectProperty and ObjectPropertyex. Use the directory presentation instead. For more information, see section.
Input triggers perform stored procedures in response to the Logon event. This event is called when installing a custom session with a SQL Server instance. Input triggers are triggered after the completion of the authentication phase when logging in, but before the user session is actually established. Consequently, all messages that occur within the trigger usually reach the user, such as error messages and messages from the PRINT instruction, are redirected to the SQL Server error log. For more information, see section.
If authentication fails, the input triggers are not triggered.
Input triggers are not supported distributed transactions. If the entry trigger is triggered, containing a distributed transaction, an error 3969 is returned.
Turning off the entrance trigger
The input trigger can effectively disable connections to the Database Engine component for all users, including members of the predefined server role. sysadmin. . If the input trigger prohibits connections, members of the predefined server role sysadmin. Can be connected using a selected administrative connection or by calling the Database Engine component in the minimum configuration mode (-f). For more information, see section.
Return results
The ability to return results from triggers will be excluded from the following version of SQL Server. Triggers returning resulting sets can lead to unexpected behavior of applications that are not intended for working with them. Do not use triggers in developed applications that return result sets and schedule the change in applications that use them at present. For triggers to return the resulting sets, set the value 1.
Input triggers always prohibit the refund of the resulting sets, and this behavior cannot be configured. If the input trigger generates a resulting set, then the trigger is not performed and the entry attempt caused by the trigger is prohibited.
A few triggers
SQL Server allows you to create multiple triggers for each DML, DDL and Logon event. For example, if the CREATE TRIGGER FOR UPDATE manual is performed in a table already having a UPDATE trigger, an update trigger is additionally created. In earlier versions of SQL Server, only one trigger was allowed in each table for each data change event insert, Update or Delete.
Recursive triggers
SQL Server permits recursive trigger call, if using the Alter Database instruction, the recursive_triggers setting is enabled.
The following types of recursion may occur in recursive triggers:
Indirect recursion
With an indirect recursion, the application updates T1 table. This event causes tr.1 trigger trigger that updates T2 table. This causes trigger T2 trigger and updating T1 table.
Direct recursion
With direct recursion, the application updates T1 table. This event causes tri1 trigger response that updates T1 table. Since T1 table has already been updated, the trig trigger trigs again and so on.
The following example uses both types of recursion: straight and indirect. Suppose the T1 table defines two trigger: TR1 and TR2. TR1 trigger recursively updates T1 table. Update instruction performs each of the triggers TR1 and TR2 once. In addition, the trigger tri1 trigger trigger trigger trigger (recursively) and tr2. The inserted and deleted trigger tables contain lines that only apply to the UPDATE instructions that caused the trigger trigger.
Disable the Recursive_Triggers settings prevents only direct recursion. To disable the indirect recursion, using the SP_CONFIGURE stored procedure, assign the NESTED TRIGGERS server to 0.
If one of the triggers performs the Rollback Transaction instruction, no other triggers, regardless of the nesting level, are not triggered.
Nested triggers
Trigger nesting can reach a maximum level 32. If the trigger changes the table for which another trigger is defined, then the second trigger is launched, which causes the trigger of the third, etc. If any of the triggers in the chain turns off the infinite cycle, the nesting level exceeds the permissible limit, and the trigger response is canceled. If the TRANSACT-SQL trigger performs a controlled code using a reference to the method, type or statistical function of the CLR environment, this link is considered one of the permissible 32 nesting levels. Methods caused from a managed code are not subject to this restriction.
To cancel nested triggers, set the value 0 NESTED TRIGGERS parameter SP_CONFIGURE procedure. In the default configuration, nested triggers are allowed. If the attached triggers are disabled, recursive triggers will also be disabled, regardless of the recursive_triggers setting installed using the Alter Database instruction.
The first AFTER trigger embedded in Instead of, trigger is triggered, even if nested triggers The server configuration parameter is set to 0. However, with this parameter value, the subsequent triggers are not triggered. It is recommended to check the applications for nested triggers to determine if the applications meet the business rules this behavior nested triggers The server configuration parameter is 0 and make appropriate changes.
Delayed interpretation of names
SQL Server served stored procedures, triggers and packages in the TRANSACT-SQL language, which contain references to tables that do not exist at the time of compilation. Such an opportunity is called deferred name interpretation.
To create a DML trigger requires Alter permission to a table or representation in which a trigger is created.
To create a DDL trigger with an action area within the server (on All Server) or the login trigger requires the Control Server permission to the server. To create a DDL trigger with a visibility area within a database (on Database), the Alter Any Database DDL Trigger permission is required to the current database.
A. Using a DML trigger with a warning message
The following DML trigger sends the client a message when someone tries to add or change the data in the Customer table in the AdventureWorks2012 database.
CREATE TRIGGER REMINDER1 ON SALES.CUSTOMER AFTER INSERT, UPDATE AS RAISERROR ("NOTIFY CUSTOMER RELATIONS", 16, 10); Go.
B. Using a DML trigger with a warning message sent by email
In the following example, the specified user (Marym) e-mail the message is sent when the Customer table changes.
Create Trigger Reminder2 on Sales.customer After Insert, Update, Delete AS Exec MSDB.DBO.sp_send_dbmail @profile_name \u003d "AdventureWorks2012 Administrator", @Recipients \u003d " [Email Protected]", @body \u003d "DON" "T Forget to Print A Report for the Sales Force.", @subject \u003d "REMINDER"; Go.
B. Using DML After Trigger For Forced Application Business Rules Between PurchaseOrderHeader and Vendor Tables
Since the CHECK restriction may contain references only to columns for which the limitations at the column level or table are defined, any intersabilities (in this case, the business rule) must be specified in the form of triggers.
The following example creates a DML trigger in the AdventureWorks database. This trigger performs the verification of the creditworthiness of the supplier is good (not 5) when trying to insert a new purchase order in the PurchaseOrderHeader table. For information about the creditworthiness of the Supplier, a link to the VENDOR table is required. In the case of too low creditworthiness, the appropriate message is displayed and the insert is not performed.
This Trigger Prevents A Row from Being Inserted In The Purchasing.purchaseOrderHeader Table - When the Credit Rating of the Specified Vendor Is Set to 5 (Below Average). CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader AFTER INSERT AS IF EXISTS (SELECT * FROM Purchasing.PurchaseOrderHeader AS p JOIN inserted AS i ON p .PurchaseOrderID \u003d i .PurchaseOrderID JOIN Purchasing.Vendor AS v ON v.BusinessEntityID \u003d p .VendorID WHERE v.CreditRating \u003d 5) Begin Raiserror ( "A Vendor" "S Credit Rating IS Tooo Low to Accept New Purchase Orders.", 16 , 1 ); ROLLBACK TRANSACTION; Return. End; Go. - This Statement Attempts to Insert A ROW INTO THE PURCHASEORDERHEADER TABLE - For a Vendor That Has a Below Average Credit Rating. - The After Insert Trigger Is Fired And The Insert Transaction Is Rolled Back Back. INSERT INTO PURCHASING.PURCHASEORDERHEADER (RevisionNumber, Status, Employeid, Vendorid, ShipMethodid, Orderdate, Shipdate, Subtotal, Taxamt, Freight) Values \u200b\u200b(2, 3, 261, 1652, 4, GetDate (), GetDate (), 44594.55, 3567.564, 1114.8638); Go.
Using the DDL DDL trigger
In the following example, the DDL trigger is used to prevent the synonyms for the database.
Create Trigger Safety On Database for Drop_Synonym as Raiserror ( "You Must Disable Trigger" Safety "to Drop Synonyms!", 10, 1) ROLLBACK GO DROP TRIGGER SAFETY ON DATABASE; Go.
D. Using the Server DDL trigger
In the following example, the DDL trigger is used to display a message when you occur on this instance of a server from the Create Database event, and the EventData function is used to obtain the text of the appropriate instruction in the TRANSACT-SQL. Additional examples of using the EventData function in DDL triggers see.
Create Trigger DDL_TRIG_DATABASE ON ALL Server for Create_Database as Print "Database Created." Select EventData ()Value ( "(/ Event_instance / tsqlcommand / commandtext)", "NVARCHAR (MAX)") GO DROP TRIGGER DDL_TRIG_DATABASE ON ALL SERVER; Go.
E. Using the entrance trigger
In the following example, the entry trigger prohibits attempts to enter SQL Server member login_Test Input if there are already three user sessions under this account.
USE Master; Go. Create login login_test with password \u003d "3khj6dhx (0xvysdf" must_change, check_expiration \u003d ON; Go. GRANT VIEW Server State to Login_Test; Go. CREATE TRIGGER Connection_limit_trigger On All Server With Execute AS "Login_Test" for Logon AS Begin if original_login () \u003d "login_test" and (Select Count (*) from SYS .DM_EXEC_SESSIONS WHERE IS_USER_PROCESS \u003d 1 AND ORIGINAL_LOGIN_NAME \u003d "LOGIN_TEST")\u003e
