Improve performance in client/server environment (triggers run on the server side, before the results is returned to the client).

Many database systems have nowadays support for triggers, usually using their own specific implementation of SQL-based dialects. CUBRID has an implementation which also uses SQL-based statements to define the triggers actions.

In CUBRID, we have 2 types of triggers:

User triggers

Table triggers

In this tutorial, we will target only table triggers; user triggers will be subject to another tutorial.

Before we proceed to the next sections, we remind you that it is very important, when defining triggers, to understand the relation between activation time and the new, old and obj qualifiers:

BEFORE

AFTER

INSERT

new

obj

UPDATE

objnew

objold

DELETE

obj

Note: If you are unsure about how are these combinations should be used in the context of triggers, please read the CUBRID manual section dedicated to this topic, before you proceed further with the next parts of this tutorial!

A Data Update trigger

Let’s start by creating a trigger which will automatically update the “notes” column for an author, when a new book is added in our library. We will need to “intercept” the INSERT operations in the “books” table and, AFTER the INSERT is done, UPDATE the “notes” column from the “authors” table.

This is the code of the trigger which performs this operation:

CREATE TRIGGER "t_update_notes"

AFTER INSERT ON "books"

EXECUTE

update authors set authors.notes= concat('A new book was released on ', obj.published_date) where authors.author_id=obj.author_id;

If you use the CUBRID Manager client to create the trigger, here below is the definition of the trigger:

If everything goes ok, the user will be notified that the trigger was successfully created.

The trigger we created will intercept this action and will update the “books” table, as desired:

A Data Validation trigger

Now, we will create a trigger which will automatically verify that whenever we add a new book in our library, the “published_date” is a later date than the author “born_date”. We will need to “intercept” the INSERT operations in the “books” table and, BEFORE the INSERT is done, compare the values of the date in the both tables. If the validation fails, the trigger should REJECT the INSERT.

This is the code of the trigger:

CREATE TRIGGER "t_verify_dates"

BEFORE INSERT ON "books"

IF new.published_date<=(select born_date from authors where author_id=new.author_id)

EXECUTE BEFORE REJECT;

In the CUBRID Manager client, the trigger can be created as shown below:

The trigger we created has intercepted this invalid action and denied it:

The trigger canceled the INSERT, because the automatic validation we setup previously has failed.

Another Data Update trigger

Finally, we will create a trigger which will automatically update the number of the books published by the author, after a book is removed (delete) from the library. We will need to “intercept” the DELETE operations in the “books” table and, and, AFTER the INSERT is done, UPDATE the “books_count” column in the “authors” table.

Here is the code of this trigger:

CREATE TRIGGER "t_update_books_count"

AFTER DELETE ON "books"

EXECUTE

update authors set books_count=(select count(*) from books where books.author_id=authors.author_id);

The trigger definition in the CUBRID Manager client is:

Let’s test what happens when we delete a book. Let’s first add a new author, with one book in the library:

The trigger we created will intercept the “books” DELETE and will update the “authors” table (will set “books_count” value to 0):

Summarizing, as you can see from the examples above, using triggers in CUBRID is not difficult at all…! 

Actually, all you have to do is to know SQL, understand the triggers concept and learn how to use the CUBRID Manager interface, which will significantly simplify the management of the triggers.

Other things to know about CUBRID triggers

Enable/Disable triggers

A trigger can always be enabled or disabled. You can achieve this by using the ALTER TRIGGER statement:

ALTER TRIGGER t_update_books_count STATUS ACTIVE;

Or

ALTER TRIGGER t_update_books_count STATUS INACTIVE;

Trigger(s) priority

Triggers in CUBRID have a “special” attribute, called PRIORITY. This attribute is used whenever there are multiple triggers that will be executed for the same event, to determine the execution order. PRIORITY can have any non-negative float value between 00.00 and 9999.99, with 0 being the highest priority. If all the triggers have the same priority, the execution order is random.

You can always use the ALTER TRGGER statement to change a trigger priority:

ALTER TRIGGER t_update_books_count PRIORITY 0.9;

Note: STATUS and PRIORITY are the only trigger attributes that can be changed after the trigger was created! If you need to change other attributes of the trigger, you will need to drop the trigger and re-create it.

Trigger execution log

You can view the execution log of a trigger by using the SET TRIGGER TRACE statement:

SET TRIGGER TRACE ON;

Let’s try this with the trigger t_update_notes:

User rights & Triggers

In CUBRID, there is no dedicated GRANT statement for triggers (like in MySQL or ORACLE. In particular, in MySQL it is the TRIGGER privilege which enables trigger operations. You must have this privilege for a table to create, drop, or execute triggers for that table).

In CUBRID the following rules apply:

A table trigger is visible to all users who have the SELECT privilege on the trigger target table.

To create a table trigger, the user must have an ALTER authorization on the table.

Triggers potential (down) side effects

Defining (and using) triggers, beside the many useful things they can achieve, can have possible downside effects as well, and the user should know about this, before taking any decisions regarding triggers usage.

Such potential downside effects can be:

Database performance slow-down. Imagine that you have a trigger which runs a slow SQL statement, and the trigger is executed for each UPDATE on a heavy-traffic table/column. Such a trigger will have a significant impact on the database performance.

Watch out for recursive triggers! Such triggers might even cause the current session close! Be very careful when defining triggers that modify the same table for which the trigger has been triggered!

Triggers should not be used to “hide” database design flaws! Do not use triggers to replace CASCADE constraints, or FOREIGN KEY references, for example.

This concludes the first CUBRID Triggers tutorial. We will return soon with another tutorial about “user triggers” in CUBRID! Please let us know your feedback and remember to periodically check the CUBRID web site – www.cubrid.org/tutorials - for other tutorials and resources.