TRIGGER

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 react
• AFTER INSERT indicates when the trigger should fire, there is here beside INSERT also the possibility to react to DELETE or UPDATE or to specify combinations of them (separated by commas). A trigger that fires on INSERT, UPDATE and DELETE is a “touch trigger” that registers every change to the table. AFTER INSERT means that the TRIGGER is not executed until all parts of the inserting statement have been successfully completed.
• Then follows with BEGIN and END a block which defines what should happen after the INSERT. Here you can access the newly inserted or deleted or overwritten values with inserted or 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 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 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.

References

• Documentation from Microsoft