TRIGGERS | Rocket Forum

Hi - as ive mentioned in previous threads, I just switched from unidata to universe. A coworker mentioned a trigger but I do not see a version of my unidata commands:

CREATE-TRIGGER, LIST-TRIGGER, DELETE-TRIGGER

I do see the LIST.SICA command, which lists universe triggers on SQL files....do we not have triggers on regular PICK files in universe?

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Hi Kathleen!

In Universe, it's actually two words - CREATE TRIGGER.

Try HELP SQL CREATE for more info.

Brian

Hi - as ive mentioned in previous threads, I just switched from unidata to universe. A coworker mentioned a trigger but I do not see a version of my unidata commands:

CREATE-TRIGGER, LIST-TRIGGER, DELETE-TRIGGER

I do see the LIST.SICA command, which lists universe triggers on SQL files....do we not have triggers on regular PICK files in universe?

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Hi

Yes on UV, triggers are documented/part of SQL engine.

look at HELP SQL CREATE TRIGGER / DROP TRIGGER.

You can use it against standard non SQL uv datafile.

be carefull, if you change the subr after create trigger you must drop/create.

Regards

------------------------------
Manu Fernandes
------------------------------

SO... cant find the catalog table - im used to CTLGTB.... if there a table of catalogued programs in universe?

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

The only example in the manuals i see is specific to an sql file. Trying this at tcl gives me an 'SQL+' prompt. Can someone show me the syntax to put a trigger on a non sql file please?

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

hello kathleen,

yes, triggers are member of universe 'SQL engine'... you miss the semi-colon at then end of the phrase ...

CREATE TRIGGER TRIGGER1 AFTER INSERT OR UPDATE OR DELETE ON MYFILE FOR EACH ROW CALLING '*MYTRIGGER' ;

i'm used to create a VOC PARAGRAPH to set/update a trigger on a file

first, basic/catalog globally

BASIC BP TRIG.LOG.b
CATALOGI BP *TRIG.LOG TRIG.LOG.b FORCE

next, define the paragraph

ED VOC SET.TRIG.LOG
PA 
DROP TRIGGER <<I2,filename>> TRIGLOG ;
CREATE TRIGGER TRIGLOG AFTER INSERT OR UPDATE OR DELETE ON <<I2,filename>> FOR EACH ROW CALLING '*TRIG.LOG';

next, use on file

SET.TRIG.LOG MYFILE

------------------------------
Manu Fernandes
------------------------------

hello kathleen,

yes, triggers are member of universe 'SQL engine'... you miss the semi-colon at then end of the phrase ...

CREATE TRIGGER TRIGGER1 AFTER INSERT OR UPDATE OR DELETE ON MYFILE FOR EACH ROW CALLING '*MYTRIGGER' ;

i'm used to create a VOC PARAGRAPH to set/update a trigger on a file

first, basic/catalog globally

BASIC BP TRIG.LOG.b
CATALOGI BP *TRIG.LOG TRIG.LOG.b FORCE

next, define the paragraph

ED VOC SET.TRIG.LOG
PA 
DROP TRIGGER <<I2,filename>> TRIGLOG ;
CREATE TRIGGER TRIGLOG AFTER INSERT OR UPDATE OR DELETE ON <<I2,filename>> FOR EACH ROW CALLING '*TRIG.LOG';

next, use on file

SET.TRIG.LOG MYFILE

------------------------------
Manu Fernandes
------------------------------

oops do not forget semin-colon at the end of DROP TRIGGER filename triggername ;

------------------------------
Manu Fernandes
------------------------------

hello kathleen,

yes, triggers are member of universe 'SQL engine'... you miss the semi-colon at then end of the phrase ...

CREATE TRIGGER TRIGGER1 AFTER INSERT OR UPDATE OR DELETE ON MYFILE FOR EACH ROW CALLING '*MYTRIGGER' ;

i'm used to create a VOC PARAGRAPH to set/update a trigger on a file

first, basic/catalog globally

BASIC BP TRIG.LOG.b
CATALOGI BP *TRIG.LOG TRIG.LOG.b FORCE

next, define the paragraph

ED VOC SET.TRIG.LOG
PA 
DROP TRIGGER <<I2,filename>> TRIGLOG ;
CREATE TRIGGER TRIGLOG AFTER INSERT OR UPDATE OR DELETE ON <<I2,filename>> FOR EACH ROW CALLING '*TRIG.LOG';

next, use on file

SET.TRIG.LOG MYFILE

------------------------------
Manu Fernandes
------------------------------

some notes :

1/ Trigger must be dropped and created again.
Problem
If trigger program is changed after creation, the trigger must be dropped and created again. This presents problems, for example, when a new release of application software is deployed. Typically, program libraries will be reloaded and recataloged. If trigger programs have changed, all files will need to be checked, triggers dropped and recreated.
Cause
The pointer to the trigger program object code resides in the SICA of the file header.
Solution
Workaround: Make the trigger program a simple call to an external subroutine. The external subroutine may now be modified without affecting the trigger

2/ triggers execution are part of an implicit 'TRANSACTION' processing
2.1/ the TRANSACTION BEGin is started by the 'WRITE' verb of the parent execution. Then all BEGIN TRANSACTION restriction occurs inside triggers.
2.2 / you must get the lock (READU / RECORDLOCKU) before update another file
2.3/ you can't execute verb which require a new level.
2.4/ be carefull if the parent already have the lock of a record, the on-file record can be wrong value
2.5/ if the parent start a explicit BEGIN TRANSACTION, all locks set by the triggers are held by the session (as part of the parent TRANSACTION) until TRANSACTION COMMIT/ROLLBACK ...
2.6/ if the parent use explicit BEGIN TRANSACTION and perform ROLLBACK triggers update are ROLLBACK'ed too, the only way to have the trace of the triggers call is via OPENSEQ/WRITESEQ which are not part of TRANSACTION processing.

3/ check trigger infinite loop
sometime, you can create infinite loop, if triggers update file where triggers are fired.
you can use the system(9001) to check if the trigger is in the parent call stack.

4/ triggers must be created on 'closed file'
if session-A have the file opened on runtime filevariable/common and session-B CREATE TRIGGER on the file, the session-A does not known the trigger, session-A must reopen the file. because, as for indices, triggers info/p-code are in the header of the file - loaded on the filevariable.

I hope this help

------------------------------
Manu Fernandes
------------------------------

Hi - as ive mentioned in previous threads, I just switched from unidata to universe. A coworker mentioned a trigger but I do not see a version of my unidata commands:

CREATE-TRIGGER, LIST-TRIGGER, DELETE-TRIGGER

I do see the LIST.SICA command, which lists universe triggers on SQL files....do we not have triggers on regular PICK files in universe?

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Kathleen,

We use them very sparingly but I've have good success using the methods defined on this post.
https://www.intl-spectrum.com/Article/r310/Auditing_Database_Changes_with_UniVerse_Indexing_Subroutines

Harry

------------------------------
Harry Hambrick
Senior Software Developer
Rooms To Go Inc
Seffner FL US
------------------------------

Kathleen,

One difference that has not been mentioned in relation to triggers with UniVerse and UniData. When you use a trigger it will be treated as though it was part of a transaction. On UniData you will never have noticed this as UniData does not enforce isolation levels of a transaction, you have to code to an isolation level. In UniVerse isolation levels can be enforced and by default the UniVerse tuneable ISOMODE is set to 1, which enforces protection against UnCommitted Reads.

In short for someone coming from UniData to UniVerse and wanting to use Triggers (or transactions) then every record that you are going to update should have an exclusive lock set on it and if you are going to just read a record in a Trigger (or transaction) you should read the record with a shared lock (see READL).

Failure to follow these rules in UniVerse you may see an error stating that the locks are not set to the required level to perform an operation. As I said these rules are not enforced in UniData (but should be).

Thanks,

------------------------------
Jonathan Smith
UniData ATS
Rocket Support
------------------------------

Hi - as ive mentioned in previous threads, I just switched from unidata to universe. A coworker mentioned a trigger but I do not see a version of my unidata commands:

CREATE-TRIGGER, LIST-TRIGGER, DELETE-TRIGGER

I do see the LIST.SICA command, which lists universe triggers on SQL files....do we not have triggers on regular PICK files in universe?

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

I have played with UV triggers in the pastand have ended up avoiding them.
At first, iirc, they were slow. I don't think that is a problem now, but the RAID debugger doesn't play nicely with them the last time I checked.
Does anyone know if that is still true? (I've always found the debugger the buggiest part of most programming languages.)

When Prime Information first introduced indexing decades ago, I was a beta site. I recognized that, with a little work, one could effect a trigger by indexing NO.NULLS, letting your subroutine do whatever you need, then always returning null. The tricky part was knowing if your subroutine were being called for an add, delete, or change. (An index gets called once for an add, 2x for a change (evaluate the old version, then the new & see if it has changed), and 1x for a delete of the record being indexed.) I suggested inventing an @-variable flag for that. Finally, at UV 11.x we have @IDX.IOTYPE so you can do that more easily.

I was going to write up how to do that, but earlier in this thread Harry Hambrick already pointed to a Spectrum by Clif Oliver: www.intl-spectrum.com/Article/r310/Auditing_Database_Changes_with_UniVerse_Indexing_Subroutines. Kudos, Clif.

------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
San Ignacio BZ
------------------------------

SO... cant find the catalog table - im used to CTLGTB.... if there a table of catalogued programs in universe?

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Do you mean the GLOBAL.CATDIR file ?
This lives in the uv account and there may be called just catdir

------------------------------
Will Johnson
Systems Analyst
Rocket Forum Shared Account
------------------------------

Kathleen,

One difference that has not been mentioned in relation to triggers with UniVerse and UniData. When you use a trigger it will be treated as though it was part of a transaction. On UniData you will never have noticed this as UniData does not enforce isolation levels of a transaction, you have to code to an isolation level. In UniVerse isolation levels can be enforced and by default the UniVerse tuneable ISOMODE is set to 1, which enforces protection against UnCommitted Reads.

In short for someone coming from UniData to UniVerse and wanting to use Triggers (or transactions) then every record that you are going to update should have an exclusive lock set on it and if you are going to just read a record in a Trigger (or transaction) you should read the record with a shared lock (see READL).

Failure to follow these rules in UniVerse you may see an error stating that the locks are not set to the required level to perform an operation. As I said these rules are not enforced in UniData (but should be).

Thanks,

------------------------------
Jonathan Smith
UniData ATS
Rocket Support
------------------------------

thank you for that warning Jonathon, and to Manu, for pointing out distinctions between the trigger functions on universe and unidata - that is very helpful in avoiding the 'oh sh*t' moment when something acts differently than you expect :)

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Do you mean the GLOBAL.CATDIR file ?
This lives in the uv account and there may be called just catdir

------------------------------
Will Johnson
Systems Analyst
Rocket Forum Shared Account
------------------------------

Hi WIll - thanks for that bit of info. We have two catalog files on unidata CTLG for local catalogs, and ctlgtb for global catalogs.

I have been reading about the &MAP& file, and now I see the global.cat you've suggested. Are these two distinct? Or part of the same setup?

Thanks for your help!

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Kathleen,

One difference that has not been mentioned in relation to triggers with UniVerse and UniData. When you use a trigger it will be treated as though it was part of a transaction. On UniData you will never have noticed this as UniData does not enforce isolation levels of a transaction, you have to code to an isolation level. In UniVerse isolation levels can be enforced and by default the UniVerse tuneable ISOMODE is set to 1, which enforces protection against UnCommitted Reads.

In short for someone coming from UniData to UniVerse and wanting to use Triggers (or transactions) then every record that you are going to update should have an exclusive lock set on it and if you are going to just read a record in a Trigger (or transaction) you should read the record with a shared lock (see READL).

Failure to follow these rules in UniVerse you may see an error stating that the locks are not set to the required level to perform an operation. As I said these rules are not enforced in UniData (but should be).

Thanks,

------------------------------
Jonathan Smith
UniData ATS
Rocket Support
------------------------------

Thanks Jonathon, I will read up on the readl command.

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Kathleen,

We use them very sparingly but I've have good success using the methods defined on this post.
https://www.intl-spectrum.com/Article/r310/Auditing_Database_Changes_with_UniVerse_Indexing_Subroutines

Harry

------------------------------
Harry Hambrick
Senior Software Developer
Rooms To Go Inc
Seffner FL US
------------------------------

this is helpful harry, I remember loving universe indexing 20 years back. thank you.

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Hi WIll - thanks for that bit of info. We have two catalog files on unidata CTLG for local catalogs, and ctlgtb for global catalogs.

I have been reading about the &MAP& file, and now I see the global.cat you've suggested. Are these two distinct? Or part of the same setup?

Thanks for your help!

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

IIRC the &MAP& file gives you certain statistics about the catalog entry
But it is not the actual catalog *code*
The code information is stored in the catdir (GLOBAL.CATDIR) file
So I suppose it matters what exactly you are looking for

Generally when I am interested in this, I'm looking for answers like Is this subroutine globally catalogued? If so, where is the actual underlying code located (for editing)? That's what I'm looking for.

------------------------------
Will Johnson
Systems Analyst
Rocket Forum Shared Account
------------------------------

I have played with UV triggers in the pastand have ended up avoiding them.
At first, iirc, they were slow. I don't think that is a problem now, but the RAID debugger doesn't play nicely with them the last time I checked.
Does anyone know if that is still true? (I've always found the debugger the buggiest part of most programming languages.)

When Prime Information first introduced indexing decades ago, I was a beta site. I recognized that, with a little work, one could effect a trigger by indexing NO.NULLS, letting your subroutine do whatever you need, then always returning null. The tricky part was knowing if your subroutine were being called for an add, delete, or change. (An index gets called once for an add, 2x for a change (evaluate the old version, then the new & see if it has changed), and 1x for a delete of the record being indexed.) I suggested inventing an @-variable flag for that. Finally, at UV 11.x we have @IDX.IOTYPE so you can do that more easily.

I was going to write up how to do that, but earlier in this thread Harry Hambrick already pointed to a Spectrum by Clif Oliver: www.intl-spectrum.com/Article/r310/Auditing_Database_Changes_with_UniVerse_Indexing_Subroutines. Kudos, Clif.

------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
San Ignacio BZ
------------------------------

thank you Chuck - I'm not hearing about most people here using actual triggers. I used them often in unidata but reflectively they were being used to log and trap issues so maybe the index resulting in a null to a file that has the no.nulls set is the equivalent here.

Thank you all for that info!

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Kathleen,

Here is our TOI document on Indexed Subroutines from when they were first introduced into UniVerse.

Thanks,
Jonathan

------------------------------
Jonathan Smith
UniData ATS
Rocket Support
------------------------------

Jonathon,

While we're on the subject & while I have your ear, thank the team for @IDX.IOTYPE for me.

Warm regards,
Chuck S

------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
San Ignacio BZ
------------------------------

Hi

Yes on UV, triggers are documented/part of SQL engine.

look at HELP SQL CREATE TRIGGER / DROP TRIGGER.

You can use it against standard non SQL uv datafile.

be carefull, if you change the subr after create trigger you must drop/create.

Regards

------------------------------
Manu Fernandes
------------------------------

You can actually use a "master" globally cataloged subroutine that calls other subroutines based on how you activate the trigger.
We have about a dozen or so triggers on all sorts of files. Each file that has a trigger has it's own trigger subroutine. So for a customer file you might name this trigger "SR.CUSTOMER.FTRIG" (cataloged locally) which is called by "SR.MASTER.FTRIG" (cataloged globally).

We've found that you can actually re-compile these locally cataloged subroutines without having to drop/activate the trigger UNLESS your system is super busy AND the trigger is on a file that is being hit very hard, in which case you might end up having to bounce UniVerse. This may also be a feature of our old UV version too. I'm sure you'll find out soon enough - we did. Nowadays we arrange an outage to deploy trigger changes but thankfully this is not very often.

@Kathleen Hambrick: Ken Ford, my former colleague here wrote a really nice utility to control the trigger enable/disable each trigger for each of the insert/modify/delete and before/after conditions for all the files that we run triggers on. It has a nice -DROPALL option but sadly no -STARTALL. Ken has posted this code publicly in the past so it has to be out there if you search for his name and/or the "MASTER.FTRIG.CONTROL" program name.

Cheers,
Peter

------------------------------
Peter Cheney
Developer and Systems Superstar
Firstmac
Brisbane Qld Australia
------------------------------

Hi - as ive mentioned in previous threads, I just switched from unidata to universe. A coworker mentioned a trigger but I do not see a version of my unidata commands:

CREATE-TRIGGER, LIST-TRIGGER, DELETE-TRIGGER

I do see the LIST.SICA command, which lists universe triggers on SQL files....do we not have triggers on regular PICK files in universe?

------------------------------
Kathleen Hambrick
Programmer at Colwell
------------------------------

Hi @Kathleen Hambrick,
If you haven't already found it, LIST.SICA filename will tell you some information

LIST.SICA CUSTOMER 10:29:18am  25 May 2022   Page    1
==========================================
Sica Region for Data File "CUSTOMER"
  Revision:        4
  Trigger "CUSTOMER.DELETE.FTRIG" is enabled, creator is "peter".
          calls "*FMC*SR.MASTER.FTRIG" for          Row After Delete
  Trigger "CUSTOMER.UPDATE.FTRIG" is enabled, creator is "peter".
          calls "*FMC*SR.MASTER.FTRIG" for          Row After Update
  Trigger "CUSTOMER.INSERT.FTRIG" is enabled, creator is "peter".
          calls "*FMC*SR.MASTER.FTRIG" for          Row After Insert
>

------------------------------
Peter Cheney
Developer and Systems Superstar
Firstmac
Brisbane Qld Australia
------------------------------

You can actually use a "master" globally cataloged subroutine that calls other subroutines based on how you activate the trigger.
We have about a dozen or so triggers on all sorts of files. Each file that has a trigger has it's own trigger subroutine. So for a customer file you might name this trigger "SR.CUSTOMER.FTRIG" (cataloged locally) which is called by "SR.MASTER.FTRIG" (cataloged globally).

We've found that you can actually re-compile these locally cataloged subroutines without having to drop/activate the trigger UNLESS your system is super busy AND the trigger is on a file that is being hit very hard, in which case you might end up having to bounce UniVerse. This may also be a feature of our old UV version too. I'm sure you'll find out soon enough - we did. Nowadays we arrange an outage to deploy trigger changes but thankfully this is not very often.

@Kathleen Hambrick: Ken Ford, my former colleague here wrote a really nice utility to control the trigger enable/disable each trigger for each of the insert/modify/delete and before/after conditions for all the files that we run triggers on. It has a nice -DROPALL option but sadly no -STARTALL. Ken has posted this code publicly in the past so it has to be out there if you search for his name and/or the "MASTER.FTRIG.CONTROL" program name.

Cheers,
Peter

------------------------------
Peter Cheney
Developer and Systems Superstar
Firstmac
Brisbane Qld Australia
------------------------------

Peter,
What has been your experience using DEBUG or RAID on programs that write to files/tables having triggers?
It was a problem a while back, but I haven't tried it at UV11.
tia,
cds

------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
San Ignacio BZ
------------------------------

Hi @Chuck Stevenson,

Debugging code that writes to files with triggers is a pain. We're on UV10.3.4 but have played with UV11.2 and it's still the same.
Need to remember to always set a break point just beyond the write line. Sometimes I forget though haha.

Cheers,
Peter

------------------------------
Peter Cheney
Developer and Systems Superstar
Firstmac
Brisbane Qld Australia
------------------------------

Anyone,

re. "Debugging code that writes to files with triggers is a pain. We're on UV10.3.4 but have played with UV11.2 and it's still the same."

With the advent of @IDX.IOTYPE, is there any advantage to using formal UV Triggers instead of an index (usually with NO.NULLS) subroutine instead, as discussed earlier in this thread?

------------------------------
Chuck Stevenson
DBA / SW Developer
Pomeroy
US
------------------------------

Chuck,

I am by no means a trigger or an indexing expert, so while I have found this discussion extremely educational, some if the items went a little beyond by skill level. I apologize ahead of time if the answer to my question was already covered and I either missed it or just plain didn’t understand.

From what I can gather, a trigger-based program gets ‘fired’ when a file gets updated (or whatever condition is defined in the trigger), while an index-based program would get run when the index itself gets updated. What then happens when/if I have to clear the indices on a file and rebuild them? Won’t that rerun the program if it’s index-based and double-up on whatever that program was supposed to do?

Brian

Sign up

Please log in or register:

Welcome to the Rocket Forum!

Please log in or register:

Scanning file for viruses.

This file cannot be downloaded