Re: Trigger problems

In message <4JsXl.32794$Vq3.6645@xxxxxxxxxxxxx>, theBP <theBP@xxxxxxxxxxxxxxx> writes
Cats wrote:
On Jun 8, 12:08 pm, Art Kagel <art.ka...@xxxxxxxxx> wrote:
Cheap solution? Execute a third procedure always that makes the logic
decision whether to run one of these two procedures or both of them.
Yes, that one occured as well! No-one seems to have spotted anything
obvious wrong in the syntax, no-one has said it can't be done in the
Informix versions I'm using, so I guess I'll have to amend the SPs one
way or another.

Well, a bit of patience (and doing it in your version) :

Sort of suggests it wasn't 100% obvious! Thanks. I have to confess I used a different solution - an ON UPDATE procedure that calls the other two procedures... After a bit of thinking I decided it might be easier in the long run. But I will inwardly digest the solutions offered here and thanks to all for them.

BTW am amazed anyone else has such an old version available!


create database jj_trigger in dbspace1 with buffered log;

create table tab1 (col1 int);
create table tab2 (col1 int);
create table tab3 (col1 int);

create procedure upd_allways_spl (col1 int)
insert into tab2 values (col1);
end procedure;

create procedure upd_ifdiff_spl (col1 int)
insert into tab3 values (col1);
end procedure;

CREATE TRIGGER update_tab1 UPDATE ON tab1
REFERENCING OLD AS pre_upd NEW AS post_upd
FOR EACH ROW
(execute procedure upd_allways_spl (post_upd.col1)),
when (post_upd.col1 != pre_upd.col1)
(execute procedure upd_ifdiff_spl (pre_upd.col1));

insert into tab1 values (1);
update tab1 set col1 = 1 where col1 = 1;
update tab1 set col1 = 2 where col1 = 1;

select "tab1", * from tab1;
select "tab2", * from tab2;
select "tab3", * from tab3;

(constant) col1

tab1 2


(constant) col1

tab2 1
tab2 2


(constant) col1

tab3 1

--
Surfer!
Email to: ramwater at uk2 dot net
.

Relevant Pages

  • Re: Trigger problems
    ... create table tab1 (col1 int); ... insert into tab3 values; ... CREATE TRIGGER update_tab1 UPDATE ON tab1 ...
    (comp.databases.informix)
  • Re: Sql-Abfrage mit Update
    ... test=# create table tab1 (nr int, col1 int); ... test=# insert into tab2 values; ...
    (de.comp.datenbanken.misc)
  • Re: Flushing
    ... col1 INT NOT NULL PRIMARY KEY, ... INSERT INTO TEST (col1) VALUES ...
    (microsoft.public.sqlserver.server)
  • Re: duplicate records
    ... "Plamen Ratchev" wrote: ... keycol INT NOT NULL PRIMARY KEY, ... SELECT col1, col2 ... SELECT col1, col2, col3, ...
    (microsoft.public.sqlserver.programming)
  • Re: Select that returns one row instead of two ones
    ... I'd strongly recommend you doing such reports on the client side ... col INT NOT NULL PRIMARY KEY, ... col1 VARCHARNOT NULL ...
    (microsoft.public.sqlserver.programming)