RE: SPL and Triggers on the same table ?
Hi
The way I've got around an error -747 is to perform a system call, that
calls another script, which then updates the table.
{INSERT STARTED}
TRIGGER Bom_Trig -
procedure bom_shell_1();
PROCEDURE Bom_shell_1 -
SYSTEM "/scripts/bom_script_1 " || param1 ||param2
||param3 ||param4 ;
SCRIPT Bom_script_1 -
/scripts/bom_script_2 $1 $2 $3 $4 &
{INSERT COMPLETED}
{UPDATE STARTED}
SCRIPT bom_script_2 -
SLEEP 1
isql $MCOMP - <<EOF 2> /dev/null 1>> /dev/null
execute procedure bomdecbug($1, $2, $3, $4); EOF
{UPDATE COMPLETED}
The first script runs the second script in the background, and so
immediately returns to the called procedure and finishes. The second script
with `sleep 1` ensures that the origonal insert completes before the update
is actioned.
Hope this helps.
Wayne Sheldon
Computer Manager
Airflow Streamlines plc
Production Division
-----Original Message-----
From: Richard [mailto:richard@disctronics.co.uk]
Sent: Tuesday, January 11, 2000 11:17 AM
To: informix-list@iiug.org
Subject: SPL and Triggers on the same table ?
Hi,
I want to update a table using a trigger & procedure after an insert on the
table.
Here is the code :
create procedure bomdecbug (parent char(22), child char(22), fromd date,
till date)
-- update bom. if from date > 01/01/1900 set from date = 01/01/1900
-- if to date < 14/10/2173 set to date = 14/10/2173
define flag integer; -- 1 todate bad, 2 fromdate bad , 3 both bad, 0 ok
if (fromd > "01/01/1900") then
let flag = 1;
end if;
if (till < "14/10/2173") then
if (flag = 1) then
let flag = 3;
else
let flag = 2;
end if;
end if;
if (flag != 0) then
if (flag = 1) then
update bom set bomfrom = "01/01/1900"
where bomparent = parent and bomchild = child; elif (flag = 2) then
update bom set bomtill = "14/10/2173"
where bomparent = parent and bomchild = child; else
update bom set bomfrom = "01/01/1900", bomtill = "14/10/2173"
where bomparent = parent and bomchild = child; end if;
end if;
end procedure;
create trigger fixbombuginsert on bom
referencing new as this
for each row (execute procedure
bomdecbug(this.bomparent,this.bomchild,this.bomfrom,this.bomtill) );
However I am getting error -747:
-747 Table or column matches object referenced in triggering statement.
This error is returned when a triggered SQL statement acts on the
triggering table, or when both statements are updates and the
column being updated in the triggered action is the same as the
column being updated by the triggering statement.
Which means you cannot update on the table you are triggering upon (i
think!).
Does anyone know a way around this ?
Regards
Richard.