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
Labels:

Comments

Post a Comment

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 setPos...
Post a Comment
Read more

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 successf...
Post a Comment
Read more

Thajsko 2018 - Ko Lanta (opičky a rybičky)

Aby sme v Thajsku nevynechali čistokrvné dovolenkové rezorty s plážami, vydali sme sa z Krabi (príspevok o Krabi nájdete tu ) na ostrov Lanta, ktorý sa nachádza asi 2 hodiny každý vanom od Krabi smerom na juh. Už cesta samotná bola zaujímavá. Prekvapil nás najmä "systém", akým tu verejná doprava funguje. Van vás väčšinou vyzdvihne u konkrétneho hotelu a pokračuje vyzdvihnúť ďalších spolucestujúcich. Nakoniec sme sa ešte zastavili na miestnej autobusovej stanici, kde šofér od nejakého pána prevzal zásielku pre nejakú pizzeriu po ceste (podľa krabice asi ingrediencie na pizzu). Cestou sme ešte párkrát rôzne zastavili a vyložili nejaký tovar. Takže taxíky tu v podstate fungujú aj ako kuriéri. Nikto žiadne meškanke príliš nerieši, väčšinou zastavia a s úsmevom vám to vysvetlia "Five minute, ok?". Okrem týchto zastávok sa nám cesta predĺžila o čakanie na kompu na Koh Lanta, takže to čo malo trvať asi 2 hodiny, trvalo viac než 3. Až tak nám to zase nevadilo, pretože t...
Post a Comment
Read more