Creating sql server triggers. Execute triggers in a specific order. Additional types of triggers
Last update: 09.11.2017
Triggers are a special type of stored procedure that is called automatically when a certain action is performed on a table or view, in particular, when adding, changing or deleting data, that is, when performing INSERT, UPDATE, DELETE commands.
Formal definition of a trigger:
CREATE TRIGGER trigger_name ON (table_name | view_name) (AFTER | INSTEAD OF) AS sql_expressions
To create a trigger, use the CREATE TRIGGER statement followed by the trigger name. Typically, the trigger name reflects the type of operation and the name of the table on which the operation is performed.
Each trigger is associated with a specific table or view, the name of which is indicated after the word ON .
Then the trigger type is set. We can use one of two types:
AFTER: Executed after the action is completed. Defined only for tables.
INSTEAD OF: executed instead of an action (that is, in fact, the action - adding, changing or deleting - is not performed at all). Defined for tables and views
After the trigger type comes an indication of the operation for which the trigger is defined: INSERT, UPDATE or DELETE.
For an AFTER trigger, you can use it for several actions at once, for example, UPDATE and INSERT. In this case, operations are indicated separated by commas. You can only define one action for an INSTEAD OF trigger.
And then after the word AS comes a set of SQL expressions, which actually make up the body of the trigger.
Let's create a trigger. Let's say we have a database productsdb with the following definition:
CREATE DATABASE productdb; GO USE productdb; CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);
Let's define a trigger that will fire when adding and updating data:
USE productdb; GO CREATE TRIGGER Products_INSERT_UPDATE ON Products AFTER INSERT, UPDATE AS UPDATE Products SET Price = Price + Price * 0.38 WHERE Id = (SELECT Id FROM inserted)
Let's say the Products table stores data about products. But the price of a product often contains various surcharges such as value added tax, added corruption tax, and so on. The person adding the data may not know all these subtleties with the tax base, and he determines the net price. Using a trigger, we can adjust the price of a product by a certain amount.
This way, the trigger will fire on any INSERT or UPDATE operation on the Products table. The trigger itself will change the price of the product, and to get the product that was added or changed, we find this product by Id. But what value should the Id of such a product have? The fact is that when adding or changing data, it is saved in the intermediate table inserted. It is created automatically. And from it we can get data about added/changed products.
And after adding a product to the Products table, in reality the product will have a slightly higher price than the one that was determined when added:
Removing a trigger
To remove a trigger, you must use the DROP TRIGGER command:
DROP TRIGGER Products_INSERT_UPDATE
Disabling a trigger
It happens that we want to pause a trigger, but we don’t want to remove it completely. In this case, it can be temporarily disabled using the DISABLE TRIGGER command:
DISABLE TRIGGER Products_INSERT_UPDATE ON Products
And when you need a trigger, you can enable it using the ENABLE TRIGGER command:
ENABLE TRIGGER Products_INSERT_UPDATE ON Products
Triggers Overview Triggers DML Triggers DDL Triggers DML Events: Insert, Delete, Update Logon Triggers DDL Events: Create, Drop, Alter Logon Introduced in SQL Server 2005
DML - trigger Object - table, VIEW Event - insert, update, delete for table and for VIEW. Activation time – before (instead of) or after the execution of the statement.
DML triggers Trigger is a block that is executed automatically every time a certain event occurs - as opposed to a procedure, which must be called explicitly Event - INSERT, UPDATE and DELETE for a table, view - a trigger cannot be defined for a query
DML triggers A trigger is created on one database table. It can also access other tables and objects of other databases. Triggers cannot be created on temporary tables or system tables, but only on user-defined tables or views. The table by which the trigger is defined is called the trigger table.
When Triggers Are Needed To evaluate the state of a table before and after data changes and take action based on that difference. Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow you to take several different actions in response to a single data modification statement.
When triggers are needed For cascading changes in related database tables (if they cannot be accomplished using cascading referential integrity constraints). To prevent accidental or incorrect INSERT, UPDATE, and DELETE operations To implement integrity constraints that cannot be specified using a CHECK constraint. DML triggers can reference columns of other tables.
More... Logging and auditing. Using triggers, you can track changes to tables that require enhanced security support. Table change data may be stored in other tables and include, for example, user ID, time of update operation; the updated data itself, etc. Data reconciliation and cleaning. With anyone simple operator SQL that updates a certain table can be associated with triggers that perform corresponding updates on other tables. Operations not related to database modification. Triggers can perform more than just database update operations. The SQL standard allows you to define stored procedures (which can be called from triggers) that send email, printing documents, etc.
When you should not use triggers You do not need to implement with triggers the capabilities achieved by using declarative DBMS tools (integrity constraints or foreign keys) Avoid complex chains of triggers
Tips Don't use triggers when you can use a CHECK constraint. Don't use a CHECK constraint when you can use a UNIQUE constraint.
Basic trigger parameters Table (or view) name Trigger time: AFTER(FOR) or INSTEAD OF Event: INSERT, UPDATE, DELETE (TRUNCATE TABLE is not a delete!) Trigger body! The sequence of triggers of the same type is arbitrary
Grouping Events For example, you could create a trigger that fires when an UPDATE or INSERT statement is executed, which we'll call an UPDATE/INSERT trigger. You can even create a trigger that will fire when any of the three data modification events occur (UPDATE/INSERT/DELETE trigger).
Trigger Rules Triggers are fired after the statement that caused them to fire completes. For example, an UPDATE trigger will not fire until the UPDATE statement is executed. If any statement attempts to perform an operation that violates some table constraint or causes some other error, then its associated trigger will not fire.
Trigger Rules A trigger is considered part of a single transaction along with the statement that calls it. Therefore, you can call a rollback operator from a trigger, and this operator will rollback both the trigger and the corresponding data modification event. If an error occurs while executing the trigger, the entire transaction is automatically rolled back. A trigger fires only once per statement, even if that statement affects multiple rows of data.
Example CREATE TRIGGER trg ON my_table FOR INSERT, UPDATE, DELETE AS select "this is trigger"
When the trigger is called, the SQL statements specified after keyword AS. You can put multiple statements here, including programming constructs such as IF and WHILE.
Selecting a trigger type INSTEAD OF triggers are used to: – Selectively inhibit the execution of a command for which a trigger is defined (checking a precondition); – Counting column values before the INSERT or UPDATE command completes. AFTER triggers are used for: – Accounting for completed operations; – Checks the post-conditions of command execution.
Loops and Nesting SQL Server allows you to use nested triggers, up to 32 levels deep. If any nested trigger performs a ROLLBACK operation, subsequent triggers are not fired. Triggers are canceled if an infinite loop occurs.
INSTEAD OF Trigger The INSTEAD OF trigger executes instead of running the SQL statement. This overrides the action of the starting statement. You can define one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement. An INSTEAD OF trigger can be defined on a table and/or a view. You can use cascades of INSTEAD OF triggers by defining views on top of views, where each view has a separate INSTEAD OF trigger. INSTEAD OF triggers are not allowed on modifiable views that contain the WITH CHECK option.
AFTER Trigger AFTER triggers can only be defined on tables. The AFTER trigger fires after all operations specified in the trigger statement or SQL statements. This includes the entire cascade of link actions and all constraint checks.
AFTER Trigger If you have multiple AFTER triggers defined by a table for a specific statement or set of statements, you can specify which trigger will fire first and which trigger will fire last. If you have more than two triggers defined, you can specify the firing order of only the first and last trigger. All other triggers are activated randomly.
AFTER Trigger Order sp_settriggerorder @triggername = "Another. Trigger", @order = "first" sp_settriggerorder @triggername = "My. Trigger", @order = "last" sp_settriggerorder @triggername = "My. Other. Trigger", @order = "none" sp_settriggerorder @triggername = "Yet. Another. Trigger", @order = "none"
Using inserted, deleted Special tables: inserted – inserted values (for INSERT, UPDATE) deleted – deleted values (for UPDATE, DELETE)
Using the deleted and inserted tables When you create a trigger, you have access to two temporary tables named deleted and inserted. They are stored in memory, not on disk. These two tables have the same structure as the table (same columns and data types) on which this trigger is defined.
Using deleted and inserted tables The deleted table contains copies of rows that were affected by a DELETE or UPDATE statement. Rows deleted from the table of this trigger are moved to the deleted table. The deleted table data can then be accessed from this trigger. The inserted table contains copies of rows that were added to the table of this trigger when an INSERT or UPDATE statement was executed. These rows are added simultaneously to the trigger table and the inserted table.
Using deleted and inserted tables Because an UPDATE statement is processed as a DELETE followed by an INSERT, when you use an UPDATE statement, the old row values are copied to the deleted table and the new row values are copied to the trigger table and the inserted table. The INSERT => deleted trigger is empty. The DELETE => inserted trigger is empty but no error message will appear!
Creating a trigger CREATE TRIGGER [ schema_name. ]trigger_name ON ( table | view ) ( FOR | AFTER | INSTEAD OF ) ( [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] ) AS ( sql_statement )
CREATE TRIGGER plus_1 ON table 1 instead of insert AS insert table 1 (id, col 1) select id+1, col 1 from inserted;
Handling Exceptions The ROLLBACK command instructs the server to stop processing the modification and disallow the transaction. There is also a RAISEERROR command that you can use to send an error message to the user. TRY...CATCH
Exception handling error message RAISERROR ("Error raised because of wrong data.", -- Message text. 16, -- Severity. 1 -- State.); Severity – a number from 0 to 25 User-defined error severity level. Any user can specify 0 to 18. 19 to 25 can only be specified by sysadmin 20 to 25 are considered fatal - the connection to the client is lost and an error message is logged in the application and error logs. State An integer from 0 to 255. Negative values or values greater than 255 result in an error being generated. If the same custom error occurs in multiple locations, you can use the unique status number for each location to determine where the error occurred in the code.
Error Functions The ERROR_LINE() function returns the line number where the error occurred. The ERROR_MESSAGE() function returns the text of the message that will be returned to the application. The text contains values for substitution parameters such as length, object names, or time. ERROR_NUMBER() returns the error number. The ERROR_PROCEDURE() function returns the name of the stored procedure or trigger in which the error occurred. This function returns NULL if this error was not committed inside a stored procedure or trigger. ERROR_SEVERITY() returns the severity level of the error. ERROR_STATE() returns the state.
Example of a trigger CREATE TRIGGER Low. Credit ON Purchasing. Purchase. Order. Header AFTER INSERT AS BEGIN DECLARE @creditrating tinyint, @vendorid int ; SELECT @creditrating = v. Credit. Rating, @vendorid = p. Vendor. ID FROM Purchasing. Purchase. Order. Header p JOIN inserted i ON p. Purchase. Order. ID = i. Purchase. Order. ID JOIN Purchasing. Vendor v ON v. Vendor. ID = i. Vendor. ID ; IF @creditrating = 5 RAISERROR ("This vendor""s credit rating is too low to accept new purchase orders.", 16, 1) ; END
Managing triggers Disabling/enabling a trigger: – DISABLE/ENABLE TRIGGER trigger_name ON object_name Disabling/enabling all table triggers: – DISABLE/ENABLE TRIGGER ALL ON object_name Changing a trigger: – ALTER TRIGGER trigger_name … Deleting a trigger: – DROP TRIGGER trigger_name
Activating/deactivating a trigger DISABLE TRIGGER (trigger_name [ , . . n ] | ALL ) ON ( object_name ) ; ENABLE TRIGGER (trigger_name [ , . . n ] | ALL ) ON ( object_name)
Using triggers Security – Deny access based on data values Accounting – Change logging Data integrity – Complex integrity rules – Complex referential integrity Derived data – Automatic calculation of values
Trigger Types Function AFTER Trigger INSTEAD OF Trigger Entities Tables and Views Number of Triggers per Table/View Multiple per Event One Trigger per Event No Constraints INSTEAD OF UPDATE and DELETE cannot be defined for tables that are subject to cascading referential integrity constraints. Cascading links After the following operations: Processing constraints. Execution Declarative referential actions. Creating inserted and deleted tables. The action that fires the trigger. Before the next activity: Processing constraints. Instead of the following operation: The action that fires the trigger. After the following operations: Creation of inserted and deleted tables.
DDL - trigger DDL triggers can be used in administrative tasks such as auditing and throttling database operations. These triggers affect all commands of the same type in the entire database or server.
DDL - Triggers DDL triggers, like regular triggers, cause stored procedures to fire in response to an event. Fire in response to a variety of Data Definition Language (DDL) events. These events generally correspond to Transact-SQL statements that begin with the CREATE, ALTER, or DROP keywords.
Tasks for DDL triggers Prevent certain changes from being made to the database schema. Perform some actions on the database in response to changes in the database schema. Record database schema changes or events. DDL triggers fire only after the corresponding DDL instructions have been executed. DDL triggers cannot be used as INSTEAD OF triggers.
CREATE TRIGGER trigger_name ON (DATABASE | ALL SERVER) ( FOR | AFTER ) ( event_type | event_group ) AS ( sql_statement [ ; ] [ , . . n ] [ ; ] )
Creating/deleting DDL-tr CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT "Database Created." DROP TRIGGER ddl_trig_database ON ALL SERVER;
DDL - trigger CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT "You must disable Trigger "safety" to drop or alter tables!" ROLLBACK ;
You can create multiple DDL triggers for a single Transact-SQL statement. The DDL trigger and the instruction that fires it are executed in the same transaction. ALTER DATABASE events that occurred inside a DDL trigger cannot be rolled back. DDL triggers execute only after a Transact-SQL statement completes. DDL triggers cannot be used as INSTEAD OF triggers. DDL triggers do not create inserted and deleted tables.
Logon - trigger Logon triggers execute stored procedures in response to the LOGON event. This event is raised when a user session is established with an instance of SQL Server. Login triggers fire after the login authentication phase has completed, but before the user session is actually established.
Logon - trigger CREATE TRIGGER trigger_name ON ALL SERVER ( FOR| AFTER ) LOGON AS ( sql_statement )
Trigger (database)
Trigger(English) trigger) is a special type of stored procedure that is not directly called by the user, but whose execution is conditioned by a data modification action: adding an INSERT, deleting a DELETE row in a given table, or changing the UPDATE of data in a specific column of a given table in a relational database. Triggers are used to ensure data integrity and implement complex business logic. The trigger is fired automatically by the server when an attempt is made to change data in the table to which it is associated. All data modifications it makes are considered to be performed in the transaction in which the action that caused the trigger to fire was performed. Accordingly, if an error is detected or data integrity is violated, this transaction may be rolled back.
When a trigger is fired is determined using the keywords BEFORE (the trigger runs before the event associated with it is executed; for example, before adding a record) or AFTER (after the event). If the trigger is called before the event, it can make changes to the record modified by the event (of course, provided that the event is not deleting the record). Some DBMSs impose restrictions on the operators that can be used in a trigger (for example, it may be prohibited to make changes to the table on which the trigger hangs, etc.)
In addition, triggers can be bound not to a table, but to a view (VIEW). In this case, with their help, the “updatable view” mechanism is implemented. In this case, the BEFORE and AFTER keywords only affect the sequence in which triggers are called, since the actual event (delete, insert, or update) does not occur.
On some servers, triggers may not be called for each modified record, but only once per table change. Such triggers are called table triggers.
Example (Oracle):
/* Table level trigger */ CREATE OR REPLACE TRIGGER DistrictUpdatedTrigger AFTER UPDATE ON district BEGIN INSERT INTO info VALUES ( "table "district" has changed") ; END ;
In this case, to distinguish table triggers from row triggers, additional keywords are introduced when describing row triggers. In Oracle this is FOR EACH ROW.
/* Row-level trigger */ CREATE OR REPLACE TRIGGER DistrictUpdatedTrigger 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
See what “Trigger (database)” is in other dictionaries:
View (database)- This term has other meanings, see Presentation. View (English view, more consonant with the non-standard name “view”, in programmer slang it is often used as a borrowing from the English “view”, “view”)... ... Wikipedia
Hierarchical databases- A hierarchical database model consists of objects with pointers from parent objects to children, connecting related information together. Hierarchical databases can be represented as a tree consisting of objects of various levels.... ... Wikipedia
Relational Databases- Relational database is a database based on a relational data model. The word "relational" comes from the English. relation. To work with relational databases, relational DBMSs are used. The use of relational databases was... ... Wikipedia
Index (databases)- This term has other meanings, see Index. Index is a database object created to improve the performance of data retrieval. Tables in a database can have a large number of strings that are stored in ... Wikipedia
Cursor (database)- This term has other meanings, see Cursor (meanings). Cursor is a link to the contextual memory area [source not specified 126 days]. In some implementations of the information logical language SQL (Oracle, ... ... Wikipedia
Trigger (values)- Trigger (English trigger in the meaning of the noun “pawl, latch, trigger in the general sense, an element that puts something into action”; in the meaning of the verb “to bring into action”): in Russian, originally a term from the field ... ... Wikipedia
Database refactoring- (English database refactoring) is a simple change in the database schema that helps improve its design while maintaining functional and informational semantics. In other words, the consequence of database refactoring cannot be... ... Wikipedia
Database- The “DB” request is redirected here; see also other meanings. A database presented in an objective form is a collection of independent materials (articles, calculations, regulations, court decisions and other similar materials), ... ... Wikipedia
Database Design- the process of creating a database schema and defining the necessary integrity constraints. Contents 1 Main tasks of database design ... Wikipedia
Data model- In classical database theory, a data model is a formal theory for representing and processing data in a database management system (DBMS), which includes at least three aspects: 1) the structure aspect: methods for describing types and ... ... Wikipedia
There are already many articles on the Internet about sql triggers, but I’ll add one more with adequate examples to reinforce the material for those who are “in the know” and to better understand the material for those who have just begun to comprehend the “zen of sql”. At the same time, I’ll create a discussion on the topic.
Let me make a reservation right away that my opinion is just my opinion, and sometimes it is very categorical. For a number of reasons, you have to work with highly loaded sites and complex web applications.
One valuable experience learned from working on them was to keep track of priorities and statistics. What does it mean? It's simple: if you have a blog and it has 2-3-4-10012 million visitors per day, and articles are written only 1-2-3-3435 times a day (an order of magnitude less than the number of views), then the speed of saving the article ( and the complexity of this) relative to the speed of display of the article may be proportionally less. The more we show, the more critical it is the display, and not the saving of the article/page/table. Which doesn't mean you can relax. Saving an article in 3-5-10 seconds on a blog is within the limits of adequacy, but generating a page in more than 2 seconds (+ while scripts and styles with pictures are loaded) is on the verge of “what a slow site, I’ll read something else” , and even worse, “I’ll go buy it somewhere else.”
If we take an average website with voting/karma, comments, page impression counter, etc., then many developers immediately come to mind with constructions like SELECT count(*) FROM comment WHERE comment.page=page_id. Well, just think about calculating the amount of ratings and the amount of comments for each article. Oh, we have 10 articles from each section on the main page. With a traffic of 10 people per second, on an average VPS, you can afford 60-100 SQL queries per page (hello, Bitrix).
But to hell with the lyrics (I'm probably tired of it already). Bare data:
blog table
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=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;
Comments table
CREATE TABLE IF NOT EXISTS `comments` (`owner_name` varchar(50) NOT NULL, `owner_id` int(12) NOT NULL, `id` int(12) NOT NULL AUTO_INCREMENT, `parent_id` int(12) DEFAULT NULL, `user_id` int(12) DEFAULT NULL, `text` text, `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=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;
As you can see, in the blog table, each article has a comment counter (comment field).
Simple practice:
1. Added a comment - increased the counter for the blog
2. Deleted/hidden the comment - decreased the counter.
Doing this in code is convenient and familiar, but there is a more convenient tool - triggers.
And so, we have 2 events (actually 3): the creation of a comment and its deletion (the third event is a change in its status (“deletion”, ban, etc.).
Let's consider only creation and deletion, and let changing the status be your homework.
There is one feature in the example: comments can be for several types of articles.
Creating a comment:
CREATE TRIGGER `add_count_comment` AFTER INSERT ON `comments` FOR EACH ROW BEGIN // for the user in personal account let's count how many comments he wrote UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id; // determine what the comment refers to and immediately increase the counter in these tables CASE NEW.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `blog `.id = NEW.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ; END CASE; // here we make it easier for ourselves to work with news feeds // we write the url of the article right away, so that THEN we don’t have to make unnecessary selections CASE NEW.`owner_name` WHEN "Blog" THEN SET userurl = (SELECT url FROM `blog` WHERE `blog`. id= NEW.`owner_id`); WHEN "Article" THEN SET userurl = (SELECT url FROM `article` WHERE article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET userurl = ``; END CASE; // write the title of the article right away so as not to make a selection THEN CASE NEW.`owner_name` WHEN "Blog" THEN SET usertitle = (select title from `blog` where blog.id=NEW.`owner_id`); WHEN "Article" THEN SET usertitle = (select title from `article` where article.id=NEW.`owner_id`); WHEN "PopulatePlace" THEN SET usertitle = ` `; END CASE; INSERT INTO user_has_events VALUES (NEW.user_id,NEW.id,"Comments",NOW(),userurl , usertitle); END
The same goes for deleting a comment:
CREATE TRIGGER `del_count_comment` AFTER DELETE ON `comments` FOR EACH ROW BEGIN UPDATE user SET user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id; CASE OLD.`owner_name` WHEN "Blog" THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ; WHEN "Article" THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`-1 WHERE `article`.`id` = OLD.`owner_id` ; WHEN "PopulatePlace" THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ; END CASE; END
And so what we got:
1. When inserting a comment, we automatically calculated the amount of comments for a specific comment object (article, page, note) using the sql server.
2. We have created a news feed (hello to all social networks, etc.)
3. When you delete a comment, we deduct all data.
4. We did not use any framework tools.
5. Retrieval of all necessary data occurs quickly (only 1 request when displaying a page, with the exception of other “left” data on it.)
We also have sphinx, which periodically makes selections of articles that have changed in the last minute. For this purpose, the blog has a modification field.
Trigger added:
CREATE TRIGGER `ins_blog` BEFORE INSERT ON `blog` // insert the time before saving the information by “replacing” the data. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END
Now, making a selection for the last minute, we will get all the documents that were added in the last minute.
CREATE TRIGGER `ins_blog` BEFORE UPDATE ON `blog` // insert the time before saving the information by “replacing” the data. FOR EACH ROW BEGIN SET NEW.modification = NOW(); END
If the data changes, we will update the search index too.
Usually, in an average project, everything that can be transferred to the sql server side is transferred. The sql server itself performs such operations faster and with fewer resources than can be done through the programming language used.
UPD: The holivar devoted to the feasibility of complicating the structure of the database is declared open.
Trigger database is a specially designed named PL/SQL block stored in a database. Each trigger is associated with a specific table and is automatically launched by ORACLE when one of the DML statements (INSERT, DELETE, UPDATE) or a combination of them is executed on this table.
Purpose of triggers. Triggers can be used:
1) to implement complex data integrity constraints that cannot be implemented in a standard way when creating a table;
2) preventing incorrect transactions;
3) performing procedures for comprehensive verification of access rights and data secrecy;
4) generating some expressions based on the values available in the columns of tables;
5) implementation of complex business rules for data processing (the ability to track “echo”, i.e. the ability to update the data of related tables when one table changes).
Creating and enabling triggers. To create and automatic switching on trigger uses the following general syntax:
CREATE TRIGGER trigger_name
(BEFORE | AFTER)
(INSERT | DELETE | UPDATE)
ON table_name
< PL/SQL_блок >
If the OR REPLACE keywords are present, the trigger is recreated if it already exists.
BEFORE design | AFTER indicates when the trigger is fired. The BEFORE option means that the trigger will fire before the execution of the triggering DML statement; the AFTER option means that the trigger will fire after the execution of the activating DML statement.
INSERT | DELETE | UPDATE specifies the type of DML statement that fires the trigger. It is allowed, using the logical OR operation, to specify a set of activating operators, for example: INSERT OR DELETE. When using the UPDATE option, if a list of columns is specified, the trigger will fire when one of the specified columns is modified; if there is no list of columns, then the trigger will be fired when any of the columns of the table associated with the trigger changes.
The FOR EACH ROW construction indicates the nature of the trigger's effect: string or operator. If the FOR EACH ROW clause is present, then the trigger is a string; in its absence, the trigger is operator. A statement trigger fires once before or after the DML statement that fires the trigger is executed, regardless of how many rows in the table associated with the trigger are modified. A row trigger is fired once for each row that is modified by the DML statement that fires the trigger.
Using the WHEN keyword, you can specify an additional restriction on the rows of the table associated with the trigger, when modified, the trigger can be fired.
The PL/SQL_block construct represents a PL/SQL block that ORACLE runs when the trigger is fired.
Classification of triggers. There are basically twelve types of triggers. The trigger type is determined by a combination of the following three parameters:
1) the nature of the trigger’s impact on the rows of the table associated with it (string or operator);
2) the moment the trigger is launched: before (BEFORE) or after (AFTER) the execution of the DML statement that activates the trigger;
3) the type of DML operator activating the trigger (INSERT, DELETE, UPDATE);
The order in which triggers are activated. If a table has several types of triggers, they are activated according to the following scheme:
1) the BEFORE operator trigger is executed (if there are several of them, then nothing can be said about the order of their execution);
2) the BEFORE string trigger is executed;
3) the DML operator activating the trigger is executed, followed by checking all data integrity constraints;
4) the AFTER string trigger is executed, followed by checking all data integrity constraints;
5) the AFTER operator trigger is executed.
Trigger predicates. If a trigger specifies a set of DML operators that activate the trigger (for example, INSERT OR DELETE), then to recognize which specific DML operator is executed on the table associated with the trigger, trigger predicates are used: INSERTING, DELETING, UPDATING. They are logical functions that return TRUE if the type of the activating operator matches the type of the predicate, and FALSE otherwise. To specify the same actions when executing different DML statements in conditional operator trigger predicates are combined using logical operations.
Pseudo-recordings. For string triggers, there are special constructs that allow, when executing DML operations on a table row, to access both the old values that were in it before modification and the new ones that will appear in the row after its modification. These constructs are called pseudo-records and are denoted old and new. The structure of these pseudo-records is identical to the structure of the row of the table being modified, but you can only operate on individual fields of the pseudo-record. The fields of a pseudo-record are accessed according to the following scheme: before old or new the symbol “:” is placed, then the name of the field is indicated through a dot. The values that pseudo-record fields take on when activating DML statements are executed are defined as follows.
INSERT statement - pseudo-record:new is equivalent to the row being inserted, and pseudo-record:old is NULL in all fields.
DELETE statement - pseudo-record:old is equivalent to the row being deleted, and pseudo-record:new is NULL in all fields.
UPDATE statement - the pseudo-record:new is equivalent to the row resulting from the modification, and the pseudo-record:old in all fields is the original row value.
Turning triggers on and off. A trigger stored in the database can be temporarily disabled without removing it from the database. To do this, use the following command:
ALTER TRIGGER trigger_name DISABLE;
You can enable a trigger after a certain period of time using the command
ALTER TRIGGER trigger_name ENABLE;
You can disable or allow the firing of all triggers associated with a certain table using the command
ALTER TABLE table_name (DISABLE | ENABLE) ALL TRIGGERS;
where the DISABLE option is used to disable, and the ENABLE option is used to enable all triggers of a given table.
Removing triggers from the database. Destroying a trigger, i.e. deleting a trigger from the database, is done using the following command:
DROP TRIGGER trigger_name;
Obtaining information about triggers. Triggers are stored in a database, so information about them can be retrieved from the USER_TRIGGERS data dictionary view, for example with the following command:
SELECT * FROM USER_TRIGGERS;
Examples.
1. Create a trigger that, before inserting the next row into the KNIGA_POSTAVKA table, checks the presence of the specified book code in the KNIGA table. If the specified book code is not present in the KNIGA table, an exception should be generated with the corresponding message.
New rows are added to the KNIGA_POSTAVKA table using the INSERT statement. Since the trigger must fire before executing each INSERT statement, it must therefore be a string BEFORE trigger. To maintain data integrity, it is necessary to check whether the entered book codes are also in the KNIGA table. To do this, use a one-liner SELECT statement information is retrieved from the KNIGA table, where the BOOK_CODE field of the pseudo-record is used in the selection condition: new. If the number of rows with a given book code in the KNIGA table is equal to zero, an exception will be generated and a corresponding message will be issued.
Trigger TR1 is created by entering the following statement:
CREATE OR REPLACE TRIGGER TR1
BEFORE INSERT ON KNIGA_POSTAVKA
SELECT COUNT(*) INTO KOL FROM KNIGA
WHERE BOOK_CODE = :NEW.BOOK_CODE;
IF KOL = 0 THEN RAISE_APPLICATION_ERROR
(–20212,"The KNIGA table contains no information about this book");
The action of trigger TR1 can be checked by executing the following statement, which inserts a row into the KNIGA_POSTAVKA table and thereby causes trigger TR2 to be activated:
INSERT INTO KNIGA_POSTAVKA VALUES(21,15,’Ivanov’,15,
Since book code 15 is not in the KNIGA table, an exception will be thrown and a corresponding message will be issued.
2. Create a trigger that prohibits entering into the KNIGA table rows with a PRICE field value greater than 5,000 rubles, as well as increasing the price of books, information about which is stored in the KNIGA table, by more than 20%. If this requirement is violated, an exception must be generated with an appropriate message.
Since new rows are added to the KNIGA table as a result of executing the INSERT statement, and the value of the PRICE field in the KNIGA table, containing the price of the book, can be changed as a result of executing the UPDATE statement, the trigger specifies a set of triggering DML statements. Since the trigger must fire before executing each of the specified DML statements, it is therefore a string BEFORE trigger. Since the actions performed by the trigger are different for each of the triggering DML statements that modify the KNIGA table, the corresponding trigger predicates INSERTING and UPDAITING are used to recognize the type of DML statement. Due to the fact that when inserting new rows, the new value of the PRICE field must be checked, and when modifying the value of the PRICE field, the new value must be compared with the old value, it is necessary to use pseudo-records: new and: old.
Creating a TR2 trigger is done by entering the following statement:
CREATE OR REPLACE TRIGGER TR2
BEFORE INSERT OR UPDATE OF PRICE ON KNIGA
IF INSERTING THEN
IF:NEW.PRICE > 5000 THEN
RAISE_APPLICATION_ERROR
(–20102, "Records with a book price > 5000 cannot be made in the KNIGA table");
IF UPDATING THEN
IF:NEW.PRICE > :OLD.PRICE*1.2 THEN
RAISE_APPLICATION_ERROR
(–20103, “In the KNIGA table, you cannot change the price of a book by more than 20%”);
The action of the TR2 trigger can be tested by executing the following statements, which, by inserting rows into the KNIGA table and updating rows in the KNIGA table, thereby cause it to be activated.
Operator for inserting rows into the KNIGA table, causing trigger TR2 to be activated:
INSERT INTO KNIGA VALUES(21, "Dune", "Herbert", 5268, "Ast",
"Fantastic");
Row update operator in the KNIGA table, causing trigger TR2 to be activated:
UPDATE KNIGA SET PRICE=6000;
Since these statements violate the requirements for the value and modification of the price of books, an exception will be generated in all cases and a corresponding message will be issued.
3. Create a trigger that enters the created STAT table containing the columns:
publisher name – IZD,
number of books in the “Novel” genre – KOL_ROM,
number of books in the Fantasy genre – KOL_FAN,
with each modification of the table, KNIGA generates and enters in the corresponding columns of the STAT table the total number of books for each of the publishing houses in the context of the specified topics: “Novel” and “Fiction”.
Modification of the KNIGA table is carried out by executing the following DML statements: INSERT, DELETE or the UPDATE operator, which modifies the value of the GENRE column in the KNIGA table. Since actions to generate information in the STAT table are performed after the execution of each of the operators modifying the KNIGA table, this is an AFTER operator trigger by type. Since the actions performed by a trigger are the same for all types of operators that activate it, trigger predicates are not used. Before creating a trigger, the STAT table must be created.
Creating a STAT table can be accomplished by entering the following set of statements:
DROP TABLE STAT;
CREATE TABLE STAT
(IZD VARCHAR2(15),
KOL_ROM NUMBER(7),
KOL_FAN NUMBER(7)
Creating a TR3 trigger is done by entering the following statement:
CREATE OR REPLACE TRIGGER TR3
AFTER INSERT OR DELETE OR UPDATE OF GENRE
CURSOR V1 IS SELECT PUBLISHING HOUSE
COUNT(NAME) KOL1
FROM KNIGA WHERE GENRE = "Novel"
GROUP BY PUBLISHING HOUSE;
CURSOR V2 IS SELECT PUBLISHING HOUSE,
COUNT(NAME) KOL2
FROM KNIGA WHERE GENRE = "Fiction"
GROUP BY PUBLISHING HOUSE;
DELETE FROM STAT;
FOR Z1 IN V1 LOOP
INSERT INTO STAT VALUES(Z1.PUBLISHER,
FOR Z1 IN V2 LOOP
UPDATE STAT SET KOL_FAN = Z1.KOL2
WHERE IZD = Z1.PUBLISHER;
IF SQL%NOTFOUND THEN
INSERT INTO STAT VALUES(Z1.PUBLISHER, 0,
The action of the trigger can be tested by executing the following statements, which, by inserting rows into the KNIGA table, deleting rows and updating rows in the KNIGA table, thereby causing trigger TR3 to be activated.
Operators for inserting rows into the KNIGA table, causing trigger TR3 to be activated:
INSERT INTO KNIGA VALUES(46, "Heretics of Dune", "Herbert",368,
"Ast", "Fantastic");
INSERT INTO KNIGA VALUES(42, "Ingvar and Alder",
"Nikitin", 168, "Ast", "Roman");
Operators for deleting rows from the KNIGA table, causing trigger TR3 to be activated:
DELETE KNIGA WHERE NAME = "Cossacks";
Row modification operators in the KNIGA table that cause trigger TR3 to be activated:
UPDATE KNIGA SET GENRE="Fiction" WHERE NAME =
"Ingvar and Alder";
You can view information in the STAT table with the following statement.