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
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 TRIGGERfollowed by the name of the trigger defines what kind of object we want to create and what it should be called.
ONdefines the table to which the trigger should react
AFTER INSERTindicates when the trigger should fire, there is here beside
INSERTalso the possibility to react to
UPDATEor to specify combinations of them (separated by commas). A trigger that fires on
DELETEis a “touch trigger” that registers every change to the table.
AFTER INSERTmeans that the
TRIGGERis not executed until all parts of the inserting statement have been successfully completed.
- Then follows with
ENDa block which defines what should happen after the
INSERT. Here you can access the newly inserted or deleted or overwritten values with
deleted. 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
INSERTs 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
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
INSERTs, the end user cannot understand why. It is therefore recommended that you use
TRIGGERs 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.