What are TRIGGERS?
Now, you’ve heard many times in the Dojo that SQL is always effective when operating on records, and that SQL is a language that allows set operations. That’s why the possibilities for flow control in SQL are rather sparse compared to other programming languages, some of which you have already got to know here. But what about function calls? How can you react when a line is inserted into a table? The answer to these questions is TRIGGER
.
What can you use TRIGGER for?
Most of you probably know TRIGGER
as a construct that takes effect whenever a record is inserted, modified or deleted. There are also TRIGGER
constructs that are used when objects are created, modified, or deleted in databases. There are also LOGIN TRIGGERS
. But what does ‘take effect’ mean? Often – in analogy to the trigger as a trigger of a firearm – it is also called ‘firing’. To understand, what exactly is meant by this, we first create a TRIGGER
. First we need two tables, one on which we define the trigger and one in which we write data when the trigger “fires”. For these tables we create our own schema:
CREATE SCHEMA Trig GO
Two tables are now added to this schema, as already mentioned:
DROP TABLE IF EXISTS Trig.FirstNames DROP TABLE IF EXISTS Trig.FullNames CREATE TABLE Trig.FirstNames ( FirstNameId INT IDENTITY(1,1) ,FirstName varchar(250) not null ) CREATE TABLE Trig.FullNames ( FullNameId INT IDENTITY(10,10) ,FirstNameId INT NULL ,LastNameId INT NULL ,FirstName varchar(250) NULL ,LastName varchar(250) NULL ) GO
Now we are done with the preparations so far that we can define a trigger:
CREATE TRIGGER Trig.TestInsertTrigger ON Trig.FirstNames AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO Trig.FullNames (FirstName, FirstNameId) SELECT i.FirstName, i.FirstNameId FROM inserted i END GO
If you look at this statement, you will see that it consists of four parts, which we would like to look at now:
CREATE TRIGGER
followed by the name of the trigger defines what kind of object we want to create and what it should be called.ON
defines the table to which the trigger should reactAFTER INSERT
indicates when the trigger should fire, there is here besideINSERT
also the possibility to react toDELETE
orUPDATE
or to specify combinations of them (separated by commas). A trigger that fires onINSERT
,UPDATE
andDELETE
is a “touch trigger” that registers every change to the table.AFTER INSERT
means that theTRIGGER
is not executed until all parts of the inserting statement have been successfully completed.- Then follows with
BEGIN
andEND
a block which defines what should happen after theINSERT
. Here you can access the newly inserted or deleted or overwritten values withinserted
ordeleted
. This is how easy you defined your first trigger. Now insert records into the FirstName table:
INSERT INTO Trig.FirstNames (FirstName) VALUES ('Akasuki'), ('Emiko'), ('Saeko')
If you now query the FirstNames
table, you will not surprisingly see three entries with the IDs 1, 2 and 3, but the trigger has now written these records to the FullNames table. But here in reverse order, so that “Saeko” with the FirstNameId
3 has the FullNameId
10 while “Akasuki” with the FirstNameId
1 has the FullNameId
30. This is because we had defined our trigger as AFTER INSERT
, which means that the TRIGGER
statements are only executed when all three INSERT
s have been successful. The order in which the records are then inserted into the FullName
table is left up to the SQL Server, which places the statements fired by the trigger on a LIFO call stack that is processed after the INSERT
statement is successfully completed. Just as we have now defined an AFTER INSERT
trigger, you can write INSTEAD OF INSERT
Triggers that intercept and redirect the INSERT
statement. For this we modify our insert trigger again:
ALTER TRIGGER Trig.TestInsertTrigger ON Trig.FirstNames INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO Trig.FullNames (FirstName, FirstNameId) SELECT i.FirstName, i.FirstNameId FROM inserted i END
Now we add another row to the FirstName
table:
INSERT INTO Trig.FirstNames (FirstName) VALUES ('Sato')
If you now query the two tables, you will see three entries (“Akasuki”, “Emiko” and “Saeko”) in the FirstNames
table, but in the FullNames
table you will also see the entry of “Sato”:
SELECT * FROM Trig.FirstNames SELECT * FROM Trig.FullNames
Important when working with triggers: Since triggers operate line-by-line, they can be fatal for the performance of a database. If a simple INSERT
results in many other INSERT
s, the end user cannot understand why. It is therefore recommended that you use TRIGGER
s sparingly in your databases.
Furthermore, the comprehensibility of your application will be considerably reduced if data suddenly appears in table B instead of table A when INSERT
in table A. However, INSTEAD OF DELETE
triggers can also be useful if you want to work with IsDeleted flags instead of deleting records, because the INSTEAD OF DELETE
trigger can prevent accidental deletion of data. Be careful when working with TRIGGERS anyway.