Saminnet-Search Article Wiki Forum Piwigo SNS Cloud vtiger Sugar
DB2 Triggers

  • Oracle
    • WebLogic Reset (245) Tue04,11:01am

      WebLogic起動するたびにユーザ名とパスワードを聞いてきて煩わしかったので回避したメモ。     securityフォルダを作成     $ mkdir $DOMAIN_HOME/servers/AdminServer/security     boot.propertiesを作成     username=weblogic    password=password     ※平文でOK     nohup & でWeblogicを起動     $ nohup sh $DOMAIN_HOME/ &     ※nohup.outに吐かれるのがいやだったら適当にリダイレクトさせる     boot.propertiesが暗号化されてることを確認     $ ca…


DB2 Triggers

This chapter describes triggers, their types, creation and dropping of the triggers.


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”.

Types of triggers

There are three types of triggers:

1. BEFORE triggers

They are executed before any SQL operation.

2. AFTER triggers

They are executed after any SQL operation.

Creating a BEFORE trigger

Let us see how to create a sequence of trigger:


db2 create sequence <seq_name>

Example: Creating a sequence of triggers for table shopper.sales1

db2 create sequence sales1_seq asint start with1 increment by1


db2 create trigger <trigger_name>no cascade before insert on 
<table_name> referencing newas<table_object>for each row set 
<table_object>.<col_name>=nextval for<sequence_name>

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 newas obj for each row set for sales1_seq

Now try inserting any values:

db2 insert into shopper.sales1(itemname, qty, price) 

Retrieving values from table

Let us see how to retrieve values from a table:


db2 select*from<tablename>


db2 select*from shopper.sales1


  ID       ITEMNAME       QTY 
-------  ------------   ---------- 
    3      bicks            100 
    2      bread            100 
  2 record(s) selected.

Creating an AFTER trigger

Let us see how to create an after trigger:


db2 create trigger <trigger_name>no cascade before insert on 
<table_name> referencing newas<table_object>for each row set
 <table_object>.<col_name>=nextval for<sequence_name>

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;end  


//inseting values in shopper.sales1 
db2 insert into shopper.sales1(itemname,qty,price) 
ID    ITEMNAME       QTY         PRICE 
    3 bicks          100         2400.00 
    4 chiken         100         12400.00 
    2 bread          100         2400.00 

	3 record(s) selected.

Dropping a trigger

Here is how a database trigger is dropped:


db2 drop trigger <trigger_name>  


db2 drop trigger slaes1_trigger   


0 #1 Guest 2018-02-20 17:03
you're in point of fact a excellent webmaster. The website loading pace is amazing.
It kind of feels that you're doing any distinctive trick.
Furthermore, The contents are masterwork. you have done a fantastic process in this subject!

Check out my website :: Bandarq Online

Category Database

TweetTweet Share on LinkedInShare on LinkedIn Share on Google+Google+ Submit to RedditReddit Publish on WordPress WordPress Send emailSend email