Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file
Hi,
I don't if its possible to do the following on Informix 7.30 NT I get a SQL
ERROR -201, probably wrong version or syntax error.
Is this supported on IDS2000 ?
create trigger TGU_getrws
UPDATE of statcond ON COM_RWS_LST
REFERENCING NEW AS val
FOR EACH ROW WHEN(val.statcond = 'L')
(UNLOAD TO 'D:\\data\\blabla' DELIMITER '|' SELECT statid from COM_RWS_LST
WHERE statcond = 'L')
OR
create trigger TGU_getrws
UPDATE of statcond ON COM_RWS_LST
REFERENCING NEW AS val
FOR EACH ROW WHEN(val.statcond = 'L')
(EXECUTE PROCEDURE P_tst_rws())
CREATE PROCEDURE P_tst_rws()
...
UNLOAD TO 'D:\\data\\blabla' DELIMITER '|' SELECT statid fromCOM_RWS_LST WHERE statcond = 'L'
END PROCEDURE;
Thanks for any comment on this
Dario
Your problem is related to the UNLOAD command which is a dbaccess command, not
an SQL statement. This prevents it from being used in Triggers or Stored
procedures.
In theory, you could invoke the SYSTEM command, in an SP, to unload your data,
but the SYSTEM command is quite expensive. Your best bet is to store such
inconsistencies in a table, unloading it once in a while through a separate
process or reporting directly from that table.
Rudy
Dario Rossa wrote:
> Hi,
>
> I don't if its possible to do the following on Informix 7.30 NT I get a SQL
> ERROR -201, probably wrong version or syntax error.
> Is this supported on IDS2000 ?
>
> create trigger TGU_getrws
> UPDATE of statcond ON COM_RWS_LST
> REFERENCING NEW AS val
> FOR EACH ROW WHEN(val.statcond = 'L')
> (UNLOAD TO 'D:\\data\\blabla' DELIMITER '|' SELECT statid from COM_RWS_LST
> WHERE statcond = 'L')> ...
> ...
> UNLOAD TO 'D:\\data\\blabla' DELIMITER '|' SELECT statid from> COM_RWS_LST WHERE statcond = 'L'
> END PROCEDURE;
> Dario
Not possible because the UNLOAD command is a built-in verb in dbaccess,
isql, 4gl, and Jonathan Leffler's sqlcmd and NOT a command that the database
server understands or even sees.
Art S. Kagel
Dario Rossa wrote:
>
> Hi,
>
> I don't if its possible to do the following on Informix 7.30 NT I get a SQL
> ERROR -201, probably wrong version or syntax error.
> Is this supported on IDS2000 ?
>
> create trigger TGU_getrws
> UPDATE of statcond ON COM_RWS_LST
> REFERENCING NEW AS val
> FOR EACH ROW WHEN(val.statcond = 'L')
> (UNLOAD TO 'D:\\data\\blabla' DELIMITER '|' SELECT statid from COM_RWS_LST
> WHERE statcond = 'L')>
> OR
>
> create trigger TGU_getrws
> UPDATE of statcond ON COM_RWS_LST
> REFERENCING NEW AS val
> FOR EACH ROW WHEN(val.statcond = 'L')
> (EXECUTE PROCEDURE P_tst_rws())>
> CREATE PROCEDURE P_tst_rws()
> ...
> UNLOAD TO 'D:\\data\\blabla' DELIMITER '|' SELECT statid from> COM_RWS_LST WHERE statcond = 'L'
> END PROCEDURE;
>
> Thanks for any comment on this
>
> Dario