DB2 trigger trouble

ConstantinL · #1 (**permalink**) 07-16-09, 07:48

Hello

I am building a data base using DB2 9.7 and i am having a bit of trouble with creating a trigger.
I have to tables:
1. tab witch contains the data with the structure id(int), name(varchar 50), subscribe (int) witch cand be either 1 if i want to monitor the row or 0 if not
2. updateTraker with the structure id_update(int autoincrement), name(varchar 50) witch will be populated through the trigger with the rows from tab that have been updated.

I have done this in the past on Microsoft SQL Server 2005, MySQL and PostgreSQL and it worked, but for some reason i am getting a strange error here.

This is the statement i use to create the trigger:

Code:

create trigger updateTab
after update on tab
REFERENCING NEW AS NEWROW
for each row
begin atomic
	insert into updateTraker 
		values (null, newrow.name) 
		where newrow.subscribe=1;
end

and this is the error i get:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "end".
Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "end". Expected tokens may include: "JOIN <joined_table>

I based my trigger on the example present in the offical documentation foud here: DB2 Database for Linux, UNIX, and Windows

Does anibody have any ideas?

ARWinner · #2 (**permalink**) 07-16-09, 08:04

You have several things wrong with your trigger. If you are going to use "BEGIN ATOMIC - END" you need to use a different character for the statement termination other than the semicolon ( ; ) (most people use the @ symbol). Second, your syntax for the insert is incorrect. You cannot have a where clause with the values statement. And lastly, you do not really need the "BEGIN ATOMIC - END block. Try this:

Code:

create trigger updateTab
after update on tab
REFERENCING NEW AS NEWROW
for each row when (newrow.subscribe=1)
	insert into updateTraker  (name) 
		values newrow.name) 
;

Andy

ConstantinL · #3 (**permalink**) 07-16-09, 08:59

Thank you very much. That solved my problem.
Now how do i mark the topic solved?

ARWinner · #4 (**permalink**) 07-16-09, 09:04

You just did.

Andy

Internet Services

Web Development

Digital Marketing

Consumer Tech