When I try next sql no action happens regardless of "FOR EACH STATEMENT" or "FOR EACH ROW" type of trigger.

create table dba.oj_ree (
   oj                   integer                            not null default autoincrement,
   nm                   varchar(64)                    null,
   main_psn             varchar(64)                    not null,
   mobiles              varchar(64)                    not null,
   email                varchar(32)                    not null,
   nodel                integer                         not null default 0,
   keyParms             long varchar                   null,
   constraint PK_OJ_REE primary key (oj)
 );

CREATE OR REPLACE TRIGGER  oj_ree_nodel INSTEAD OF DELETE ON dba.oj_ree
REFERENCING old AS tbl
FOR EACH STATEMENT
begin
    declare n,ojl,nodell int;
    select count(*) into n from tbl;
    select oj,nodel into ojl,nodell from tbl;
    MESSAGE 'INSTEAD OF n='||n||' ojl='||ojl||' nodell='||nodell to CONSOLE ;
    set nodell = (if nodell=1 then 0 else 1 endif);        
    update dba.oj_ree 
      set nodel = nodell 
      where oj= ojl
      ;
end;


INSERT INTO "dba"."oj_ree" ("oj","nm","main_psn","mobiles","email","nodel","keyParms") VALUES(
DEFAULT,'Tornado','Serge','099965730','mail@gmail.com',DEFAULT,DEFAULT);
INSERT INTO "dba"."oj_ree" ("oj","nm","main_psn","mobiles","email","nodel","keyParms") VALUES(
DEFAULT,'Tornado','Serge','099965730','mail@gmail.com',DEFAULT,DEFAULT);
INSERT INTO "dba"."oj_ree" ("oj","nm","main_psn","mobiles","email","nodel","keyParms") VALUES(
DEFAULT,'Tornado','Serge','099965730','mail@gmail.com',DEFAULT,DEFAULT);
INSERT INTO "dba"."oj_ree" ("oj","nm","main_psn","mobiles","email","nodel","keyParms") VALUES(
DEFAULT,'Tornado','Serge','099965730','mail@gmail.com',DEFAULT,DEFAULT);
INSERT INTO "dba"."oj_ree" ("oj","nm","main_psn","mobiles","email","nodel","keyParms") VALUES(
DEFAULT,'Tornado','Serge','099965730','mail@gmail.com',DEFAULT,DEFAULT);
INSERT INTO "dba"."oj_ree" ("oj","nm","main_psn","mobiles","email","nodel","keyParms") VALUES(
DEFAULT,'Tornado','Serge','099965730','mail@gmail.com',DEFAULT,DEFAULT);

When I try to do:

DELETE FROM "dba"."oj_ree" WHERE "oj"=3;

I see the silence and row is deleted.

asked 05 Dec '19, 08:51

Serge's gravatar image

Serge
12681016
accept rate: 0%

edited 05 Dec '19, 08:57

Uh, no...

INSTEAD OF n=1 ojl=3 nodell=0
INSTEAD OF n=1 ojl=3 nodell=1
INSTEAD OF n=1 ojl=3 nodell=0

17.0.9.4882

(05 Dec '19, 12:40) Breck Carter

Hm, with v17.0.10.5923, the row is not deleted, and the column nodel is set to 1, and the log tells "INSTEAD OF n=1 ojl=3 nodell=0". Another call of the DELETE statement sets the column back to 0, and the log does tell that, too.

So I think the instead of trigger does work as expected.

Note, your trigger is specified FOR EACH STATEMENT but does not expect more than one row to be deleted at one time as the second SELECT apparently just assumes one single row to be returned.

When more than one row is affected (say, by calling "DELETE FROM "dba"."oj_ree" WHERE "oj"<=3"), you get

Could not execute statement.
SELECT returns more than one row
SQLCODE=-185, ODBC 3 State="HY000"
Line 1, column 1
permanent link

answered 05 Dec '19, 12:39

Volker%20Barth's gravatar image

Volker Barth
36.4k343504757
accept rate: 34%

converted 05 Dec '19, 12:39

1

I wish I had problems like this :)

(05 Dec '19, 13:56) Breck Carter
permanent link

answered 06 Dec '19, 14:21

Serge's gravatar image

Serge
12681016
accept rate: 0%

I just downloaded the Developer Edition and it gave me build 2053, so my best guess is... to get a more recent build you'll have to buy a copy, then use the s-user-id to gain access to the recent EBFs.

...and please ignore my unjustified meme.

(06 Dec '19, 15:02) Breck Carter

See also that FAQ...

(06 Dec '19, 16:13) Volker Barth
2

We expect to be updating the developer edition in early 2020.

(06 Dec '19, 19:50) Chris Keating
Replies hidden

Chris, I do not use the Developer Edition myself so I'm not personally affected - but I think it's quite difficult to understand that the "current" setup uses a build which was published two and a half years ago, IIRC...

(09 Dec '19, 03:23) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×171
×72
×41

question asked: 05 Dec '19, 08:51

question was seen: 310 times

last updated: 09 Dec '19, 03:23