Triggered action after the update

[BrianI]

Hi, am I correct in assuming the triggered action on update occurs before the actual update? If so is there a way to trigger it after?

In the below example a trigger is on the table sl_tbl_attendance. The time_out field will have just been populated. The select query is returning all results from the table sl_tbl_attendance for the current staff member where time_out is null. The issue I think I am having is because the trigger is firing before the record is actually updated it will always return the current record. Is there a way of preventing this?

Thanks

[CoopperBackpack]

Hello @BrianI,

In general, in SQL there are 'Before Triggered Action' and 'After Triggered Action'.

In Caspio Triggers are 'Before Triggered Action'. So, you are correct, the Trigger is fired before the values are inserted, updated, or deleted.

In this post, I described how the #inserted table works when the record is updated:

https://forums.caspio.com/topic/31251-trigger-to-update-a-record-and-insert-new-record-when-the-id-match-in-both-tables/?do=findComment&comment=80165

We can reference the values that were in the table before the update and after the update, and compare them if this is needed.

As for your Trigger example, I am afraid I don`t quite follow the workflow. Could you please describe it in more detail? Also, it is helpful to see the action that should be executed if the condition is met. In most cases, it is possible to utilize Joins, etc. instead of using IF-THEN.

[BrianI]

Hi, I have Staff members who are attending clients. 1 staff member can attend multiple clients at the same time. I am tracking the attendance of clients in the table attendance. Here I have the staff_id, client_id, time_in and time_out. This gives me the client attendance report. However I also need to report on the staff hours. Because staff can attend multiple clients simultaneously I don't believe I can write a query in Caspio to calculate that. Therefore I have setup another table with staff_id, time_in and time_out. I am adding a record to this table the first time a staff member attends a client, that works. What I need to do now is clock them off when they finish with their last client. To do this I need to query to see if there are any clients that do not have a time_out date linked to that staff member. If they all have time_out then I know there are no clients being attended by that staff member and I can set the time_out on the staff record to clock them out.

Hope that makes sense and explains why I am trying to use that Select query. 

[CoopperBackpack]

Thank you for the provided details @BrianI,

So, as far as I understood there are 2 tables: sl_tbl_attendance and one more table populated by a Trigger (in my example the table name is 'Staff_hours').

For example, today there are 3 records for the employee with staff_fk = 1.

Should it result in 1 record for today`s day where the 'time_in' is the minimum date/time and the 'time_out' is the maximum date/time? And so on for each date. As a result, it is possible to calculate the date difference between the 'time_in' and 'time_out' to get the working hours. 

[BrianI]

Hi @CoopperBackpack, thanks for the effort in looking at this! Unfortunately it is a bit more complicated than that because there could be a break between attendances for the same staff member on the same day. For instance staff record 1 could attend client 1 between 9:00 and 12:00, client 2 between 10:00 and 13:00 and then client 3 between 14:00 and 15:00. The correct answer here is that staff 1 attended from 9:00 to 13:00 and then from 14:00 to 15:00. Hence 5 hours. Just taking the min in and max out would give 6 hours.

I have started to take a different approach to this now and I'm running a task at the end of the day to try and calculate the result and populate a separate table. I think I'm getting there slowly