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:
(
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:
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:
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
- Structure Query Language (SQL): Chapter 1 – Manipulating information in the DB.
- Structure Query Language (SQL): Chapter 2 – SQL Server Database’s Files and Logs.
- Structure Query Language (SQL): Chapter 3 – Data Discard and DB Limits.
- Structure Query Language (SQL): Chapter 4 – Backup.
- Structure Query Language (SQL): Chapter 5 – Best practices for setting up Historics and Queries.
- Structure Query Language (SQL): Chapter 6 – SQL Commands.
- Structure Query Language (SQL): Chapter 7 – Views.
- Structure Query Language (SQL): Chapter 8 – Triggers.
- Structure Query Language (SQL): Chapter 9 – Stored Procedures.
