Skip to main content

Creating trigger for DB2

Recently I was solving a problem, where a trigger for DB2 database was needed. Using few of available tutorials on the internet, I started to build my first DB2 trigger. Everything seemed to be easy understand, until my first test of the trigger.
After couple minutes of googling I have eventually discovered what did the mysterious error in the console mean. The problem was in termination character for the SQL query. As the query, which should be ran after triggering event has to be terminated with a semicolon, the trigger query itself has to be terminated with something else apparently - so that the SQL engine won't start the execution after first semicolon it sees after the action query.
Solution
The solution is quiet simple - change the termination character.



--#SET TERMINATOR @
CREATE TRIGGER trg1 BEFORE INSERT ON table
FOR EACH ROW
BEGIN ATOMIC
VALUES 1; --here you can see the semicolon, which normally causes problems :)
END
@
--SET TERMINATOR ;


Some usefull links:

If you are not satisfied with the trigger you have created, you will probably think about deleting (dropping). This easy query will drop the created trigger:
DROP TRIGGER trigger_name;

PDF File
An Introduction to Trigger in DB2 for OS

Comments

Popular posts from this blog

Servant (Design Pattern) in Java - example

The servant design pattern - or better idiom is used to provide the functionality (methods) to some group of objects. This functionality is common for all these object and therefor should not be repeated in every of these classes.
The object, which should be served is passed to the method of servant as a parameter. All the served objects should implement common interface - in this particular example IMovable interface. Also the type of argument passed to the servand method is of type IMovable.
The servant in this example is used to move objects from one position to another. In real life application these methods should change the position of object in small steps so that the final change would look like smooth movement (animation). In my servant method, only some message are printed instead for demonstration.
IMovable interface:
package com.shimon.servant; import java.awt.Point; /** * Movable interface * @author shimon * */ public interface IMovable { public void setPosition(P…

Hrebeňovka Nízkych Tatier za 4 dni

Nízke Tatry som ešte do nášho presunu do Žiliny a následneho sťahovania do Brna poznal z našich pohorí asi najlepšie. V dobe keď som ešte žil v Brezne ma myšlienka hrebeňovky príliš nenadchýnala, predsa len som to mal všetko za domom (takže tam môžem ísť kedykoľvek :). Ako to už ale väčšinou býva, človek si uvedomí čo mal, až keď to stratí - našťastie to nebola v tomto prípade žiadna tragická (či trvalá) strata, a tak sme sa spolu s Lukášom a Maťom rozhodli využiť tohoročné nádherné letné počasie na prechod z Telgártu na Donovaly po červenej značke a teda po hrebeni.

Kto nechce čítať ďalej a chce si pozrieť len fotky, nech pokračuje tu.


Vstávanie, cestovanie a ostrý štart Jednou z koplikovanejších otázok, bola práve otázka presunu zo Žiliny na Telgárt. Túru je potrebné začať čím skôr a preto do úvahy prichádzali len asi 2 spoje z Banskej Bystrici s prestupom v Brezne. Každopádne to pre nás znamenalo nastaviť si budík na 3:15, aby bolo reálne stihnúť autobus o pol šiestej ráno v Bystri…

Java Crate (design pattern/idiom) example

Another example, based on example explained in the book "Navrhove vzory" (Design patterns) from Rudolf Pecionvsky. I have re-made this example just to somehow get more familiar with this design pattern (or better idiom). The "crate" is used to store the set/list of object in one place, so that the moving (passing) these objects is easier.

The example from the book is very easy, and helps to understand, how this design pattern could be applied to som very usefull application (e.g. day planner)

Code example:
package com.sim.crate.common; import java.util.ArrayList; import java.util.List; import java.util.ListIterator; /** * The Day Plan class demonstrates the usage of crate to create simple day plan, with items that do not collide. * @author shimon * */ public class DayPlan { private final List actions = new ArrayList(); /** * Tries to add an item to the day plan with entered start, end time and duration. Returns true, if * the try was successful, or f…