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…

Livigno 2017

Po niekoľkých rokoch, kedy sme skončili v lete na dovolenke niekde pri Jadrane, sme si minulý rok so Zuzkou povedali, že ďalšia dovolenka musí byť iná. K tomu sa pridal obmedzený počet dní dovolenky, ktoré sme mohli cez leto využiť. Takže sme zvažovali, či vôbec ísť k moru, a aké sú ďalšie možné varianty. Po zavrhnutí niekoľkých iných alternatív sme sa nakoniec rozhodli pre "cyklo" dovolenku v horách, a ako je zrejmé už z nadpisu, v talianskom alpskom mestečku Livigno. Zastávka v Innsbrucku Na dovolenku sme chceli využiť 2 české štátne sviatky v priebehu prvého júlového týždňa, preto sme dovolenku plánovalí od soboty do nedele. Až týždeň pred odjazdom sme si uvedomili, že by sme takto zabili celú sobotu cestovaním a tak sme si povedali, že vyrazíme už v piatok po práci a zastavíme sa v niektorom z rakúskych miest po ceste. Do úvahy pripadali asi len Salzburg a Innsbruck. Keďže Innsbruch je k Švajčiarsku (cez ktoré vedie najkratšia cesta do Livigna) najbližšie, zabookovali sm…

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…