Db2 简明教程
DB2 - Triggers
本章介绍触发器，它们的类型以及触发器的创建和放弃。
This chapter describes triggers, their types, creation and dropping of the triggers.
Introduction
触发器是一组针对数据库中指定表上的 INSERT、UPDATE 或 DELETE 操作进行响应而执行的 actions。触发器一次性存储在数据库中。它们处理数据管理。可以在多个应用程序之间访问和共享它们。使用触发器的优点在于，如果需要在应用程序中进行任何更改，则在触发器处进行，而不是更改访问触发器的每个应用程序。触发器易于维护，且它们迫使应用程序开发更快。触发器使用 SQL 语句 “CREATE TRIGGER” 定义。
A trigger is a set of actions, which are performed for responding to an INSERT, UPDATE or DELETE operation on a specified table in the database. Triggers are stored in the database at once. They handle governance of data. They can be accessed and shared among multiple applications. The advantage of using triggers is, if any change needs to be done in the application, it is done at the trigger; instead of changing each application that is accessing the trigger. Triggers are easy to maintain and they enforce faster application development. Triggers are defined using an SQL statement “CREATE TRIGGER”.
Creating a BEFORE trigger
让我们看看如何创建触发器序列：
Let us see how to create a sequence of trigger:
Syntax:
Syntax:
db2 create sequence <seq_name>Example ：为 shopper.sales1 表创建触发器序列
Example: Creating a sequence of triggers for table shopper.sales1
db2 create sequence sales1_seq as int start with 1 increment by 1Syntax:
Syntax:
db2 create trigger <trigger_name> no cascade before insert on
<table_name> referencing new as <table_object> for each row set
<table_object>.<col_name>=nextval for <sequence_name>Example ：为 shopper.sales1 表创建触发器以自动插入主键号
Example: Creating trigger for shopper.sales1 table to insert primary key numbers automatically
db2 create trigger sales1_trigger no cascade before insert on
shopper.sales1 referencing new as obj for each row set
obj.id=nextval for sales1_seq现在尝试插入任何值：
Now try inserting any values:
db2 insert into shopper.sales1(itemname, qty, price)
values('bicks', 100, 24.00)Retrieving values from table
让我们看看如何从表中检索值：
Let us see how to retrieve values from a table:
Syntax:
Syntax:
db2 select * from <tablename>Example :
Example:
db2 select * from shopper.sales1Output ：
Output:
ID ITEMNAME QTY
------- ------------ ----------
3 bicks 100
2 bread 100
2 record(s) selected.Creating an AFTER trigger
让我们看看如何创建 after 触发器：
Let us see how to create an after trigger:
Syntax:
Syntax:
db2 create trigger <trigger_name> no cascade before insert on
<table_name> referencing new as <table_object> for each row set
<table_object>.<col_name>=nextval for <sequence_name>Example: [插入和检索值]
Example: [To insert and retrieve the values]
db2 create trigger sales1_tri_after after insert on shopper.sales1
for each row mode db2sql begin atomic update shopper.sales1
set price=qty*price; endOutput:
Output:
//inseting values in shopper.sales1
db2 insert into shopper.sales1(itemname,qty,price)
values('chiken',100,124.00)
//output
ID ITEMNAME QTY PRICE
----- -------------- ----------- -----------
3 bicks 100 2400.00
4 chiken 100 12400.00
2 bread 100 2400.00
3 record(s) selected.