{"id":3345,"date":"2019-03-25T17:43:51","date_gmt":"2019-03-25T20:43:51","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/structure-query-language-sql-chapter-8-triggers\/"},"modified":"2020-02-12T15:50:36","modified_gmt":"2020-02-12T18:50:36","slug":"structure-query-language-sql-chapter-8-triggers","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/structure-query-language-sql-chapter-8-triggers\/","title":{"rendered":"Structure Query Language (SQL): Chapter 8 – Triggers."},"content":{"rendered":"
\n

Triggers are a type of special stored procedures that are executed whenever there is an attempt to change the data in a table protected by them.\u00a0 DML triggers (Data Manipulation Language – INSERT, UPDATE, DELETE) can be used to impose rules, to check data integrity, to query other tables, and to include complex Transact-SQL instructions.<\/p>\n

These procedures will be automatically triggered whenever one tries to insert, update, or delete data from a table, and they can never be ignored. Unlike common stored procedures, triggers can’t be executed directly, and they can’t send or receive parameters either.<\/p>\n

A trigger can contain a ROLLBACK TRANSACTION instruction, even when there is no explicit BEGIN TRANSACTION instruction; in this case, the whole transaction (both the trigger and the instruction triggering it) will be reverted or undone.<\/p>\n

Triggers will always be executed after an INSERT, UPDATE, or DELETE command. In this article, this will be illustrated with an example of INSERT trigger.<\/p>\n

\"\"<\/div>\n

INSERT trigger<\/b><\/p>\n

An INSERT trigger can send error or success messages, or even execute certain functions according to the created transactions. These actions cane be set up in a trigger via IF conditionals, which makes it possible to check whether the operation was executed successfully. In the example, a table called tbl_test<\/b> will be criated, and customers’ registry will be simulated. Whenever a new user is inserted, a confirmation message will be recorded with information of the operation’s date\/time and of its success status.<\/p>\n

DML triggers instructions can also use two special tables: Deleted <\/b>and Inserted<\/b>. The SQL Server is the responsible for automatically creating and managing these tables, and they can be used to test the effect of changes in data. The Inserted <\/b>table stores copies of lines affected during INSERT and UPDATE instructions. When inserting or updating data, new lines will be added to Inserted <\/b>table.<\/p>\n

Creating tables: <\/b><\/p>\n

CREATE TABLE<\/span> tbl_test<\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (<\/span>
\nuser_cpf<\/span> int not null primary key<\/span>,<\/span>
\nuser_name<\/span> varchar<\/span>(128) not null,<\/span>
\nuser_email<\/span> varchar<\/span>(128) not null<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/span>
\nGO<\/span>CREATE TABLE<\/span> Status_tbl<\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (<\/span>
\nField1 <\/span>varchar<\/span>(128) not null,<\/span>
\nDate_Time<\/span> datetime<\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/span>
\nGO<\/span><\/div>\n

Creating triggers:<\/b><\/p>\n

CREATE TRIGGER<\/span> onInsertValue <\/span>ON <\/span>tbl_test<\/span><\/span>
\nFOR INSERT AS<\/span><\/span>
\nIF <\/span>(SELECT <\/span>COUNT(*) FROM <\/span>INSERTED<\/span>) = 1<\/span>
\nINSERT INTO<\/span> Status_tbl<\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (<\/span>
\nField1,<\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Date_Time<\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/span>
\nVALUES<\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (‘Record inserted successfully’<\/span>, CURRENT_TIMESTAMP<\/span>)<\/span>
\nGO<\/span><\/div>\n

To every record successfully inserted, the SGBD will write in an auxiliary table called Status_tbl. From this auxiliary table, you can handle records in E3, confirming whether the values have really been inserted in the main table.<\/p>\n

Inserting values in a table from SQL Management Studio:<\/b><\/p>\n

\"\"<\/div>\n

To check whether there was an INSERT command in the database, you can create a query in E3 that returns the last inserted record in Status_tbl<\/b> table. The query’s syntax is the following:<\/p>\n

SELECT Top 1 Status_tbl.Field1,Status_tbl.Date_Time <\/span><\/b>
\nFROM Status_tbl<\/span><\/b>
\nORDER BY Status_tbl.Date_TimeDESC<\/span><\/b><\/div>\n

Every time data has been successfully written in the table (via Historic’s WriteRecord <\/i>method or Insert Into<\/i> command), Status_tbl<\/b> table’s fields will return with a message and last INSERT’s time\/date, according to what was set up in the trigger:<\/p>\n

\"\"<\/div>\n

The database used in this article is Microsoft SQL Server 2012.<\/p>\n<\/div>\n

 <\/p>\n

Related Articles<\/h3>\n
\n