File Level Triggers

[jyoung]

How do you create a File level Trigger?

I have a case that requires complex validation of multiple Fields in a File. I thought this may be a good use for a Trigger instead of putting logic in a Server Module.

I can't find where that describes how you put a Trigger on a File NOT on a Field.

This doc states that its possible.
http://docs.lansa.com/14/en/lansa015/co ... 2_0010.htm

Rules and triggers can be defined in both field and table definitions.

But when you follow the doc trail down, I don't see how you apply the Trigger to the File itself. It does link to a tutorial exercise
http://docs.lansa.com/14/en/lansa095/co ... 1_0355.htm
that has you review File level triggers, but nothing is mentioned regarding one.

EDIT
To clarify I am looking to create a LANSA Trigger not a Database Trigger. I am trying

EDIT
Am I reading this correctly? You SHOULD NOT DO ANY I/O on File IN THE TRIGGER?
http://docs.lansa.com/14/en/lansa015/co ... c10135.htm

Do not do any I/O to the table with which the trigger is linked. Attempting such I/O directly, or indirectly, may cause a recursive call to the table Object Access Module. Do not attempt to use *DBOPTIMIZE to circumvent this rule. Such attempts will cause the table cursor of the active Object Access Module to become lost or corrupted.

If you can't read the File while executing a trigger, then this whole thing is pointless and my only option is to put the logic in the server module.

[caseywhite]

The difference between a file level trigger and a field is just what object you open up. Instead of opening up the field in the repository, just open up the file. Then in the file's rules and triggers tab, pick a field (I usually pick the highest level key field) and add your trigger. This is now a file level trigger. In the trigger function you can get access to the data being added/updated using GET_ENTRY using entry number 1 for the current values being inserted/updated. In the case of an update you can GET_ENTRY number 2 to get the values in the file since the update hasn't actually occured yet so that you can compare values. In the case of a before insert/update trigger operation you can change values of the fields and then do an UPD_ENTRY proving you already did a GET_ENTRY will will update the values of the record being added/updated. If you set the trigger return code to VE that will tell the program a validation error has occurred. What they mean by not reading the file is don't read the same file you are updating/inserting. But you can read other files provided you have set "Relax restrictions on trigger functionality" to Y in Work with Administration Tasks -> Review System Settings -> Execution and Security.

[jyoung]

I need to read the File itself, i.e. make sure this value is not in any of these columns for this year. So that indicates to me that I should not use a Trigger.

I find the documentation/workflow confusing, because, as you mentioned, you add the Trigger to a FIELD on the File.

There is no way to simply attach a Trigger to the File WITHOUT selecting a Field.

IMHO, If you could attach a Trigger to File (without first selecting a Field) the workflow would be less confusing.

Thanks,
Joe

[caseywhite]

Agreed that trigger is not be the best answer in this case if you need to read other records in the file.

[PierreLefrancois]

A possible solution would be to define a file level trigger fired only on the « Before Insert » and « Before Update » events and use a free format SELECT_SQL to perform the validation.

This might not be so pretty but it works and it allows you to define RDMLX validation routine that reads back the file in the trigger.

Note : I may be wrong but I believe that the following warning was written before the free format SELECT_SQL was made available. The SELECT_SQL does not invoke the OAM.

Do not do any I/O to the table with which the trigger is linked. Attempting such I/O directly, or indirectly, may cause a recursive call to the table Object Access Module. Do not attempt to use *DBOPTIMIZE to circumvent this rule. Such attempts will cause the table cursor of the active Object Access Module to become lost or corrupted.

For example, your trigger might look like this:

Code: Select all

Function Options(*DIRECT) Rcv_List(#TRIG_LIST) Trigger(*FILE YourFile)

Def_List Name(#TRIG_LIST) Type(*WORKING) Entrys(2)

* Assume all will be good
#TRIG_RETC := OK

Case Of_Field(#TRIG_OPER)

When Value_Is('= BEFINS' '= BEFUPD')

* Obtain record about to be inserted or updated.
Get_Entry Number(1) From_List(#TRIG_LIST)

* Build SQL command to perform your specific validation (Ex: make sure this value is not in any of these columns for this year).
* Use field content from record about to be inserted or updated (#FieldA below)
#STD_STRNG := 'SELECT <Fields> FROM <YourFile> WHERE <FieldA> =' + *QUOTE + #FieldA + *quote

Select_Sql Fields(#FieldA) Using(#STD_STRNG)
* Here record is found - If this is a validation error, set return code and message.
#TRIG_RETC := VE
Message Msgtxt('This is my validation error message.')
Endselect

Otherwise
Abort Msgtxt('File YourFile trigger function invalidly invoked/ used.')
Endcase

Return

Pierre

[atostaine]

I need to read the File itself, i.e. make sure this value is not in any of these columns for this year. So that indicates to me that I should not use a Trigger.
Thanks,
Joe

You could build a logical on that field that has to be unique and do a check_for in the trigger to that logical. If you make the it keyed unique you wouldn't have to check it at all.

[PierreLefrancois]

Art,

The CHECK_FOR will call the OAM right ? I don't think you can perform an I/O operation in the trigger that will access the underlying OAM.

Pierre

[atostaine]

Art,

The CHECK_FOR will call the OAM right ? I don't think you can perform an I/O operation in the trigger that will access the underlying OAM.

Pierre

Yeah I think that's right. I might give it a try But the previous poster was correct SQL should be OK. I'd be worried about performance doing SQL statements on every add.

[KevinW]

Why not make the database do the work?

Forget about LANSA file level triggers and make the logical view that is keyed by the columns unique. You do have such a logical view, don't you.

Your next job of course is to make the duplicate key error comprehensible to the user.

Edited to say oops this has already been suggested. I support that suggestion.

[jyoung]

So, I ended up putting this logic in the SM and it worked out just fine.

I could use the free format sql and do everything suggested, but in this case the trade off was not worth it.