MySQL Trigger help:

[zorzis]

i have that code from a trigger i use in one of my log tables,

Delimiter $$
/* Trigger  for INSERT */
CREATE TRIGGER Algae_Survey_Species_Log_Insert AFTER INSERT ON Algae_Survey_Species FOR EACH ROW  BEGIN
DECLARE N TIMESTAMP;
SET N = now();
INSERT INTO Algae_Survey_Species_Log(FK_Algae_Surveys_Log_ID,Action,Action_Start_Date,SurveySpecies_ID,Survey_ID,Algae,Quantity,Family)
VALUES (XXX the value we must take goes here,'CREATE', N, NEW.SurveySpecies_ID,NEW.Survey_ID,NEW.Algae,NEW.Quantity,NEW.Family);
END;

** that code is updating the Child_Log table but i need to take somehow the Parent_Log PK as FK to Child_Log table after a new insert in Child_Log table.
and i want to implement it with some code like that:

UPDATE child_log LEFT JOIN parent_log ON parent_log.original_pk = child_log.parent_fk AS pl SET(log_pk...) VALUE (pl.id...)

any idea with the mysql query structrure?

 

[cheesegrits]

When we first discussed your requirement for a change logging system, we only talked about the main table. For which a MySQL trigger that just writes out the complete main table row to a new row in your log table every time a main table row is created or updated makes sense.

At that point I wasn't aware that you wanted to also log changes on joined rows, and somehow maintain the relationships such that you could display the change log in Fabrik, and have those joined log rows correctly relate to the main table log row.

I really don't know if this is possible, for the reasons we outlined, and you are trying to solve.

This may work:

CREATE TRIGGER Algae_Survey_Species_Log_Insert
AFTER INSERT ON Algae_Survey_Species
FOR EACH ROW
BEGIN
DECLARE N TIMESTAMP;
SET N = now();
INSERT INTO Algae_Survey_Species_Log
(
    FK_Algae_Surveys_Log_ID,
    Action,
    Action_Start_Date,
    SurveySpecies_ID,
    Survey_ID,
    Algae,
    Quantity,
    Family
)
VALUES
(
    (
        SELECT id FROM Algae_Surveys_Log WHERE Algae_Surveys_Log.Survey_ID = NEW.Survey_ID
    )
    'CREATE',
    N, 
    NEW.SurveySpecies_ID,
    NEW.Survey_ID,
    NEW.Algae,
    NEW.Quantity,
    NEW.Family
);
END;

The 'id' in that inner SELECT query needs to be whatever the PK is for your Algae_Surveys_Log is.

That *should* take the original FK value in the log table, i/e; the one that points to the original main table PK, look that up in the main row log table, using the field you have on that for storing the original main table PK, and give you the actual log row PK.

-- hugh

 

[cheesegrits]

BTW, note how I've reformatted your query. Writing them like that makes it 10x easier to read and understand them. A good habit to get in to when writing down even the most trivial query.

-- hugh

 

[zorzis]

Ok after fixing some mistakes in the code we have that one: ie:

DELIMITER &&
CREATE TRIGGER Algae_Survey_Species_Log_Update
AFTER Update ON Algae_Survey_Species
FOR EACH ROW
BEGIN
DECLARE N TIMESTAMP;
SET N = now();
INSERT INTO Algae_Survey_Species_Log
(
    FK_Algae_Surveys_Log_ID,
    Action,
    Action_Start_Date,
    SurveySpecies_ID,
    Survey_ID,
    Algae,
    Quantity,
    Family
)
VALUES
(
    (
        SELECT Algae_Surveys_Log_ID FROM Algae_Surveys_Log WHERE
 
Algae_Surveys_Log.Survey_ID = NEW.Survey_ID
    ),
    'UPDATE',
    N,
    NEW.SurveySpecies_ID,
    NEW.Survey_ID,
    NEW.Algae,
    NEW.Quantity,
    NEW.Family
);
END;

but when updating a raw in the Fabrik original table then it is creating a new raw with the repeated - child table raws.
But the second time you update the same raw, you get an error like that in fabrik frontend:

        Store row failed:
        Subquery returns more than 1 row SQL=UPDATE `Algae_Survey_Species` SET `Survey_ID`='132',`Family`='1',`Algae`='10',`Quantity`='15' WHERE SurveySpecies_ID='1347'
        Store row failed:
        Subquery returns more than 1 row SQL=UPDATE `Algae_Survey_Species` SET `Survey_ID`='132',`Family`='1',`Algae`='13',`Quantity`='4' WHERE SurveySpecies_ID='1348'
        Store row failed:
        Subquery returns more than 1 row SQL=UPDATE `Algae_Survey_Species` SET `Survey_ID`='132',`Family`='1',`Algae`='32',`Quantity`='18' WHERE SurveySpecies_ID='1349'
        Store row failed:
        Subquery returns more than 1 row SQL=UPDATE `Algae_Survey_Species` SET `Survey_ID`='132',`Family`='1',`Algae`='58',`Quantity`='1' WHERE SurveySpecies_ID='1350'
        Store row failed:
        Subquery returns more than 1 row SQL=UPDATE `Algae_Survey_Species` SET `Survey_ID`='132',`Family`='1',`Algae`='66',`Quantity`='20' WHERE SurveySpecies_ID='1351'
        Store row failed:
        Subquery returns more than 1 row SQL=UPDATE `Algae_Survey_Species` SET `Survey_ID`='132',`Family`='2',`Algae`='78',`Quantity`='24' WHERE SurveySpecies_ID='1352'
        Store row failed:
        Subquery returns more than 1 row SQL=UPDATE `Algae_Survey_Species` SET `Survey_ID`='132',`Family`='5',`Algae`='107',`Quantity`='7' WHERE SurveySpecies_ID='1353'

from phpmyadmin side: we have that situation: Despite we did update each of the two raws we choosed in the original fabrik front-end table to be updated, it gave us only two new group of data from the child log table.

 

[cheesegrits]

Yup. That's what I thought would happen. I just don't see a way to derive the log for the main row, from within a trigger.

That's why last time we spoke on Skype, i said I thought it could only be done at the application layer (i.e. PHP), not the database layer (MySQL), In this case, as a form submission script. Which is what I would have suggested in the first place, had I known this is what you were trying to do.

It still won't be trivial, but I think it's do-able.

Given that what you are essentially trying to do is maintain a full revision history, what do you want to do when (say) someone updates one of the child rows, and nothing else, when submitting a form?

In the model you seem to be going for, this would create a complete new "set" of log rows, for the parent and all child rows.

Likewise, if someone deleted a child row, you want to write out a set of related log rows for the parent and remaining child rows, minus the deleted one.

Basically, what you want is a bordering on a fully featured revision system, for related sets of tables. Including the view layer, i.e. navigating the revision log as Fabrik lists / forms.

Doing this in a form plugin becomes possible, because you "know" all the stuff you need. You know what the parent ID is, and all the child ID's. Whereas in the trigger, all you knew was the child details, with zero knowledge of the parent.

However ... this still may not be a trivial thing to do. I don't think it should be "too hard", and I have a pretty good idea of how to code it, but I've never done this before, so ... "how hard can it be?"

I think the real answer is that we looked at implementing this as a core Fabrik plugin. I can see it being something which started as this - a basic setup that creates the transaction / revision logs, but growing over time to be a way of providing actual revision control over related sets of Fabrik lists, like being able to revert your main tables to previous revisions, etc.

Let me do some more thinking about it, give my brain time to chew on it. If it's workable, I should have a good intuitive grasp of how to do it within a day or two.

If you haven't heard back in a couple of days, bump this thread.

-- hugh

 

[rob]

ive already done some work on this in the audit branch which zoris took a look at previously

 

[zorzis]

yes rob i know but it's a bit problematic that one. maybe some unfinished code generaly it cannot work the way i want to work. the way we disgussed with cheesgrits is the better one i think

 

[zorzis]

friendly bump mate

 

[cheesegrits]

There really isn't a lot I can do to help. You are trying to achieve something Fabrik just doesn't yet do, and now I know you want to log 'sets' of related row changes, MySQL triggers aren't going to help you. As I said, I wouldn't have suggested it if I'd known that's what you wanted to do.

The only approach I think would work would be a form submission plugin, which writes out the resulting 'data set' to the log tables, with the necessary changes to the log set FK's. As explained in my previous post, it has to happen at the "application" level, not the database level, in order to have knowledge of the row relationships.

But it won't be a trivial bit of coding, it'd probably take me at least a couple of days to write and test it, and that would probably be a site specific version, not a "generic" plugin that could be configured to handle any related data setup. To write something generic could take twice as long.

-- hugh

 

[zorzis]

yes i understand now what the problem is. but if you can make it work it would be nicer. it is a thing must be done. and the trigger approach really didn't work as you said. but at least i had to try.

do you think we have to give a live site, look for the issue?

 

[cheesegrits]

As I said in my previous message, we're looking at a couple of days work to get this going as a form plugin. That's not something I can throw in with a support subscription, even at Pro level.

If you want to discuss doing it as a custom coding job, that's about the only way I could do this.

-- hugh

 

[zorzis]

yes maybe just custom.never mind of me. but it has to be completed at a point cause it has to be finished. just tell me to give you the username and pass for that mate.

 

[cheesegrits]

Well, by "custom coding job" I mean "job", as in paid by the hour. It isn't just something I can do as part of regular support.

-- hugh