using the record object in a trigger for relationships

[shiraz]

Steve Hannah,

Thanks for your response to my last post. I look forward to version 0.6!

A question:

I've written a save (both update and insert) trigger for a particular field. In that trigger I used a delegate class. In this class I retrieve information about the current record being updated/inserted, so I use the record object as you've documented.

It works fine when I'm inserting/updating the record originating the procedure from its own table.

But when I'm inserting/updating the record after having clicked on the "+ Add New (fieldname) Record" button in the relationship tab of another table, it does not work. Apparently the record object is populated with the original table's current record. Is there another object, or another method I can use to retrieve whatever is the current record being updated/inserted, regardless of how I got there?

Many thanks,

Shiraz

[shannah]

Hi Shiraz,

The situation you describe should work, if I understand you correctly. Suppose you have 2 tables: Products and Accessories, where each product can have a number of accessories. So the Products table has a relationship named 'accessories' that contains records from the 'Accessories' table.

Suppose you want a trigger to be called whenever a record is inserted into the Accessories table. Then you add a method to the "Accessories" delegate class (in tables/Accessories/Accessories.php) as follows:

Code: Select all





This trigger would be called whenever an Accessories record is inserted into the Accessories table - including the case where it is added as a related record. When adding records using the "Add New Related Records" form it is possible for triggers to be called on multiple tables - if more than one record is inserted to satisfy the relationship. For example if a record is inserted into a many-to-many relationship, generally this would involve adding a record to a join table also. Therefore the beforeInsert() method would be called in both the delegate class of the join table and the destination table of the relationship. These methods would be passed records of their repsective tables as parameters.

Let me know if you are experiencing different behavior and possibly post some table structures so that I can reproduce the problem on my end.

Best regards

Steve

[shiraz]

Steve,

Thank you kindly for your response. I look forward to showing you this database once it's developed, if you're interested. It's quite a project!

You understood my question correctly.

I realized what the problem is -- you are right in that $record is populated with the proper values, regardless of whether the method was triggered by the current table or via a relationship.

But there is one important exception -- the primary key does not turn up in the $record array when it is triggered by insertion from a relationship. Try putting a print_r statement into the delegate class, ie.

class tables_Master_Contact_List {
function afterSave (&$record){
print_r($record);

....


You'll notice that primary key is empty when the action originated by adding a new record from a relationship, but not empty when the action originated from the same table.


The reason this is a problem is because I'm trying to update fields within a record after that very record has been updated/inserted, ie. calculated fields. On the SQL query, I need to use the primary key for the update statement, but in the case of a relationship trigger, I have no way of knowing what that primary key is, since it doesn't appear in the record object.

Am I missing something? I suppose I could use a different field for the update statement, but only the primary key in this case is necessarily unique.


Again, thanks for your input!

Shiraz

ps. I live nearby, on the Sunshine Coast.

[shannah]

Hi Shiraz,

Are you using the beforeInsert() or afterInsert() trigger. There are a few subtleties that could cause these sorts of problems (and then it could be a bug.. but we'll find out). If you are using beforeInsert() and the primary key is an auto-increment column, then it won't have a primary key yet. It will acquire the primary key after the insert is complete - and hence, should be available to the afterInsert() trigger (unless there is a bug that I have overlooked).

Another thing that could cause this behavior is if the relationship does not actually include the primary key. When you define the relationship, if you used SQL, you probably had something like:

__sql__ = "SELECT Column1, Column2, ..., ColumnN FROM RelatedTable where ID='$ID'"

or something like that.

If you don't have the primary key include in the list of columns (Column1, ..., ColumnN) then the primary key will not be passed to the trigger, unfortunately... I guess this could be called a bug, but it was the most obvious way to implement it at the time.

Interesting to know that you're a fellow BC guy. Hope the sunshine coast is sunny for you - and I'm definitely interested in taking a look at your project when it's done.

Best regards

Steve

PS.. I haven't had a chance to double check on these trigger scenarios since you brought it up, but I'll take a closer look later today or tomorrow to see if any bugs pop up.

[shannah]

Hi Shiraz,

Just took a look at the code, and there is a bug that is preventing this primary key from being passed.. i'll have to think about the fix for a bit, but I'll have it posted later today or tomorrow.

Best regards

Steve

[shiraz]

Hi again,

Well I figure it's a bug then, because:

a) I'm using the after(insert) trigger. Actually, I'm using afterSave.

and

b) my relationship is defined starting with "__sql__ = "SELECT *" ie. the primary key is being included.


One thing to note is that I have the primary key hidden (via fields.ini for both details and list view), for both the target and join tables. I'm deadline-struck right now, but I'll let you know if I get a chance to check out if unhiding them fixes the problem. In any case though it would be a bug, as far as I can tell.


Shiraz

ps. sunny today anyhow!

[shannah]

Hi Shiraz,

It has nothing to do with the field being hidden.

It is a bug, but it is a bug in the code that handles the firing of the triggers (in Dataface/IO.php). I know where the problem is and will be fixing it asap.

Best regards

Steve

[shiraz]

You rock!

[shannah]

I have filed a bug report for this issue and attached a fix at http://sourceforge.net/tracker/index.php?func=detail&aid=1481474&group_id=153729&atid=788932

Let me know if you still experience problems with it.

Best regards
Steve

[shiraz]

fixed, thank you!