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 PAGE 1 TRIG_AUDIT 16:32:16 1 5 SEP 2006 *103 1 records listed. Doug Miller [EMAIL PROTECTED] Manager of Technical Services Strategy 7 Dallas 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/
