Date: 2009-05-08 11:38:30 +0200
From: Bram [Cronos] <>
To: SQL devs <>
Version: -- development
CC: @romulogoncalves, @njnes

Last updated: 2019-01-21 14:42:09 +0100

Comment 11711

Date: 2009-05-08 11:38:30 +0200
From: Bram [Cronos] <>

[This is a repost of my message to the monetdb-users mailing list]

I'm experiencing some strange trigger behaviour. Not sure if I'm simply
missing something, or if I've run into a bug.

create table t1 (id serial, toggle boolean);
create table t2 (id serial, ref bigint);

insert into t1 (toggle) values (false);
insert into t1 (toggle) values (false);

create trigger updateMe
after update on t1 referencing new row new_row
for each row when new_row.toggle = true
insert into t2 (ref) values (new_row.id);

update t1 set toggle = true where id = 1;

After this update statement, I would expect to see 1 row in t2.
Instead, I see two rows:

+----+-----+
| id | ref |
+====+=====+
| 1 | 1 |
| 2 | 2 |
+----+-----+

The "when" clause seems to be ignored entirely -- I can change to "when
1 = 0" and it'll still work.

If I change the trigger to

create trigger updateMe
after update on t1 referencing new row new_row
for each row insert into t2 (ref) values (new_row.id);

something equally odd happens. Instead of doing an insert for each
UPDATED row, it inserts a row for each row present in t1. This seems a
bit odd to me, but again, I could just be misunderstand the way this is
supposed to work. Documentation on triggers is a bit sparse I'm afraid...

Any help on this issue would be more than welcome!

Comment 11712

Date: 2009-05-13 22:40:04 +0200
From: @njnes

The when 'condition' code is missing as it seems. Implementation will take some time.

Comment 11713

Date: 2009-08-14 13:57:54 +0200
From: @drstmane

tagged subject

Comment 11714

Date: 2010-05-04 09:32:09 +0200
From: Pseudo user for Sourceforge import <>

This bug was previously known as tracker item 2788876 at http://sourceforge.net/support/tracker.php?aid=2788876

Comment 15689

Date: 2011-03-28 17:36:34 +0200
From: @sjoerdmullender

The Mar2011 version has been released.

Comment 16044

Date: 2011-07-29 11:00:08 +0200
From: @sjoerdmullender

Apr2011-SP2 has been released.

Comment 16283

Date: 2011-09-16 15:10:42 +0200
From: @sjoerdmullender

The Aug2011 version has been released.

Comment 17693

Date: 2012-08-24 14:56:06 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 18046

Date: 2012-11-27 12:58:45 +0100
From: @romulogoncalves

A test was added in the SQL module in the BugTracker_2012 directory.

The test is called:
conditions_when_for_triggers_do_not_work.Bug-2073

And the stable out and err were also added.

Comment 26813

Date: 2019-01-21 14:42:09 +0100
From: @sjoerdmullender

This was fixed in the Jul2017 release.