on create trigger does not work if written after create stat

Posted by nehaakg on 02-Aug-2017 05:33

Hi All,

Just wanted to know if this is an expected behaviour of create statement,

when i am writing it after create statement. it does not execute, instead if i am writing it before create statement it did work.

Below is the code which does not work.

/*trigger does not execute*/

define variable x as int init 1.

create table1.

assign table1.field1 = x.

on create of table1

do:

x = 10.

end.

below is the code which works.

/*trigger executes here*/

define variable x as int init 1.

on create of table1

do:

x = 10.

end.

create table1.

assign table1.field1 = x.

Posted by Brian K. Maher on 02-Aug-2017 09:40

You are running into a transaction/record scoping issue.  You create and assign data then the on write trigger is hit in the execution stream so the event is registered then your code continues on and the record is written out (i.e. transaction scope ends).  For performance reasons, we defer writes until needed.

Posted by Laura Stern on 02-Aug-2017 09:16

I agree it is not intuitive, but Brian is correct.  The AVM doesn't really know the ON Trigger exists until it has run across the ON statement in the execution stream.  When we get to it, we don't run the contents of the block, but we set the trigger up so that when the CREATE happens we then know to run it.  This is counter intuitive because it is not like anything else in the language - like internal procedures or methods.  Those do not have to occur above the point where we run them.  But ON triggers do.  

All Replies

Posted by Brian K. Maher on 02-Aug-2017 05:44

It is executed at runtime so the trigger will not be in effect until the ON statement executes.  Same rule applies to a UI trigger.

Posted by nehaakg on 02-Aug-2017 08:24

yes, I agree it executed at run time. but ON statement should get exceuted as soon as AVM finds create staement. isn't it?

then why does it matter if on staement written before or after create statement.

Posted by Brian K. Maher on 02-Aug-2017 08:28

>> but ON statement should get exceuted as soon as AVM finds create staement. isn't it?

The above statement is incorrect.

Posted by Brian K. Maher on 02-Aug-2017 08:28

Just a thought .. maybe you are confused between session level database triggers (i.e. you writing the ON statement in code) versus defining triggers in the database itself.

Posted by nehaakg on 02-Aug-2017 08:40

I have defined the triggers in database itself, so there is no issues withg that.

Above query just for my understanding that why it really matters where i am writing on statement in the code. I know when I am writing on create trigger it will behave like session level trigger so i can override my schema trigger.

My assumption was it should get executed whereever i write as long as it is in same program.

Posted by nehaakg on 02-Aug-2017 08:41

ohh then when exactly on create get called, i thought create behaves like event.

Posted by Brian K. Maher on 02-Aug-2017 08:48

For session level triggers your thinking is incorrect.  Having the ON statement in the source code does nothing until, at runtime, the AVM **executes** the ON statement.  At that point is when the AVM sets up the session trigger to be invoked.
 
What you are trying to do is the equivalent of writing a letter, sealing it in an envelope then putting it in a drawer and wondering why no one has read it.  <smile>
 

Posted by Laura Stern on 02-Aug-2017 09:16

I agree it is not intuitive, but Brian is correct.  The AVM doesn't really know the ON Trigger exists until it has run across the ON statement in the execution stream.  When we get to it, we don't run the contents of the block, but we set the trigger up so that when the CREATE happens we then know to run it.  This is counter intuitive because it is not like anything else in the language - like internal procedures or methods.  Those do not have to occur above the point where we run them.  But ON triggers do.  

Posted by nehaakg on 02-Aug-2017 09:32

Thanks Brian and Laura!

Just to be more clear on this point, if this is the case as Brian and Laura explained then wouldn't it be same for ON write statement as well?

But ON write statement is getting executed even i am writing it after create + assign statements.

define variable x as int init 1.

create table1.

assign table1.field1 = x.

on write of table1

do:

    table1.field2 = 8.

end.

Posted by Laura Stern on 02-Aug-2017 09:39

Yes, it should be the same.   And for me it is!  I put a message statement in the Write trigger.  I am not seeing it unless i move the ON WRITE trigger up to the top.

Posted by Brian K. Maher on 02-Aug-2017 09:40

You are running into a transaction/record scoping issue.  You create and assign data then the on write trigger is hit in the execution stream so the event is registered then your code continues on and the record is written out (i.e. transaction scope ends).  For performance reasons, we defer writes until needed.

Posted by nehaakg on 02-Aug-2017 09:48

Thank you so much Brian and Laura, got your point now :)

This thread is closed