Changing the tag on the database based on the date provided after a certain period of time

[Eunsu Kim]

Hello, 

I'm new to Confluence but I was wondering about trying to imitate this kind of function and automate it based on the provided rules. 

I am trying to change the status tag on the database, in this case starting from the status "GOOD" to "BAD" after 30 days of the tag from a provided date. Additionally, if this is possible, I would like to notify a person selected from the database about the status change.  The purpose of this function is to try and organize items and notify that they have expired after 30 days and need to be thrown away and replaced as indicated by the status tag.  To clarify, the status tag, date, and user are in a separate table column. 

I've noticed that there's a similar system in using pages, but I was wondering if there's a way for me to implement it in the database specifically and automate it. 

Thank you for your help. 

[Anwesha Pan]

Hi @Eunsu Kim 👋🏻

You might have already found answer to this but just wanted to share my thoughts here.

Since database status columns typically don't update themselves automatically based on time, you will need an external process or a scheduled internal job to run a query periodically (usually once a day).

You can automate this using a combination of a scheduled SQL task and a notification service.

For MySQL/PostgreSQL you can use something like this:

UPDATE your_table
SET status = 'BAD'
WHERE status = 'GOOD'
AND provided_date <= CURDATE() - INTERVAL 30 DAY;

For SQL server:

UPDATE your_table
SET status = 'BAD'
WHERE status = 'GOOD'
AND provided_date <= DATEADD(day, -30, GETDATE());

To notify the specific user associated with the record, you can integrate your database with a workflow or script that runs immediately after the update. This can be done by Database Trigger Job.

Hope this answers your question.🙂

Thanks,
Anwesha