Structure Query Language (SQL): Chapter 8 – Triggers.

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.  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.

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.

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.

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.

INSERT trigger

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 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.

DML triggers instructions can also use two special tables: Deleted and Inserted. 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 table stores copies of lines affected during INSERT and UPDATE instructions. When inserting or updating data, new lines will be added to Inserted table.

Creating tables:

CREATE TABLE tbl_test
           (
user_cpf int not null primary key,
user_name varchar(128) not null,
user_email varchar(128) not null
           )
GOCREATE TABLE Status_tbl
           (
Field1 varchar(128) not null,
Date_Time datetime
           )
GO

Creating triggers:

CREATE TRIGGER onInsertValue ON tbl_test
FOR INSERT AS
IF (SELECT COUNT(*) FROM INSERTED) = 1
INSERT INTO Status_tbl
         (
Field1,
         Date_Time

         )
VALUES
              (‘Record inserted successfully’, CURRENT_TIMESTAMP)
GO

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.

Inserting values in a table from SQL Management Studio:

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 table. The query’s syntax is the following:

SELECT Top 1 Status_tbl.Field1,Status_tbl.Date_Time
FROM Status_tbl
ORDER BY Status_tbl.Date_TimeDESC

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

The database used in this article is Microsoft SQL Server 2012.

 

Related Articles


Este artigo foi útil? Was this post helpful?
Yes0
No0

Leave a Reply

Your email address will not be published.Required fields are marked *