Transaction trigger delay

I have a standard group here where I am trying to trigger using a SQL group tag to perform an update on my database.
I am using the hour meter and trying to log runtimes per shift.
So I am updating the row and at shift change, I am adding a NULL row using SQL server.
This is so it will update the next row and keep my old shift information in the same table.
However, I did get this to work numerous way… Although none of these settings seem to work 100% of the time.
I have my shift change as a button with the following event script:
system.db.runUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” )
system.db.runUpdateQuery(“INSERT INTO photo (t_stamp) VALUES (CURRENT_TIMESTAMP)” )
system.tag.writeToTag(‘ShiftReset’, 1)

ShiftReset is a bool SQL tag with INt2 group data type.
my execution scheduling is set to timer ‘1 second’
My items are set to hour meter, retentive, and reset on ShiftReset = 1

my trigger is set to only execute once while trigger is active
reset trigger after exectution
trigger conditions are is >0

If I change these settings to the handshake settings, I get the same results.

The problem seems to be a timing issue between my execution scheduling and how fast I can change my trigger and reset it.
Dropping my execution time down to 1 milisecond seemed to help my resets, but crippled my accumulation.
It seems like there is something strange here, this seems very difficult to set up.
It also seems like I want to be able to have conditions for when to take a certain table action.
For instance, I’d like to insert a new row or block on my reset, but on my standard execution of the group where my meters are running I want it to only update the last row or block in the table.

I think the problem with the timing was that I was using multiple transaction groups with the same SQL tag as a trigger, so one of my groups was using the trigger and resetting it before the other groups could see it.

I would like to be able to decide conditions where I can insert a new block or row AND update a row or block depending on a condition or triggering method inside of the group.

Hi,

Yeah, having a single tag triggering both groups would result in what you described.

As for the “update generally, then insert on trigger”, I agree. What you’re trying to do is fairly common, and unfortunately more complicated than it should be. The hour meter reset condition on the item helped a tad, but something like you described would probably help a lot. Until then, the insert could probably be done in a on-trigger action item (“if({tag}>0,executeUpdateQuery(…),0)”) to keep everything in the group, but again, I recognize that this is tougher than it needs to be.

Regards,

ok, this would be nice.
I made an expression item and put this script in:

(“if({ShiftReset}=1, executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),0)”)

It keeps getting evaluation errors or config errors.
Does this look ok?
I’ve got the group set to update rows… so this script should handle the logging by creating a new row.

I dropped the outside quotes and paranthesis.
It works !
Now if I can figure out how to do this exactly the same way with block groups, I’m all set.

I think I’m going to need to loop the updatequery as many times as I have items in my blocks

if({ShiftReset}=1, executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),0)

can I use a loop somewhere in here? I was trying something like…

if({groupEND}=1,
for n in range(0,100000000):
executeUpdateQuery(“INSERT INTO block (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),0)

You cannot do any loops in expression items within groups. It would be easier and better to use a gateway timer or tag change script to do that.

Well I think the whole idea why this will work is that the group trigger expression executes because the data transfer which is what I need.

If I use a tag change script, Would I still have the proper timing to pull this off?
How about something inside the group item expression like…

if({ShiftReset}=1, executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),executeUpdateQuery(“INSERT INTO motorrun (t_stamp) VALUES (CURRENT_TIMESTAMP)” ),0)

How would I structure this?