Re: [U2] Universe and triggers
Because you can not create a trigger on anything that's a directory, you can not create a trigger on a multi-level file per se (since the top level is merely a directory). However there ought to be nothing stopping you creating VOC pointers to the individual Type 18 second-level files and using those pointers to create triggers. > - Original Message - > From: "Marcos Fogaga" <[EMAIL PROTECTED]> > To: u2-users@listserver.u2ug.org > Subject: RES: RES: [U2] Universe and triggers > Date: Wed, 12 Sep 2007 11:57:22 -0300 > > > Hi Clif, > > My file is type 18 and it is multi data level. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
DOH! Sorry. Overlooked that. So much for speed reading. - Clif On Sep 13, 2007, at 3:38 AM, Brian Leach wrote: See point 6 on my post --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
Clif
See point 6 on my post
It's not enough on a 24x7 site to just have a wrapper if the name of the
subroutine doesn't change - UniVerse may still cache the object code in memory
if the file is opened e.g. to named common.
So I hold the name of the routine to call externally, and have the master
trigger read it. Then I can vary it when the subroutine changes (e.g. adding a
version number to the end).
Brian
Oooo! Now there's a neat trick (excuse me, "technique"). I'd never
thought of it.
Have you ever used Mark Baldridge's Profiler ("Travels With Mark," on
the IBM Developer Works site) to quantify what I assume is a rather
trivial amount of overhead in the double call? If not, I'll take a go
at it and report back. It sounds like a great technique. Thanks!
--
Regards,
Clif
~~~
W. Clifton Oliver, CCP
CLIFTON OLIVER & ASSOCIATES
Tel: +1 619 460 5678Web: www.oliver.com
~~~
On Sep 12, 2007, at 8:23 PM, Phil Walker wrote:
> What I do is create a wrapper trigger program which does nothing
> more than call the real subroutine. That way as this trigger never
> or rarely changes I can modify the underlying trigger program
> without having to drop and recreate the trigger on the file.
>
> Clifton Oliver <[EMAIL PROTECTED]> wrote:
>
> =
> Two more come to mind:
>
> If you change and recompile the subroutine, you have to drop and re-
> create the trigger.
>
> Files with triggers cannot be updated via UV/Net.
>
>
> --
>
> Regards,
>
> Clif
>
>
> On Sep 12, 2007, at 2:04 AM, Brian Leach wrote:
>
>> 5. Triggers impose some limitations : they all make sense if you
>> step back and think about them except (b):
> ---
> u2-users mailing list
> u2-users@listserver.u2ug.org
> To unsubscribe please visit http://listserver.u2ug.org/
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
Oooo! Now there's a neat trick (excuse me, "technique"). I'd never
thought of it.
Have you ever used Mark Baldridge's Profiler ("Travels With Mark," on
the IBM Developer Works site) to quantify what I assume is a rather
trivial amount of overhead in the double call? If not, I'll take a go
at it and report back. It sounds like a great technique. Thanks!
--
Regards,
Clif
~~~
W. Clifton Oliver, CCP
CLIFTON OLIVER & ASSOCIATES
Tel: +1 619 460 5678Web: www.oliver.com
~~~
On Sep 12, 2007, at 8:23 PM, Phil Walker wrote:
What I do is create a wrapper trigger program which does nothing
more than call the real subroutine. That way as this trigger never
or rarely changes I can modify the underlying trigger program
without having to drop and recreate the trigger on the file.
Clifton Oliver <[EMAIL PROTECTED]> wrote:
=
Two more come to mind:
If you change and recompile the subroutine, you have to drop and re-
create the trigger.
Files with triggers cannot be updated via UV/Net.
--
Regards,
Clif
On Sep 12, 2007, at 2:04 AM, Brian Leach wrote:
5. Triggers impose some limitations : they all make sense if you
step back and think about them except (b):
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Universe and triggers
Oops. Meant to type "not" able to TL a type 1 or 19. -C On Sep 12, 2007, at 1:15 PM, Clifton Oliver wrote: There currently isn't a .Type19 file; that's the default in the absence of a .Type1 or .Type30. But they could add one and do what you suggest. I know this has been discussed within IBM for some time. You have a similar issue for the same reason of being able to Transaction Log a type 1 or 19. -- Regards, Clif ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER & ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Sep 12, 2007, at 6:06 AM, Mats Carlid wrote: Clifton Oliver skrev: Since 10.0. Yes, it works on all file types except 1 and 19. Being directories, they don't have file headers, thus no place to put the SICA. It would be possible placing the SICA in a separate file .sica or in the .Type1 / .Type19 files wouldn't it? And it would really be nice and usefull to have triggers in directories too. -- mats --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
Two more come to mind: If you change and recompile the subroutine, you have to drop and re- create the trigger. Files with triggers cannot be updated via UV/Net. -- Regards, Clif On Sep 12, 2007, at 2:04 AM, Brian Leach wrote: 5. Triggers impose some limitations : they all make sense if you step back and think about them except (b): --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Universe and triggers
There currently isn't a .Type19 file; that's the default in the absence of a .Type1 or .Type30. But they could add one and do what you suggest. I know this has been discussed within IBM for some time. You have a similar issue for the same reason of being able to Transaction Log a type 1 or 19. -- Regards, Clif ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER & ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Sep 12, 2007, at 6:06 AM, Mats Carlid wrote: Clifton Oliver skrev: Since 10.0. Yes, it works on all file types except 1 and 19. Being directories, they don't have file headers, thus no place to put the SICA. It would be possible placing the SICA in a separate file .sica or in the .Type1 / .Type19 files wouldn't it? And it would really be nice and usefull to have triggers in directories too. -- mats --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RES: RES: [U2] Universe and triggers
Hi Clif, My file is type 18 and it is multi data level. DICT GVMTA030 D_GVMTA030 18 7 DATA GVMTA030,BAIXADOS GVMTA030/BAIXADOS18 3001 DATA GVMTA030,DELETADOS GVMTA030/DELETADOS 18 3001 DATA GVMTA030,GVMTA030 GVMTA030/GVMTA03018 5001 >.L GVMTA030 GVMTA030 001 F 002 GVMTA030 003 D_GVMTA030 004 M 005 006 007 BAIXADOS}DELETADOS}GVMTA030 008 BAIXADOS}DELETADOS}GVMTA030 Do you know what must I do to create a trigger in a file with these features? Thank you. Regards Marcos Fogaga -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de Clifton Oliver Enviada em: terga-feira, 11 de setembro de 2007 19:54 Para: u2-users@listserver.u2ug.org Assunto: Re: RES: [U2] Universe and triggers I use triggers on 10.2 files frequently. What file type is GVMTA030? If it's a type 1 or 19, it won't work. If it is a static hashed 2 through 18 it will. Also works on type 30. However, if it is a distributed file, you have to create the trigger on each individual partfile; you cannot name the top level DF. Also, do a tcl >.L GVMTA030 And see if there is an M in line four. Creating triggers on multi data level files requires some tricks as the comma is not valid in a file name for CREATE TRIGGER (SQL syntax restriction). -- Regards, Clif ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER & ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Sep 11, 2007, at 2:53 PM, Marcos Fogaga wrote: > Hi, > > My UV is release 10.2 and if I try convert a regular pick file > there are the > error message : > > CREATE TRIGGER T_TRIGGER AFTER INSERT OR UPDATE ON GVMTA030 FOR > EACH ROW > CALLING '*T_TRIGGER'; > UniVerse/SQL: Invalid file type specified. > Warning: SICA information could not be read properly!! > > T_TRIGGER > 001 SUBROUTINE > T_TRIGGER(PAR1,PAR2,PAR3,PAR4,PAR5,PAR6,PAR7,PAR8,PAR9,PAR10,PAR1 > 1,PAR12,PAR13,PAR14) > 002 RETURN > > What type of file are you using? > > Thanks in advance. > > Regards, > > Marcos Fogaca > > -Mensagem original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Em nome de gerry-u2ug > Enviada em: terga-feira, 11 de setembro de 2007 18:26 > Para: u2-users@listserver.u2ug.org > Assunto: RE: [U2] Universe and triggers > > Hi Marcos, what version of universe are you running ? > We used to use the index w subroutine method you describe but this is > now gone in favour of native universe triggers. We have many files in > universe with triggers on them , there is no requirement to SQL'ize > the > table and believe me these tables would take a LOT of work to make > them > sql conformant. > Universe will add a SICA for the table but that's it, any garbage > hashed > file works just fine afaik. > > > > > > -Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Sent: September 11, 2007 04:07 PM > To: u2-users@listserver.u2ug.org > Subject: Re: [U2] Universe and triggers > > Hi Marcos, > > Universe seems hellbent on invoking the entire SQL system in order to > put a simple trigger on a file. UniData is MUCH more forgiving in > that > regard. > > With Universe, I have had a certain amount of success making what I > call > 'indexed subroutines' where by an index on a file calls a virtual > Dictionary subroutine which does, in fact, operate as a rudimentary > trigger. You CANNOT affect data going to the actual file record > update > as you might with an actual trigger, but you can write information > to a > tracking database and then I have a background phantom in operation > which manages email distribution from the triggered events and what > not. > I *could* even write back to the originating file record that > initiated > the trigger -- I've closed off the mortal embrace between phantom and > virtual subroutine -- but that will always have a lag time between the > actual record update event and the final conclusion of the trigger, > so I > would not consider it a particularly clean solution for true database > triggering. It does work well as a tracking and reporting system, > though. > > Trey > > > -- Original message -- > From: Marcos Fogaga <[EMAIL PROTECTED]> >> Hi, >> >> >> >> It is possible create a trigger (subroutine) for a pick file (non SQL >> table)? >> >> On D3 we have CALLX, is there a feature like this in Universe? &
Re: [U2] Universe and triggers
Clifton Oliver skrev: Since 10.0. Yes, it works on all file types except 1 and 19. Being directories, they don't have file headers, thus no place to put the SICA. It would be possible placing the SICA in a separate file .sica or in the .Type1 / .Type19 files wouldn't it? And it would really be nice and usefull to have triggers in directories too. -- mats --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
Just to clarify 1. You can add triggers to non-sql files so long as they have a header. See the HELP SQL CREATE TRIGGER for the subroutine layout. 2. You can have up to 6 main triggers -before and after each type of event (insert, update, delete). 3. triggers must be globally cataloged since the file may be updated from any account - so a local catalog wouldn't be found. 4. To globally catalog copy the CATALOG entry from the VOC of the uv account to your local VOC (under a new name) and use that. Prefix the catalog name with an asterisk. 5. Triggers impose some limitations : they all make sense if you step back and think about them except (b): a. They observe some SQL constraints for isolation - so ALL writes must be lock protected. b. You can't move the file to a new platform (format.conv won't process it) c. Some commands are disallowed. d. No interaction is allowed, including the RAID debugger. 6. A useful tip - If a trigger changes you must ensure no-one has the file open. That's not always possible on a 24x7 system so I have a master trigger read a list of subtriggers from a file and call them in turn. Then I can back out a change by removing a routine from the list, or substitute a new routine at any time. 7. Triggers slow processing if over-used. Brian --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
I use triggers on 10.2 files frequently. What file type is GVMTA030? If it's a type 1 or 19, it won't work. If it is a static hashed 2 through 18 it will. Also works on type 30. However, if it is a distributed file, you have to create the trigger on each individual partfile; you cannot name the top level DF. Also, do a tcl >.L GVMTA030 And see if there is an M in line four. Creating triggers on multi data level files requires some tricks as the comma is not valid in a file name for CREATE TRIGGER (SQL syntax restriction). -- Regards, Clif ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER & ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Sep 11, 2007, at 2:53 PM, Marcos Fogaga wrote: Hi, My UV is release 10.2 and if I try convert a regular pick file there are the error message : CREATE TRIGGER T_TRIGGER AFTER INSERT OR UPDATE ON GVMTA030 FOR EACH ROW CALLING '*T_TRIGGER'; UniVerse/SQL: Invalid file type specified. Warning: SICA information could not be read properly!! T_TRIGGER 001 SUBROUTINE T_TRIGGER(PAR1,PAR2,PAR3,PAR4,PAR5,PAR6,PAR7,PAR8,PAR9,PAR10,PAR1 1,PAR12,PAR13,PAR14) 002 RETURN What type of file are you using? Thanks in advance. Regards, Marcos Fogaca -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de gerry-u2ug Enviada em: terga-feira, 11 de setembro de 2007 18:26 Para: u2-users@listserver.u2ug.org Assunto: RE: [U2] Universe and triggers Hi Marcos, what version of universe are you running ? We used to use the index w subroutine method you describe but this is now gone in favour of native universe triggers. We have many files in universe with triggers on them , there is no requirement to SQL'ize the table and believe me these tables would take a LOT of work to make them sql conformant. Universe will add a SICA for the table but that's it, any garbage hashed file works just fine afaik. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: September 11, 2007 04:07 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Universe and triggers Hi Marcos, Universe seems hellbent on invoking the entire SQL system in order to put a simple trigger on a file. UniData is MUCH more forgiving in that regard. With Universe, I have had a certain amount of success making what I call 'indexed subroutines' where by an index on a file calls a virtual Dictionary subroutine which does, in fact, operate as a rudimentary trigger. You CANNOT affect data going to the actual file record update as you might with an actual trigger, but you can write information to a tracking database and then I have a background phantom in operation which manages email distribution from the triggered events and what not. I *could* even write back to the originating file record that initiated the trigger -- I've closed off the mortal embrace between phantom and virtual subroutine -- but that will always have a lag time between the actual record update event and the final conclusion of the trigger, so I would not consider it a particularly clean solution for true database triggering. It does work well as a tracking and reporting system, though. Trey -- Original message -- From: Marcos Fogaga <[EMAIL PROTECTED]> Hi, It is possible create a trigger (subroutine) for a pick file (non SQL table)? On D3 we have CALLX, is there a feature like this in Universe? Thanks in advance. Best Regards, Marcos Fogaca Integral Sistemas Sao Paulo / Brazil --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
At 04:24 PM 9/11/2007, Marcos Fogaga wrote: >Hi Doug, > >The problem is that the TEST.FILE MUST be a UV SQL Table. In my case, will >be a problem if a need to convert my regular files using the command >CONVERT.SQL, it will be painful. > Not per this line in the FAQ as well as reports from some others posting that they implemented triggers on UV files. "From the 10.0 release of UniVerse, and later, it is possible to place a trigger on a UniVerse file." Doug Miller [EMAIL PROTECTED] Manager of Technical Services Strategy 7Dallas TX --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: RES: [U2] Universe and triggers
Not as of release 10.0. On Sep 11, 2007, at 2:24 PM, Marcos Fogaga wrote: The problem is that the TEST.FILE MUST be a UV SQL Table. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Universe and triggers
Since 10.0. Yes, it works on all file types except 1 and 19. Being directories, they don't have file headers, thus no place to put the SICA. -- Regards, Clif On Sep 11, 2007, at 1:59 PM, Geoffrey Mitchell wrote: I'm not sure in what version it was implemented, but you *can* have triggers on non-SQL files in Universe (and have been able to for some time now). Cataloging the subroutine so that it can be called as a trigger is somewhat weird, and not well documented, but if you search the list archives, you should be able to find what you need to know. We have been using this in production for a couple of years. Actually installing the trigger uses the same syntax as for an SQL table. I believe that this works on all hashed file types (i.e. anything but type 1 or 19 files). --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RES: [U2] Universe and triggers
Hi, My UV is release 10.2 and if I try convert a regular pick file there are the error message : CREATE TRIGGER T_TRIGGER AFTER INSERT OR UPDATE ON GVMTA030 FOR EACH ROW CALLING '*T_TRIGGER'; UniVerse/SQL: Invalid file type specified. Warning: SICA information could not be read properly!! T_TRIGGER 001 SUBROUTINE T_TRIGGER(PAR1,PAR2,PAR3,PAR4,PAR5,PAR6,PAR7,PAR8,PAR9,PAR10,PAR1 1,PAR12,PAR13,PAR14) 002 RETURN What type of file are you using? Thanks in advance. Regards, Marcos Fogaca -Mensagem original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Em nome de gerry-u2ug Enviada em: terga-feira, 11 de setembro de 2007 18:26 Para: u2-users@listserver.u2ug.org Assunto: RE: [U2] Universe and triggers Hi Marcos, what version of universe are you running ? We used to use the index w subroutine method you describe but this is now gone in favour of native universe triggers. We have many files in universe with triggers on them , there is no requirement to SQL'ize the table and believe me these tables would take a LOT of work to make them sql conformant. Universe will add a SICA for the table but that's it, any garbage hashed file works just fine afaik. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: September 11, 2007 04:07 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Universe and triggers Hi Marcos, Universe seems hellbent on invoking the entire SQL system in order to put a simple trigger on a file. UniData is MUCH more forgiving in that regard. With Universe, I have had a certain amount of success making what I call 'indexed subroutines' where by an index on a file calls a virtual Dictionary subroutine which does, in fact, operate as a rudimentary trigger. You CANNOT affect data going to the actual file record update as you might with an actual trigger, but you can write information to a tracking database and then I have a background phantom in operation which manages email distribution from the triggered events and what not. I *could* even write back to the originating file record that initiated the trigger -- I've closed off the mortal embrace between phantom and virtual subroutine -- but that will always have a lag time between the actual record update event and the final conclusion of the trigger, so I would not consider it a particularly clean solution for true database triggering. It does work well as a tracking and reporting system, though. Trey -- Original message -- From: Marcos Fogaga <[EMAIL PROTECTED]> > Hi, > > > > It is possible create a trigger (subroutine) for a pick file (non SQL > table)? > > On D3 we have CALLX, is there a feature like this in Universe? > > > > Thanks in advance. > > > > Best Regards, > > > > Marcos Fogaca > > Integral Sistemas > > Sao Paulo / Brazil > --- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Universe and triggers
After FILETRIG is compiled it must be globally catalogued: >CATALOG BP FILETRIG "*ce*FILETRIG" cataloged. Note that, depending on your account flavor, this may not do what you need. I believe that you need an Information flavor catalog. We have a verb setup as follows: >ED VOC ICATALOG 6 lines long. : P 0001: V 0002: CATALOG 0003: I 0004: BDGZ 0005: catalog 0006: INFORMATION.FORMAT Using this will. globally catalog your subroutine. A pick format catalog will only locally catalog it (create an entry in the VOC on the local account). I could be wrong in some detail, but this is my understanding, and waht I have found to work. -- Geoffrey Mitchell Programmer/Analyst Home Decorator's Collection 314-684-1062 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RES: [U2] Universe and triggers
Hi Doug,
The problem is that the TEST.FILE MUST be a UV SQL Table. In my case, will
be a problem if a need to convert my regular files using the command
CONVERT.SQL, it will be painful.
Regards,
Marcos Fogaca
-Mensagem original-
De: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Em nome de Doug Miller
Enviada em: terga-feira, 11 de setembro de 2007 18:01
Para: u2-users@listserver.u2ug.org
Assunto: Re: [U2] Universe and triggers
At 02:01 PM 9/11/2007, you wrote:
>It is possible create a trigger (subroutine) for a pick file (non SQL
>table)?
Yes. I just so happened looked this up for someone else this
morning. The best source of documentation I came across was this FAQ.
File Trigger Example
Technote (FAQ)
Problem
Sometimes it is useful to find out what processing has occurred on a
file. For instance, records may have been deleted from a file in
error and these records need to be tracked. Placing a trigger on a
file which fires after the delete can keep a record of the event.
Cause
From the 10.0 release of UniVerse, and later, it is possible to
place a trigger on a UniVerse file.
Solution
UniVerse executes ("fires") triggers when some action changes the
file's data. UniVerse treats all events that change the database as
either an INSERT, UPDATE, or DELETE action. For example, a BASIC
WRITE statement would be either an INSERT or UPDATE action and a
BASIC DELETE would be treated as a DELETE action.
The following is an example of how to place a trigger on a file which
fires when a DELETE has taken place. The trigger updates a separate
file which can then be read to find out what was deleted.
Some example code for a trigger BASIC subroutine follows. It is
called FILETRIG and is saved in the BP file. A trigger BASIC
subroutine must contain 14 arguments in a specific order. For more
information on these arguments, see the chapter titled File Triggers
in the UniVerse System Description manual.
0001: SUBROUTINE
TEST(TEST,SCHEMA,TABLE,EVENT,TIME,NEWID,NEWREC,OLDID,OLDREC,ASSOC,ASSOC.EVEN
T,COUNT,CHAIN.CASCADE,CASCADE)
0002: COMMON /TEST/ AUDITFV
0003: FILEOPENED = FILEINFO(AUDITFV,0)
0004: IF FILEOPENED NE 1 THEN
0005: OPEN '','TRIG_AUDIT' TO AUDITFV ELSE CRT 'COULD NOT OPEN TRIG_AUDIT';
0006: RETURN
0007: END
0008: ITEM = 'Deleted ':OLDID:' in ':TABLE
0009: ID = TIMEDATE(): '*' :OLDID
0010: LOOP
0011: RECORDLOCKU AUDITFV,ID
0012: ON ERROR RETURN
0013: LOCKED
0014: NAP 250
0015: CONTINUE
0016: END
0017: EXIT
0018: REPEAT
0019: WRITE ITEM ON AUDITFV,ID
0020: RETURN
After FILETRIG is compiled it must be globally catalogued:
>CATALOG BP FILETRIG
"*ce*FILETRIG" cataloged.
Now create the trigger on the file in question:
>CREATE TRIGGER TEST.TRIGGER AFTER DELETE ON TEST.FILE FOR EACH ROW
CALLING "*ce*FILETRIG";
Adding trigger "TEST.TRIGGER"
Now create the file TRIG_AUDIT:
>CREATE.FILE TRIG_AUDIT 30
Creating file "TRIG_AUDIT" as Type 30.
Creating file "D_TRIG_AUDIT" as Type 3, Modulo 1, Separation 2.
Added "@ID", the default record for RetrieVe, to "D_TRIG_AUDIT".
Now, if a record is deleted from the file called TEST.FILE,
TEST.TRIGGER will fire and update the TRIG_AUDIT file. As an example,
record id 103 was deleted from TEST.FILE. Listing TRIG_AUDIT gives:
$<50>LIST TRIG_AUDIT 16:32:23 15-09-06 PAGE1
TRIG_AUDIT
16:32:16 1
5 SEP 2006
*103
1 records listed.
Doug Miller [EMAIL PROTECTED]
Manager of Technical Services
Strategy 7Dallas TX
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Universe and triggers
Trey, I do the same thing. You have to keep the actions taken by the trigger brief, though. You don't want to interfere with the entry, maintenance, and other updating that the file receives. - Chuck [EMAIL PROTECTED] wrote: Hi Marcos, With Universe, I have had a certain amount of success making what I call 'indexed subroutines' where by an index on a file calls a virtual Dictionary subroutine which does, in fact, operate as a rudimentary trigger. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Universe and triggers
I'm not sure in what version it was implemented, but you *can* have triggers on non-SQL files in Universe (and have been able to for some time now). Cataloging the subroutine so that it can be called as a trigger is somewhat weird, and not well documented, but if you search the list archives, you should be able to find what you need to know. We have been using this in production for a couple of years. Actually installing the trigger uses the same syntax as for an SQL table. I believe that this works on all hashed file types (i.e. anything but type 1 or 19 files). Geoff [EMAIL PROTECTED] wrote: >Hi Marcos, > >Universe seems hellbent on invoking the entire SQL system in order to put a simple trigger on a file. UniData is MUCH more forgiving in that regard. > >With Universe, I have had a certain amount of success making what I call 'indexed subroutines' where by an index on a file calls a virtual Dictionary subroutine which does, in fact, operate as a rudimentary trigger. You CANNOT affect data going to the actual file record update as you might with an actual trigger, but you can write information to a tracking database and then I have a background phantom in operation which manages email distribution from the triggered events and what not. I *could* even write back to the originating file record that initiated the trigger -- I've closed off the mortal embrace between phantom and virtual subroutine -- but that will always have a lag time between the actual record update event and the final conclusion of the trigger, so I would not consider it a particularly clean solution for true database triggering. It does work well as a tracking and reporting system, though. > >Trey > > > -- Original message -- >From: Marcos Fogaga <[EMAIL PROTECTED]> > > >>Hi, >> >> >> >>It is possible create a trigger (subroutine) for a pick file (non SQL >>table)? >> >>On D3 we have CALLX, is there a feature like this in Universe? >> >> >> >>Thanks in advance. >> >> >> >>Best Regards, >> >> >> >>Marcos Fogaca >> >>Integral Sistemas >> >>Sao Paulo / Brazil >>--- >>u2-users mailing list >>u2-users@listserver.u2ug.org >>To unsubscribe please visit http://listserver.u2ug.org/ >> >> >--- >u2-users mailing list >u2-users@listserver.u2ug.org >To unsubscribe please visit http://listserver.u2ug.org/ > > > > -- Geoffrey Mitchell Programmer/Analyst Home Decorator's Collection 314-684-1062 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Universe and triggers
Hi Marcos, what version of universe are you running ? We used to use the index w subroutine method you describe but this is now gone in favour of native universe triggers. We have many files in universe with triggers on them , there is no requirement to SQL'ize the table and believe me these tables would take a LOT of work to make them sql conformant. Universe will add a SICA for the table but that's it, any garbage hashed file works just fine afaik. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: September 11, 2007 04:07 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Universe and triggers Hi Marcos, Universe seems hellbent on invoking the entire SQL system in order to put a simple trigger on a file. UniData is MUCH more forgiving in that regard. With Universe, I have had a certain amount of success making what I call 'indexed subroutines' where by an index on a file calls a virtual Dictionary subroutine which does, in fact, operate as a rudimentary trigger. You CANNOT affect data going to the actual file record update as you might with an actual trigger, but you can write information to a tracking database and then I have a background phantom in operation which manages email distribution from the triggered events and what not. I *could* even write back to the originating file record that initiated the trigger -- I've closed off the mortal embrace between phantom and virtual subroutine -- but that will always have a lag time between the actual record update event and the final conclusion of the trigger, so I would not consider it a particularly clean solution for true database triggering. It does work well as a tracking and reporting system, though. Trey -- Original message -- From: Marcos Fogaga <[EMAIL PROTECTED]> > Hi, > > > > It is possible create a trigger (subroutine) for a pick file (non SQL > table)? > > On D3 we have CALLX, is there a feature like this in Universe? > > > > Thanks in advance. > > > > Best Regards, > > > > Marcos Fogaca > > Integral Sistemas > > Sao Paulo / Brazil > --- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Universe and triggers
At 02:01 PM 9/11/2007, you wrote:
It is possible create a trigger (subroutine) for a pick file (non SQL
table)?
Yes. I just so happened looked this up for someone else this
morning. The best source of documentation I came across was this FAQ.
File Trigger Example
Technote (FAQ)
Problem
Sometimes it is useful to find out what processing has occurred on a
file. For instance, records may have been deleted from a file in
error and these records need to be tracked. Placing a trigger on a
file which fires after the delete can keep a record of the event.
Cause
From the 10.0 release of UniVerse, and later, it is possible to
place a trigger on a UniVerse file.
Solution
UniVerse executes ("fires") triggers when some action changes the
file's data. UniVerse treats all events that change the database as
either an INSERT, UPDATE, or DELETE action. For example, a BASIC
WRITE statement would be either an INSERT or UPDATE action and a
BASIC DELETE would be treated as a DELETE action.
The following is an example of how to place a trigger on a file which
fires when a DELETE has taken place. The trigger updates a separate
file which can then be read to find out what was deleted.
Some example code for a trigger BASIC subroutine follows. It is
called FILETRIG and is saved in the BP file. A trigger BASIC
subroutine must contain 14 arguments in a specific order. For more
information on these arguments, see the chapter titled File Triggers
in the UniVerse System Description manual.
0001: SUBROUTINE
TEST(TEST,SCHEMA,TABLE,EVENT,TIME,NEWID,NEWREC,OLDID,OLDREC,ASSOC,ASSOC.EVENT,COUNT,CHAIN.CASCADE,CASCADE)
0002: COMMON /TEST/ AUDITFV
0003: FILEOPENED = FILEINFO(AUDITFV,0)
0004: IF FILEOPENED NE 1 THEN
0005: OPEN '','TRIG_AUDIT' TO AUDITFV ELSE CRT 'COULD NOT OPEN TRIG_AUDIT';
0006: RETURN
0007: END
0008: ITEM = 'Deleted ':OLDID:' in ':TABLE
0009: ID = TIMEDATE(): '*' :OLDID
0010: LOOP
0011: RECORDLOCKU AUDITFV,ID
0012: ON ERROR RETURN
0013: LOCKED
0014: NAP 250
0015: CONTINUE
0016: END
0017: EXIT
0018: REPEAT
0019: WRITE ITEM ON AUDITFV,ID
0020: RETURN
After FILETRIG is compiled it must be globally catalogued:
>CATALOG BP FILETRIG
"*ce*FILETRIG" cataloged.
Now create the trigger on the file in question:
>CREATE TRIGGER TEST.TRIGGER AFTER DELETE ON TEST.FILE FOR EACH ROW
CALLING "*ce*FILETRIG";
Adding trigger "TEST.TRIGGER"
Now create the file TRIG_AUDIT:
>CREATE.FILE TRIG_AUDIT 30
Creating file "TRIG_AUDIT" as Type 30.
Creating file "D_TRIG_AUDIT" as Type 3, Modulo 1, Separation 2.
Added "@ID", the default record for RetrieVe, to "D_TRIG_AUDIT".
Now, if a record is deleted from the file called TEST.FILE,
TEST.TRIGGER will fire and update the TRIG_AUDIT file. As an example,
record id 103 was deleted from TEST.FILE. Listing TRIG_AUDIT gives:
$<50>LIST TRIG_AUDIT 16:32:23 15-09-06 PAGE1
TRIG_AUDIT
16:32:16 1
5 SEP 2006
*103
1 records listed.
Doug Miller [EMAIL PROTECTED]
Manager of Technical Services
Strategy 7Dallas TX
---
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Universe and triggers
Hi Marcos, Universe seems hellbent on invoking the entire SQL system in order to put a simple trigger on a file. UniData is MUCH more forgiving in that regard. With Universe, I have had a certain amount of success making what I call 'indexed subroutines' where by an index on a file calls a virtual Dictionary subroutine which does, in fact, operate as a rudimentary trigger. You CANNOT affect data going to the actual file record update as you might with an actual trigger, but you can write information to a tracking database and then I have a background phantom in operation which manages email distribution from the triggered events and what not. I *could* even write back to the originating file record that initiated the trigger -- I've closed off the mortal embrace between phantom and virtual subroutine -- but that will always have a lag time between the actual record update event and the final conclusion of the trigger, so I would not consider it a particularly clean solution for true database triggering. It does work well as a tracking and reporting system, though. Trey -- Original message -- From: Marcos Fogaga <[EMAIL PROTECTED]> > Hi, > > > > It is possible create a trigger (subroutine) for a pick file (non SQL > table)? > > On D3 we have CALLX, is there a feature like this in Universe? > > > > Thanks in advance. > > > > Best Regards, > > > > Marcos Fogaca > > Integral Sistemas > > Sao Paulo / Brazil > --- > u2-users mailing list > u2-users@listserver.u2ug.org > To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] Universe and triggers
Hi, It is possible create a trigger (subroutine) for a pick file (non SQL table)? On D3 we have CALLX, is there a feature like this in Universe? Thanks in advance. Best Regards, Marcos Fogaca Integral Sistemas Sao Paulo / Brazil --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
