Trigger problems
On IDS/DB-Access 7.31.FD9, the poster's UPDATE trigger worked when it just ran two procedures unconditionally, but adding a WHEN clause so the second EXECUTE PROCEDURE ran only on changed columns produced error -201 (syntax error). The cause turned out to be bracket placement: each triggered-action list must be closed before the WHEN clause, i.e. "(EXECUTE PROCEDURE a(...)), WHEN (condition) (EXECUTE PROCEDURE b(...));" rather than wrapping everything in one outer pair of parentheses. Heinz and theBP posted corrected syntax, the latter with a tested worked example on the same version. Others suggested moving the logic into a wrapper procedure, which is what the poster finally did.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Firstly, lets get out of the way the fact that we are very old version
of ISQL and IDS, but THERE IS NO CHOICE - at least not between now and
the Xmas/New Year break. Replies telling me to upgrade are unhelpful
in the extreme - I know the customer should upgrade, they know they
should upgrade, we were going to do it but I was seriously ill last
year, was off work for 4 months and that clobbered our plans totally.
Not to mention that they would have to upgrade Solaris first to get to
the latest version of IDS...
> dbaccess -v
DB-Access Version 7.31.FD9
> onstat -p | headIBM Informix Dynamic Server Version 7.31.FD9
I need to create a trigger that executes two stored procedures. If
both are always to be executed there is no problem:
CREATE TRIGGER del_dependants
DELETE ON dependants
REFERENCING OLD AS pre_del
FOR EACH ROW
(
EXECUTE PROCEDURE del_dep_extra(pre_del.dep_key),
EXECUTE PROCEDURE ins_former_ten(
"D", pre_del.joint_ten,
pre_del.dep_key,
pre_del.pro_ref,
pre_del.ten_ref,
pre_del.surname,
pre_del.forenames,
pre_del.title,
pre_del.sex,
pre_del.date_of_birth,
"", "", "" )
);
But when one is to be conditionally executed there *is* a problem:
CREATE TRIGGER upd_dependants UPDATE ON dependants
REFERENCING OLD AS pre_upd NEW AS post_upd
FOR EACH ROW
(
EXECUTE PROCEDURE upd_dep_extra(
post_upd.dep_key, pre_upd.surname, post_upd.surname ),
--WHEN (
--(post_upd.title != pre_upd.title )
--OR (post_upd.forenames != pre_upd.forenames )
--OR (post_upd.surname != pre_upd.surname)
--OR (post_upd.sex != pre_upd.sex)
--OR (post_upd.date_of_birth != pre_upd.date_of_birth)
--)
--(
EXECUTE PROCEDURE ins_former_ten(
"U", pre_upd.joint_ten, 0,
pre_upd.pro_ref,
pre_upd.ten_ref,
pre_upd.surname,
pre_upd.forenames,
pre_upd.title,
pre_upd.sex,
pre_upd.date_of_birth,
post_upd.surname,
post_upd.forenames,
post_upd.title )
--)
);
It works fine with the comments in but if I take out the comments '--'
then I get the following error:
CREATE TRIGGER upd_dependants UPDATE ON dependants
REFERENCING OLD AS pre_upd NEW AS post_upd
FOR EACH ROW
(
EXECUTE PROCEDURE upd_dep_extra(
post_upd.dep_key, pre_upd.surname, post_upd.surname ),# ^
# 201: A syntax error has occurred.
#
I am pretty sure I don't have a mismatch on brackets, of course I
might be wrong, but have tried to layout the SQL to make sure they are
correct. Reading the documentation suggests I should be able to do
what I am trying to do, so I am baffled.
Any help greatly appreciated, even if it's just telling me it can't be
done with these versions of Informix.
--
Surfer!
Email to: ramwater at uk2 dot net
Cats wrote:
> Firstly, lets get out of the way the fact that we are very old version
> of ISQL and IDS, but THERE IS NO CHOICE - at least not between now and
> the Xmas/New Year break. Replies telling me to upgrade are unhelpful
> in the extreme - I know the customer should upgrade, they know they
> should upgrade, we were going to do it but I was seriously ill last
> year, was off work for 4 months and that clobbered our plans totally.
> Not to mention that they would have to upgrade Solaris first to get to
> the latest version of IDS...
>
>> dbaccess -v
> DB-Access Version 7.31.FD9>
>> onstat -p | head> IBM Informix Dynamic Server Version 7.31.FD9
>
>
>
> I need to create a trigger that executes two stored procedures. If
> both are always to be executed there is no problem:
>
> CREATE TRIGGER del_dependants
> DELETE ON dependants
> REFERENCING OLD AS pre_del
> FOR EACH ROW
> (
> EXECUTE PROCEDURE del_dep_extra(pre_del.dep_key),>
> EXECUTE PROCEDURE ins_former_ten(
> "D", pre_del.joint_ten,
> pre_del.dep_key,
> pre_del.pro_ref,
> pre_del.ten_ref,
> pre_del.surname,
> pre_del.forenames,
> pre_del.title,
> pre_del.sex,
> pre_del.date_of_birth,
> "", "", "" )
> );>
>
> But when one is to be conditionally executed there *is* a problem:
>
> CREATE TRIGGER upd_dependants UPDATE ON dependants
> REFERENCING OLD AS pre_upd NEW AS post_upd
> FOR EACH ROW
> (
> EXECUTE PROCEDURE upd_dep_extra(
> post_upd.dep_key, pre_upd.surname, post_upd.surname ),>
> --WHEN (
> --(post_upd.title != pre_upd.title )
> --OR (post_upd.forenames != pre_upd.forenames )
> --OR (post_upd.surname != pre_upd.surname)
> --OR (post_upd.sex != pre_upd.sex)
> --OR (post_upd.date_of_birth != pre_upd.date_of_birth)
> --)
> --(
> EXECUTE PROCEDURE ins_former_ten(
> "U", pre_upd.joint_ten, 0,
> pre_upd.pro_ref,
> pre_upd.ten_ref,
> pre_upd.surname,
> pre_upd.forenames,
> pre_upd.title,
> pre_upd.sex,
> pre_upd.date_of_birth,
> post_upd.surname,
> post_upd.forenames,
> post_upd.title )
> --)
> );>
> It works fine with the comments in but if I take out the comments '--'
> then I get the following error:
>
> CREATE TRIGGER upd_dependants UPDATE ON dependants
> REFERENCING OLD AS pre_upd NEW AS post_upd
> FOR EACH ROW
> (
> EXECUTE PROCEDURE upd_dep_extra(
> post_upd.dep_key, pre_upd.surname, post_upd.surname ),> # ^
> # 201: A syntax error has occurred.
> #
>
> I am pretty sure I don't have a mismatch on brackets, of course I
> might be wrong, but have tried to layout the SQL to make sure they are
> correct. Reading the documentation suggests I should be able to do
> what I am trying to do, so I am baffled.
>
> Any help greatly appreciated, even if it's just telling me it can't be
> done with these versions of Informix.
Have you tried upgrading to a more recent version of Informix? :o)
Sorry, I had to say it.
What happens if you move the condition into the stored procedure?
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.
Hi,
Try
CREATE TRIGGER upd_dependants UPDATE ON dependants
REFERENCING OLD AS pre_upd NEW AS post_upd
FOR EACH ROW
(
EXECUTE PROCEDURE upd_dep_extra(
post_upd.dep_key, pre_upd.surname, post_upd.surname )),
WHEN (
(post_upd.title != pre_upd.title )
OR (post_upd.forenames != pre_upd.forenames )
OR (post_upd.surname != pre_upd.surname)
OR (post_upd.sex != pre_upd.sex)
OR (post_upd.date_of_birth != pre_upd.date_of_birth)
)
(
EXECUTE PROCEDURE ins_former_ten(
"U", pre_upd.joint_ten, 0,
pre_upd.pro_ref,
pre_upd.ten_ref,
pre_upd.surname,
pre_upd.forenames,
pre_upd.title,
pre_upd.sex,
pre_upd.date_of_birth,
post_upd.surname,
post_upd.forenames,
post_upd.title )
);
Heinz
>>> Cats <ramwater@uk2.net> 08.06.2009 11:07 >>>
Firstly, lets get out of the way the fact that we are very old version
of ISQL and IDS, but THERE IS NO CHOICE - at least not between now and
the Xmas/New Year break. Replies telling me to upgrade are unhelpful
in the extreme - I know the customer should upgrade, they know they
should upgrade, we were going to do it but I was seriously ill last
year, was off work for 4 months and that clobbered our plans totally.
Not to mention that they would have to upgrade Solaris first to get to
the latest version of IDS...
> dbaccess -v
DB-Access Version 7.31.FD9
> onstat -p | headIBM Informix Dynamic Server Version 7.31.FD9
I need to create a trigger that executes two stored procedures. If
both are always to be executed there is no problem:
CREATE TRIGGER del_dependants
DELETE ON dependants
REFERENCING OLD AS pre_del
FOR EACH ROW
(
EXECUTE PROCEDURE del_dep_extra(pre_del.dep_key),
EXECUTE PROCEDURE ins_former_ten(
"D", pre_del.joint_ten,
pre_del.dep_key,
pre_del.pro_ref,
pre_del.ten_ref,
pre_del.surname,
pre_del.forenames,
pre_del.title,
pre_del.sex,
pre_del.date_of_birth,
"", "", "" )
);
But when one is to be conditionally executed there *is* a problem:
CREATE TRIGGER upd_dependants UPDATE ON dependants
REFERENCING OLD AS pre_upd NEW AS post_upd
FOR EACH ROW
(
EXECUTE PROCEDURE upd_dep_extra(
post_upd.dep_key, pre_upd.surname, post_upd.surname ),
--WHEN (
--(post_upd.title != pre_upd.title )
--OR (post_upd.forenames != pre_upd.forenames )
--OR (post_upd.surname != pre_upd.surname)
--OR (post_upd.sex != pre_upd.sex)
--OR (post_upd.date_of_birth != pre_upd.date_of_birth)
--)
--(
EXECUTE PROCEDURE ins_former_ten(
"U", pre_upd.joint_ten, 0,
pre_upd.pro_ref,
pre_upd.ten_ref,
pre_upd.surname,
pre_upd.forenames,
pre_upd.title,
pre_upd.sex,
pre_upd.date_of_birth,
post_upd.surname,
post_upd.forenames,
post_upd.title )
--)
);
It works fine with the comments in but if I take out the comments '--'
then I get the following error:
CREATE TRIGGER upd_dependants UPDATE ON dependants
REFERENCING OLD AS pre_upd NEW AS post_upd
FOR EACH ROW
(
EXECUTE PROCEDURE upd_dep_extra(
post_upd.dep_key, pre_upd.surname, post_upd.surname ),# ^
# 201: A syntax error has occurred.
#
I am pretty sure I don't have a mismatch on brackets, of course I
might be wrong, but have tried to layout the SQL to make sure they are
correct. Reading the documentation suggests I should be able to do
what I am trying to do, so I am baffled.
Any help greatly appreciated, even if it's just telling me it can't be
done with these versions of Informix.
--
Surfer!
Email to: ramwater at uk2 dot net
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
--
WESTFLEISCH eG * Hauptsitz: Brockhoffstr. 11, 48143 Münster
Amtsgericht Münster: Gen.-Reg. 307
Aufsichtsratsvorsitzender: Heinz Westkämper
Vorstand: Dirk Niederstucke, Peter Piekenbrock, Josef Lehmenkühler, Dr.
Bernd Cordes, Dr. Helfried Giesen
Hinweise: Es können nur Mails bis 30 MB empfangen werden.
PowerPoint-Dateien müssen in eine ZIP-Datei gepackt werden.
--------------------------------------------------
Cheap solution? Execute a third procedure always that makes the logic
decision whether to run one of these two procedures or both of them.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Jun 8, 2009 at 5:07 AM, Cats <ramwater@uk2.net> wrote:
> Firstly, lets get out of the way the fact that we are very old version
> of ISQL and IDS, but THERE IS NO CHOICE - at least not between now and
> the Xmas/New Year break. Replies telling me to upgrade are unhelpful
> in the extreme - I know the customer should upgrade, they know they
> should upgrade, we were going to do it but I was seriously ill last
> year, was off work for 4 months and that clobbered our plans totally.
> Not to mention that they would have to upgrade Solaris first to get to
> the latest version of IDS...
>
> > dbaccess -v
> DB-Access Version 7.31.FD9>
> > onstat -p | head> IBM Informix Dynamic Server Version 7.31.FD9
>
>
>
> I need to create a trigger that executes two stored procedures. If
> both are always to be executed there is no problem:
>
> CREATE TRIGGER del_dependants
> DELETE ON dependants
> REFERENCING OLD AS pre_del
> FOR EACH ROW
> (
> EXECUTE PROCEDURE del_dep_extra(pre_del.dep_key),>
> EXECUTE PROCEDURE ins_former_ten(
> "D", pre_del.joint_ten,
> pre_del.dep_key,
> pre_del.pro_ref,
> pre_del.ten_ref,
> pre_del.surname,
> pre_del.forenames,
> pre_del.title,
> pre_del.sex,
> pre_del.date_of_birth,
> "", "", "" )
> );>
>
> But when one is to be conditionally executed there *is* a problem:
>
> CREATE TRIGGER upd_dependants UPDATE ON dependants
> REFERENCING OLD AS pre_upd NEW AS post_upd
> FOR EACH ROW
> (
> EXECUTE PROCEDURE upd_dep_extra(
> post_upd.dep_key, pre_upd.surname, post_upd.surname ),>
> --WHEN (
> --(post_upd.title != pre_upd.title )
> --OR (post_upd.forenames != pre_upd.forenames )
> --OR (post_upd.surname != pre_upd.surname)
> --OR (post_upd.sex != pre_upd.sex)
> --OR (post_upd.date_of_birth != pre_upd.date_of_birth)
> --)
> --(
> EXECUTE PROCEDURE ins_former_ten(
> "U", pre_upd.joint_ten, 0,
> pre_upd.pro_ref,
> pre_upd.ten_ref,
> pre_upd.surname,
> pre_upd.forenames,
> pre_upd.title,
> pre_upd.sex,
> pre_upd.date_of_birth,
> post_upd.surname,
> post_upd.forenames,
> post_upd.title )
> --)
> );>
> It works fine with the comments in but if I take out the comments '--'
> then I get the following error:
>
> CREATE TRIGGER upd_dependants UPDATE ON dependants
> REFERENCING OLD AS pre_upd NEW AS post_upd
> FOR EACH ROW
> (
> EXECUTE PROCEDURE upd_dep_extra(
> post_upd.dep_key, pre_upd.surname, post_upd.surname ),> # ^
> # 201: A syntax error has occurred.
> #
>
> I am pretty sure I don't have a mismatch on brackets, of course I
> might be wrong, but have tried to layout the SQL to make sure they are
> correct. Reading the documentation suggests I should be able to do
> what I am trying to do, so I am baffled.
>
> Any help greatly appreciated, even if it's just telling me it can't be
> done with these versions of Informix.
>
> --
> Surfer!
> Email to: ramwater at uk2 dot net
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>
On Jun 8, 11:26 am, Obnoxio The Clown <obno...@serendipita.com> wrote:
> Cats wrote:
> > Firstly, lets get out of the way the fact that we are very old version
> > of ISQL and IDS, but THERE IS NO CHOICE - at least not between now and
> > the Xmas/New Year break. Replies telling me to upgrade are unhelpful
> > in the extreme - I know the customer should upgrade, they know they
> > should upgrade, we were going to do it but I was seriously ill last
> > year, was off work for 4 months and that clobbered our plans totally.
> > Not to mention that they would have to upgrade Solaris first to get to
> > the latest version of IDS...
>
> >> dbaccess -v
> > DB-Access Version 7.31.FD9>
> >> onstat -p | head> > IBM Informix Dynamic Server Version 7.31.FD9
>
> > I need to create a trigger that executes two stored procedures. If
> > both are always to be executed there is no problem:
>
> > CREATE TRIGGER del_dependants> > DELETE ON dependants
> > REFERENCING OLD AS pre_del
> > FOR EACH ROW
> > (
> > EXECUTE PROCEDURE del_dep_extra(pre_del.dep_key),>
> > EXECUTE PROCEDURE ins_former_ten(
> > "D", pre_del.joint_ten,
> > pre_del.dep_key,
> > pre_del.pro_ref,
> > pre_del.ten_ref,
> > pre_del.surname,
> > pre_del.forenames,
> > pre_del.title,
> > pre_del.sex,
> > pre_del.date_of_birth,
> > "", "", "" )
> > );>
> > But when one is to be conditionally executed there *is* a problem:
>
> > CREATE TRIGGER upd_dependants UPDATE ON dependants> > REFERENCING OLD AS pre_upd NEW AS post_upd
> > FOR EACH ROW
> > (
> > EXECUTE PROCEDURE upd_dep_extra(
> > post_upd.dep_key, pre_upd.surname, post_upd.surname ),>
> > --WHEN (
> > --(post_upd.title != pre_upd.title )
> > --OR (post_upd.forenames != pre_upd.forenames )
> > --OR (post_upd.surname != pre_upd.surname)
> > --OR (post_upd.sex != pre_upd.sex)
> > --OR (post_upd.date_of_birth != pre_upd.date_of_birth)
> > --)
> > --(
> > EXECUTE PROCEDURE ins_former_ten(
> > "U", pre_upd.joint_ten, 0,
> > pre_upd.pro_ref,
> > pre_upd.ten_ref,
> > pre_upd.surname,
> > pre_upd.forenames,
> > pre_upd.title,
> > pre_upd.sex,
> > pre_upd.date_of_birth,
> > post_upd.surname,
> > post_upd.forenames,
> > post_upd.title )
> > --)
> > );>
> > It works fine with the comments in but if I take out the comments '--'
> > then I get the following error:
>
> > CREATE TRIGGER upd_dependants UPDATE ON dependants> > REFERENCING OLD AS pre_upd NEW AS post_upd
> > FOR EACH ROW
> > (
> > EXECUTE PROCEDURE upd_dep_extra(
> > post_upd.dep_key, pre_upd.surname, post_upd.surname ),> > # ^
> > # 201: A syntax error has occurred.
> > #
>
> > I am pretty sure I don't have a mismatch on brackets, of course I
> > might be wrong, but have tried to layout the SQL to make sure they are
> > correct. Reading the documentation suggests I should be able to do
> > what I am trying to do, so I am baffled.
>
> > Any help greatly appreciated, even if it's just telling me it can't be
> > done with these versions of Informix.
>
> Have you tried upgrading to a more recent version of Informix? :o)
>
> Sorry, I had to say it.
>
> What happens if you move the condition into the stored procedure?
Yes, I'm sure that will work but it will mean altering the stored
procedure and the other places it's called from - plus I'd like to see
if there is an answer!
Cats wrote:
> Firstly, lets get out of the way the fact that we are very old version
> of ISQL and IDS, but THERE IS NO CHOICE - at least not between now and
> the Xmas/New Year break. Replies telling me to upgrade are unhelpful
> in the extreme - I know the customer should upgrade, they know they
> should upgrade, we were going to do it but I was seriously ill last
> year, was off work for 4 months and that clobbered our plans totally.
> Not to mention that they would have to upgrade Solaris first to get to
> the latest version of IDS...
>
>> dbaccess -v
> DB-Access Version 7.31.FD9>
>> onstat -p | head> IBM Informix Dynamic Server Version 7.31.FD9
>
>
>
> I need to create a trigger that executes two stored procedures. If
> both are always to be executed there is no problem:
>
> CREATE TRIGGER del_dependants
> DELETE ON dependants
> REFERENCING OLD AS pre_del
> FOR EACH ROW
> (
> EXECUTE PROCEDURE del_dep_extra(pre_del.dep_key),>
> EXECUTE PROCEDURE ins_former_ten(
> "D", pre_del.joint_ten,
> pre_del.dep_key,
> pre_del.pro_ref,
> pre_del.ten_ref,
> pre_del.surname,
> pre_del.forenames,
> pre_del.title,
> pre_del.sex,
> pre_del.date_of_birth,
> "", "", "" )
> );>
>
> But when one is to be conditionally executed there *is* a problem:
>
> CREATE TRIGGER upd_dependants UPDATE ON dependants
> REFERENCING OLD AS pre_upd NEW AS post_upd
> FOR EACH ROW
> (
> EXECUTE PROCEDURE upd_dep_extra(
> post_upd.dep_key, pre_upd.surname, post_upd.surname ),>
> --WHEN (
> --(post_upd.title != pre_upd.title )
> --OR (post_upd.forenames != pre_upd.forenames )
> --OR (post_upd.surname != pre_upd.surname)
> --OR (post_upd.sex != pre_upd.sex)
> --OR (post_upd.date_of_birth != pre_upd.date_of_birth)
> --)
> --(
> EXECUTE PROCEDURE ins_former_ten(
> "U", pre_upd.joint_ten, 0,
> pre_upd.pro_ref,
> pre_upd.ten_ref,
> pre_upd.surname,
> pre_upd.forenames,
> pre_upd.title,
> pre_upd.sex,
> pre_upd.date_of_birth,
> post_upd.surname,
> post_upd.forenames,
> post_upd.title )
> --)
> );>
> It works fine with the comments in but if I take out the comments '--'
> then I get the following error:
>
> CREATE TRIGGER upd_dependants UPDATE ON dependants
> REFERENCING OLD AS pre_upd NEW AS post_upd
> FOR EACH ROW
> (
> EXECUTE PROCEDURE upd_dep_extra(
> post_upd.dep_key, pre_upd.surname, post_upd.surname ),> # ^
> # 201: A syntax error has occurred.
> #
>
> I am pretty sure I don't have a mismatch on brackets, of course I
> might be wrong, but have tried to layout the SQL to make sure they are
> correct. Reading the documentation suggests I should be able to do
> what I am trying to do, so I am baffled.
>
> Any help greatly appreciated, even if it's just telling me it can't be
> done with these versions of Informix.
>
> --
> Surfer!
> Email to: ramwater at uk2 dot net
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
On Jun 8, 12:08 pm, Art Kagel <art.ka...@gmail.com> 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.
Look again at Heinz's solution: you have the brackets in the wrong place :-) Regards, Doug Lawry "Cats" <ramwater@uk2.net> wrote in message news:b95ea07a-720d-43b8-b01c-f3c71345ee76@3g2000yqk.googlegroups.com... > On Jun 8, 12:08 pm, Art Kagel <art.ka...@gmail.com> 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.
Cats wrote:
> On Jun 8, 12:08 pm, Art Kagel <art.ka...@gmail.com> 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) :
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
In message <4JsXl.32794$Vq3.6645@newsfe30.ams2>, theBP
<theBP@Usenet-News.Net> writes
>Cats wrote:
>> On Jun 8, 12:08 pm, Art Kagel <art.ka...@gmail.com> 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