|
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. |
|
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 |
|
I`m using v17.0.4.2053 the only reached for developer version on SQL Anywhere Update Service (http://sqlasupport.sap.com/update_checker/updateservice?vendor1=iAnywhere&p1=SA:DB&v1=17.0.4.2053&os1=WIN32&p2=SA:DB&os2=WIN_X64&p3=SA:CL&p4=SA:CL&os4=WIN32&p5=SA:DOC_EN&v5=17.0.4.0&os5=WIN_X64&p6=SA:AT&v6=17.0.4.2053&os6=WIN32&p7=SA:AT&os7=WIN_X64&p8=SA:EC&bitness=64&show_maint=false&show_info=false&show_notice=false&show_ebf=true). Please, how I can get v17.0.10.5923 in my case, if it possible? 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
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
|
Uh, no...
17.0.9.4882