 |
Triggers in DB2 (DB2 Triggers). |
Greetings and welcome back to today's session on DB2 Trigger. The goal of this session is to provide you with a basic understanding of DB2 triggers and how DB2 triggers work within the DB2 ecosystem. Furthermore, you will learn what triggers DB2 supports and how to create them and what is the difference between DB2 trigger and SQL Stored Procedure. Let's get started with today's agenda.
Agenda.
- Introduction.
- What are triggers?
- How does a trigger work?
- What is the type of trigger?
- DB2 triggers vs store procedures.
- How do you create triggers?
- Trigger syntax details.
- Triggers Example.
- Conclusion.
Introduction.
Most large-scale enterprise applications use databases to store their data. Depending on the functionality of the system, an application program processes the data. The application layer is always where the core business logic lives. However, modern databases, such as IBM DB2, Microsoft SQL Server, Oracle, MongoDB, Amazon Aurora, etc., support advanced code features such as triggers and stored procedures. With DB2 Triggers, you can build active databases that respond to naturally occurring database activities. The use of triggers extends the capabilities of conventional application programming.
What are the Triggers in DB2?
In layman's terms, triggers in DB2 are pieces of code executed in response to a SQL data modification operation. When an insert, delete, or update operation is performed on a table or view, the database automatically executes a trigger.
It is relatively easy to understand the concept of a trigger. DB2 triggers are actually event-driven, that is, they are stored and maintained by the database management system, and each trigger is associated with a specific table. Triggers are advanced forms of "rules" and "constraints" written using SQL extensions.
Triggers are created using DDL and stored in the database like other database objects, such as tables and indexes. They are also stored as entries in the DB2 Catalog.
Note: As a result, triggers are implicit, automatic, and inviolable. DB2 triggers cannot be invoked externally.
How does a trigger work?
Triggers are relatively simple to understand and they are defined on DB2 tables. Triggers define sets of actions that are initiated automatically by the database when a delete, insert, or update occurs on a table or view via SQL statement. The trigger is activated as soon as the operation is performed.
Furthermore, triggers can determine when the database has changed. When a column is changed, a trigger can view both the old and new values and perform an action based on that.
Triggers are an invaluable addition to conventional application development. Despite the fact that they are written by the application programmers, they are called directly from the database rather than by the application programs. Thus, triggers in DB2 create an active database that initiates a predefined action toward any operation on the table.
What are the types of triggers?
DB2 Trigger or simply triggers are divided into four types:
- INSERT Trigger: An insert trigger is defined on the DB2 table. It is triggered automatically each time an insert operation is performed on the table via the INSERT SQL statement.
- UPDATE Trigger: An update trigger is defined on the DB2 table. It is triggered automatically each time an update operation is performed on the table via the UPDATE SQL statement.
- DELETE Trigger: An delete trigger is defined on the DB2 table. It is triggered automatically each time a delete operation is performed on the table via the DELETE SQL statement.
- INSTEAD OF Trigger: The instead of trigger is different from above mention trigger types because it's defined on the table views. It provides logic to make non-updatable views to be updatable.
DB2 Trigger vs Stored Procedures.
Stored procedures and triggers are similar in functionality. But, their application and execution are different. They both contain logic that is stored in the database.
- A stored procedure, on the other hand, is not event-driven and does not belong to an individual table.
- Stored procedures are explicitly executed using a CALL. However, Triggers are initiated automatically by the database.
- Additionally, stored procedures can access many tables without being specifically attached to any of them. Triggers can modify other tables, but their execution is tied to an individual table.
How do you create triggers?
The process of creating triggers is relatively straightforward and simple. To define DB2 triggers on specific tables, you must use the DDL SQL statement. The following two SQL statements were used to define or alter the trigger which is already defined in the table.
- SQL CREATE A TRIGGER: SQL statement to define a trigger in DB2.
- SQL ALTER TRIGGER: SQL statement to modify or alter existing defined trigger in DB2.
Now, let's discuss the syntax of DB2 triggers in detail.
CREATE Trigger syntax details.
CREATE TRIGGER trigger-name 1
trigger-activation-time 2
trigger-event ON subject-table 3
transition-variables / transition-table 4
trigger-granularity 5
trigger-mode 6
triggered-action 7
- The create statement starts with CREATE TRIGGER keyword followed by the trigger name. The trigger name should be unique and it should be as per project standards.
- The trigger activation time determines when the trigger event will occur. You can put the condition BEFORE, AFTER, or INSTEAD OF.
- Activation of a trigger is caused by a trigger event. The operation can be INSERT, UPDATE, or DELETE. If more than one event is specified, the keyword OR can be used, for example, INSERT OR DELETE. The subject table is the table or view for which the trigger is defined.
- For any columns or rows that are involved in the trigger event, transition variables or transition tables provide before and after values for those columns and rows.
- For each row or for each statement, the trigger granularity determines whether the action is executed for each row or for each statement changed. An UPDATE operation, for example, may update seven rows, and the trigger is triggered once or seven times, depending on this attribute.
- When the trigger is set to MODE DB2ROW, it is activated after each row operation. In MODE DB2SQL, triggers are activated after every row operation.
- As soon as the trigger is triggered, the triggered action is executed. The action can be simple or complex. It can begin with a WHEN clause, which can include any true/false condition.
DB2 Trigger Example.
1. Create NEW_EMPLOYEE via SQL to create trigger statements.
CREATE TRIGGER NEW_EMPLOYEEAFTER INSERT ON EMPLOYEEFOR EACH ROW MODE DB2SQLUPDATE EMPSTATSET EMPCNT = EMPCNT + 1;
2. Now, let's check the value of EMPCNT in the EMPSTAT table.
SELECT EMPCNT FROM EMPSTAT;
3. Let's insert data into EMPLOYEE and then check the value of EMPCNT.
INSERT INTO Employee (empno, firstnme, midinit,
lastname, workdept,phoneno,
hiredate, job, edlevel, sex,
birthdate, salary, bonus,comm)
VALUES (100501, 'DAN', 'P', 'ANDERSON',
'A00', 5541, '1992-06-09', 'Analyst',
16, 'M', '1974-07-01', 27600, 400,
1922);
4. Let's check the value of EMPCNT from EMPSTAT table.
SELECT EMPCNT FROM EMPSTAT;
Youtube Tutorial: Triggers in DB2 | DB2 Triggers | Create Triggers Syntax.
The following youtube tutorial video clearly talks about DB2 Triggers, how to create triggers, and the syntax.
Conclusion.
Finally, this marks an end to the DB2 Triggers or simply triggers in DB2. In this tutorial, you will learn the basic fundamental of triggers. DB2 Triggers allow developers to implement code that runs periodically in response to predefined events. The triggers concept is the same for all relational databases. However, the syntax would be different for different databases. Do check out COBOL LEVEL 88 Condition.
►Subscribe to Topictrick & Don't forget to press THE BELL ICON to never miss any updates. Also, Please visit mention the link below to stay connected with Topictrick and the Mainframe forum on -
► Youtube
► Facebook
► Linkedin
► Reddit
Thank you for your support.
Mainframe Forum™
No comments:
Post a Comment