0450 Triggers in MonetDB

An Example

CREATE TABLE trigTab ( Number INT );   CREATE TABLE logTab ( Pre INT, Post INT ); SELECT * FROM trigTab; SELECT * FROM logTab;

The task is that for each change in "trigTab", we remember the change in the "logTab". These are the changes in the "trigTab":

	1) INSERT INTO trigTab VALUES ( 1 );   2) UPDATE trigTab SET Number = 2 WHERE Number = 1;   3) DELETE FROM trigTab WHERE Number = 2;
	Each time we change something in "trigTab", we will remember that change in the "logTab". 1) INSERT INTO logTab VALUES ( null, 1 );   2) INSERT INTO logTab VALUES ( 1, 2 );   3) INSERT INTO logTab VALUES ( 2, null );

Although now, our "trigTab" is empty, we have "logTab" with the whole history of changes in the "trigTab".

We want to automate remembering this history and for that we can use triggers.

Triggers

A SQL trigger is a database object that automatically executes a predefined set of SQL statements in response to certain events occurring on a specific table. In our example, each DML statement on a "trigTab" will trigger the change on the "logTab". Like a knee-jerk reaction. A trigger is defined on a table. When we delete that table, the associated trigger will also be deleted. The response of a trigger is an SQL procedure. Any set of statements that we can put into a procedure can be used as the response of a trigger.

This is an example of a trigger that will be triggered each time we insert some number into "trigTab".

CREATE OR REPLACE TRIGGER trigINSERT  --create a new trigger AFTER INSERT ON trigTab               --this trigger is activated after each insert into trigTab        REFERENCING NEW ROW AS NewRow         --we can reference inserted values as RecordAlias.ColumnName     FOR EACH ROW                     --this trigger will be activated once for each inserted number     BEGIN ATOMIC                                             --this is trigger response:          INSERT INTO logTab VALUES ( null, NewRow.Number );  --we insert null and new value into logTab     END;

We will insert 2 numbers into trigTab and that will start a knee-jerk reaction. INSERT INTO trigTab VALUES ( 1 ), ( 2 ); Message is saying that 4 rows will be affected. Two are in the "trigTab", and two are the response in the "logTab". SELECT * FROM trigTab;  SELECT * FROM logTab;

Triggers Theory

Triggers are used to automate some actions. We are using triggers when we want to:

• Enforce business rules and formatting of data.
• Enforce integrity rules. Changes to multiple tables should occur simultaneously.
• In denormalized schemas, we can use triggers to update summary tables.
• When we want to log the changes made.

Triggers are powerful, but big power can create big problems:

• Triggers are reducing server performance and they increase database locking.
• Triggers are hard for debugging.
• Triggers create dependency between database objects. This makes modifying of a database harder.
• We can easily forget that triggers exist and can be confused by unexpected changes in our data.
• It is difficult to determine in what order the triggers will be activated (if we have several triggers of the same type on one table).              

When using triggers we should follow next rules:

• Limit the number of triggers of the same type on one table.
• Don't place complex logic into triggers.
• Avoid cascading triggers. Cascading trigger is a trigger that can activate some other trigger. This can lead to hard-to-debug behavior or infinite loops.
• Always document triggers. Document why the trigger do exist, what does it work, are there any exceptions or caveats to how the trigger works.

Triggers can not be activated directly, only indirectly. Triggers can not accept arguments.

BEFORE and AFTER

Some change on a table will activate the trigger. Our response to that change can be done before or after the change. We can be proactive or reactive.

CREATE OR REPLACE TRIGGER trigTRUNCATE    BEFORE INSERT ON trigTab         --this trigger is activated before each insert into trigTab      REFERENCING NEW ROW AS NewRow        FOR EACH ROW                 --this trigger will be activated once for each row        WHEN ( NewRow.Number IN ( 0, 1 ) )   --only when we try to insert zero or one        BEGIN ATOMIC               DELETE FROM trigTab;     --before inserting the 0 or 1, we'll empty trigTab            END;

This trigger will be proactive. We will act BEFORE the INSERT event. Before we insert a zero, or one in the table "trigTab", we will empty the same table.

Let's initiate our trigger. Again, we will insert two values. Before the insert, I will empty "trigTab" table. TRUNCATE trigTab; INSERT INTO trigTab ( Number ) VALUES ( 0 ), ( 1 );   We can now read from the table "trigTab"; SELECT * FROM trigTab;

Suprisingly, we'll have two numbers in our table. This is because BEFORE trigger fill fire twice, before the INSERT even started. So, "trigTab" will be deleted twice, and then the two numbers will be inserted.

This is not the consequence of the transaction isolation. Inside one transaction we can insert one value in a table and then read it.   This is the consequence of the fact that all of the BEFORE responses will be executed before the changes in a table are made.

Let's test this with AFTER trigger. I will first clear the environment.

TRUNCATE trigTab;

TRUNCATE logTab;

DROP TRIGGER trigTRUNCATE;

DROP TRIGGER trigINSERT;

CREATE OR REPLACE TRIGGER trigCOUNT AFTER INSERT ON trigTab         REFERENCING NEW ROW AS NewRow                 FOR EACH ROW                     WHEN ( NewRow.Number IN ( 0, 1 ) )               BEGIN ATOMIC                 DECLARE NumberOfRows INT;                 SET NumberOfRows = ( SELECT COUNT( * ) FROM trigTab );                 INSERT INTO logTab ( Pre, Post ) VALUES (NewRow.Number, NumberOfRows);             END;

After each insert into "trigTab" table, we will register how many rows this table has. We will use the table "logTab" to remember these results.

We will now test AFTER trigger. INSERT INTO trigTab ( Number ) VALUES ( 0 ), ( 1 );   We will then read from the "logTab". I will change the names of columns of this table to appropriate aliases. SELECT pre AS InsertedValue, post AS NumberOfRows FROM logTab;

We can see that "NumberOfRows" is always 2. That means that we have first executed INSERT statement, and only then did we execute AFTER triggers.

"SELECT COUNT( * ) FROM trigTab" will be run twice, but only after the INSERT INTO has finished.

Can We Intercept and Modify the Change on a Table?

In MonetDB, triggers are either proactive (BEFORE) or reactive (AFTER), so they can not modify the statement that activates them. Let's assume that when a user wants to insert number X into "trigTab", our trigger should intercept that statement and modify number X into ( X +1 ), so that ( X + 1 ) is going to be inserted instead. This is not possible to do in the MonetDB, but we can get the same result by altering the value X in the table into ( X + 1 ), by the AFTER trigger.

I will clear the environment:

TRUNCATE trigTab;

TRUNCATE logTab;

DROP TRIGGER trigCOUNT;

CREATE OR REPLACE TRIGGER trigINTERCEPT AFTER INSERT ON trigTab   REFERENCING NEW ROW AS NewRow          FOR EACH ROW          BEGIN ATOMIC            UPDATE trigTab SET Number = NewRow.Number + 1 WHERE trigTab.Number = NewRow.Number;          END;

This trigger will use UPDATE to transform X into ( X + 1).   Note that we have to use prefix "trigTab" when defining WHERE condition.

We'll induce the change, and then we will read from the "trigTab". INSERT INTO trigTab ( Number ) VALUES ( 19 ); SELECT * FROM trigTab; We can see on the image that number 19 is increased by 1, so we have number 20. In this solution it is necessary that we have a way to identify a row that was changed, so that we can overwrite the original change.  The example bellow will better explain this problem.

INSERT INTO trigTab ( Number ) VALUES ( 20 ); SELECT * FROM trigTab; We inserted number 20 into table that already has number 20. This time the trigger will not know what row to change, so it will change both of them, so that is why we have 21 twice. Such trigger can be useful only if we can uniquely identify the changed row.

Triggers Activated by DELETE and TRUNCATE

I will clear the environment:

DROP TRIGGER trigINTERCEPT;

CREATE OR REPLACE TRIGGER trigDELETE AFTER DELETE ON trigTab   REFERENCING OLD OldRow          FOR EACH ROW          BEGIN ATOMIC           INSERT INTO logTAB ( pre, post ) VALUES ( OldRow.Number, null ) ;      END;

DELETE FROM trigTab; After delete, we can only reference old rows. SELECT * FROM logTab;

Everything will work the same for the TRUNCATE.

Triggers Activated by UPDATE

I will prepare the environment:

INSERT INTO trigTab VALUES ( 5 );

TRUNCATE logTab;

DROP TRIGGER trigDELETE;

CREATE OR REPLACE TRIGGER trigUPDATE BEFORE UPDATE ON trigTab   REFERENCING OLD OldRow NEW NewRow   --no comma between           FOR EACH ROW           BEGIN ATOMIC              INSERT INTO logTAB ( pre, post )              VALUES ( OldRow.Number, NewRow.Number );           END;

UPDATE trigTab SET Number = 6 WHERE Number = 5; With UPDATE, we can reference both old and new values. SELECT * FROM logTab;

We can limit UPDATE trigger only to changes in some of the columns. I will prepare the environment once more:

TRUNCATE trigTab;

DROP TRIGGER trigUPDATE;

CREATE OR REPLACE TRIGGER trigUpdateOf BEFORE UPDATE OF pre ON logTab   REFERENCING OLD OldRow NEW NewRow           FOR EACH ROW           BEGIN ATOMIC              INSERT INTO trigTab VALUES ( OldRow.Pre * 10 + NewRow.Pre );           END;

This trigger will be activated only if someone update a value in the "pre" column. This time I have reversed the roles of the "trigTab" and "logTab" tables. Changes on "logTab" will be logged into "trigTab".

UPDATE logTab SET post = 7 WHERE post = 6; SELECT * FROM trigTab; Update statement is changing only the column "post". That means that this trigger should not be activate. We can see on the image that the trigger was activated. We can conclude that "UPDATE of" doesn't work in MonetDB.

CREATE OR REPLACE TRIGGER trigUpdateOf BEFORE UPDATE OF pre ON logTab   REFERENCING OLD OldRow NEW NewRow           FOR EACH ROW           WHEN ( OldRow.Pre <> NewRow.Pre )              BEGIN ATOMIC              INSERT INTO trigTab VALUES ( OldRow.Pre * 10 + NewRow.Pre );       END;

Maybe this will work instead of "UPDATE of"? But it wont. It seems that we can not use OldRow.Pre in the WHEN subclause. We can only use NewRow.Pre.

CREATE OR REPLACE TRIGGER trigUpdateOf BEFORE UPDATE OF pre ON logTab   REFERENCING OLD OldRow NEW NewRow           FOR EACH ROW           BEGIN ATOMIC             IF ( OldRow.Pre <> NewRow.Pre ) THEN                     INSERT INTO trigTab VALUES ( OldRow.Pre * 10 + NewRow.Pre );             END IF;           END;

This version is using IF statement  to control flow. This trigger will be accepted by MonetDB, but when we try to update something: UPDATE logTab SET pre = 99; , we will get an error.

It seems to me that currently we can not compare old and new values in MonetDB UPDATE trigger.

Triggers on Views

Because MonetDB views are not updateable, it is not possible to create triggers on views.

FOR EACH STATEMENT

I will clear the environment:

TRUNCATE trigTab;

TRUNCATE logTab;

DROP TRIGGER trigUpdateOf;

Triggers don't have to be activated for each row that is changed. Triggers can be activated only once for the whole statement.

CREATE OR REPLACE TRIGGER trigStatement BEFORE INSERT ON trigTab   REFERENCING NEW TABLE AS NewTable           FOR EACH STATEMENT           BEGIN ATOMIC              DECLARE NoOfRows INTEGER;              SET NoOfRows = ( SELECT COUNT(*) FROM newTable );              INSERT INTO logTab VALUES ( NoOfRows, null );           END;		Instead of "FOR EACH ROW", we now have "FOR EACH STATEMENT". Instead of "NEW ROW", we now have "NEW TABLE". "NewTable"  represents all of the inserted rows (only the changed rows). We can use COUNT(*) to find the number of inserted rows.
INSERT INTO trigTab VALUES ( 13 ), ( 17 ); SELECT Pre AS "NoOfRows" FROM logTab; I am using alias "NoOfRows" to correctly label the column.	After inserting 2 values, "logTab" table now has 2 rows.

When using UPDATE trigger, we can also use variable "OLD TABLE". "NEW TABLE" represents changed rows, and "OLD TABLE" represents those rows before the change.

LOGIN Triggers

LOGON triggers can be activated before or after the user have login to MonetDB server.  

CREATE OR REPLACE TRIGGER trigSchema AFTER LOGIN            BEGIN ATOMIC              SET SCHEMA voc;           END;

We can use a trigger like this to change a user's default schema after they log in. It seems that these LOGIN triggers don't work. I have tried several variants of such triggers and I have never succeeded to make them working.

Statement Instead of the Procedure

If our trigger has to only execute one statement as response, we don't have to use syntax for a procedure. We can just write that one statement.

I will clear the environment:

TRUNCATE trigTab;

TRUNCATE logTab;

DROP TRIGGER trigStatement;

CREATE OR REPLACE TRIGGER trigNoProcedure AFTER INSERT ON trigTab   FOR EACH ROW     INSERT INTO logTAB VALUES ( 88, 88 );

We don't need syntax BEGIN ATOMIC and END if we only have one statement.

INSERT INTO trigTab VALUES ( 1 ); SELECT * FROM logTab;

Recursive Triggers

I will clear the environment.

TRUNCATE trigTab;

TRUNCATE logTAB;

DROP TRIGGER trigNoProcedure;

Each trigger is doing some action in a response to triggering event. In the example below we can see trigger that will response with an INSERT statement, but that INSERT statement will call the same trigger again. In that way we have an endless loop. MonetDB will response with the error message.

CREATE OR REPLACE TRIGGER trigRecursive BEFORE INSERT ON trigTab   FOR EACH ROW     INSERT INTO trigTab VALUES ( 21 );

INSERT INTO trigTab VALUES ( 14 );

I will now delete trigRecursive with "DROP TRIGGER trigRecursive".

We can also have two triggers that are mutually recursive.

CREATE OR REPLACE TRIGGER trigBefore BEFORE TRUNCATE ON logTab   FOR EACH ROW     INSERT INTO trigTab VALUES ( 21 );

CREATE OR REPLACE TRIGGER trigAfter AFTER INSERT ON trigTab   FOR EACH STATEMENT     TRUNCATE logTab;

TRUNCATE logTAB; "trigBefore" is doing INSERT, which will start "trigAfter". "trigAfter" is doing TRUNCATE, which will call "trigBefore".   This is vicious cycle that will never end and MonetDB will response with the error message. We should strive to avoid such situations.

System Table

Data about our triggers can be found in sys.triggers system table.

SELECT * FROM sys.triggers;