Db2 CREATE TRIGGER

Summary: in this tutorial, you will learn how to use the Db2 CREATE TRIGGER statement to create new triggers in the database.

Introduction to Db2 CREATE TRIGGER statement

The CREATE TRIGGER statement allows you to create a new trigger or replace an existing trigger in the database.

The basic syntax for the CREATE TRIGGER statement is the following:

CREATE [OR REPLACE] TRIGGER trigger_name
    [NO CASCADE] {AFTER | BEFORE | INSTEAD OF} trigger_event
    ON { table_name |view_name }
    REFERENCING {OLD AS | NEW AS | OLD TABLE AS | NEW TABLE AS} 
    {correlation_name |identifier}
    {FOR EACH ROW | FOR EACH STATEMENT}
    action;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the trigger after the CREATE TRIGGER keywords. If you use CREATE OR REPLACE TRIGGER, Db2 will create the trigger if it does not exist or replace in case the trigger already exists.
• Second, specify the event that activates the trigger e.g., INSERT, UPDATE, or DELETE.
• Third, identify the name of the table or view on which the trigger is defined.
• Fourth, specify a correlation name that identifies the row state before triggering the operation.
• Fifth, specify a temporary table name that identifies the set of affected rows before triggering the operation.
• Sixth, specify the action to perform when a trigger is fired.

Db2 CREATE TRIGGER example

Let’s see an example of using the CREATE TRIGGER statement to create an after insert trigger.

First, create a new table named employees to store employee data:

CREATE TABLE employees(
   employee_id INT GENERATED ALWAYS AS IDENTITY NOT NULL,
   first_name VARCHAR(100) NOT NULL,
   last_name VARCHAR(100) NOT NULL,
   gender CHAR(1) NOT NULL,
   dob DATE NOT NULL,
   salary DEC(10,2) NOT NULL,
   start_date DATE,
   PRIMARY KEY(employee_id),
   CHECK(salary > 0),
   CHECK(gender = 'F' OR gender = 'M')
);
Code language: SQL (Structured Query Language) (sql)

Second, create a new table hr_stats and insert a row into the hr_stats table. The hr_stats table has one column that store total headcount:

CREATE TABLE hr_stats(
   headcount INT NOT NULL DEFAULT 0,
   CHECK(headcount>=0)
);
INSERT INTO hr_stats(headcount) 
VALUES(0);
Code language: SQL (Structured Query Language) (sql)

Third, create a new trigger named update_headcount that increases the headcount by one after a new employee is inserted into the employees table

CREATE OR REPLACE TRIGGER update_headcount 
   AFTER INSERT ON employees
   FOR EACH ROW MODE DB2SQL 
   UPDATE hr_stats 
   SET headcount = headcount + 1;
Code language: SQL (Structured Query Language) (sql)

Fourth, insert two employees to the employees table:

INSERT INTO 
   EMPLOYEES(first_name, last_name, gender, dob, start_date, salary)
VALUES
   ('John','Doe','M','1990-12-15','2019-06-07',120000),
   ('Jane','Doe','F','1992-06-12','2019-06-07',120000);
Code language: SQL (Structured Query Language) (sql)

The trigger update_headcount were executed twice.

Finally, view the contents of the hr_stats table to verify whether the trigger has been executed or not.

SELECT * FROM hr_stats;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

HEADCOUNT
---------
2
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 CREATE TRIGGER statement to create a new trigger in the database.