0

Thank you for attention, take a look

I have a code 

BEGIN
INSERT INTO log_de_money (user_id, wallet, bank)
SELECT user_id, wallet, bank
FROM vrp_user_moneys
WHERE (wallet > 4000000 OR bank > 8000000);END

The results is, when the client has over 4.000.000 at his wallet or bank it insert at log_de_money What i want is to question if its possible to:

Insert at log_de_money only it has a modification from last insert.

Example:

Run the check

Michael has 4.000.000 at his wallet,
Code check if he is into log_de_money, 
if no,  
the code insert him at log_de_money.

few time later... ill run the Check again.. and then..

Michael has 4.000.000 at his wallet, 
Code check if he is into log_de_money, 
yes he is,  
Code check if the money at wallet changed...
IF YES
the code insert him at log_de_money again with the new result,
IF NO
the code ends

its too hard to make it? Someone know how to do ?

Thanks everyone. Best Regards.

Michael RedbuLL
  • 1

1 Answers1

0

Very generally, add a timestamp column which is automatically set on the insert, perhaps using a trigger. Then have an update trigger which sets either another timestamp column and/or a flag (i.e. Boolean) column that indicates when a modification has occurred. Or perhaps even better, just have the update trigger record the log event directly.

There are multiple ways to accomplish this, but I have modeled my timestamp columns after this other SO answer.

Here is just one example of a trigger that directly records into the log. Oh, this syntax is for sqlite because I prepared it before I realized that the tags changed. I'm not sure if this is compatible with MySQL:

CREATE TRIGGER update_log_trigger
AFTER UPDATE ON vrp_user_moneys
WHERE (NEW.wallet > 4000000 OR NEW.bank > 8000000)
BEGIN
   INSERT INTO log_de_money (user_id, wallet, bank)
   VALUES (NEW.user_id, NEW.wallet, NEW.bank);   
END;

Do you understand how this will record the log rows automatically, without having to run a timed query?

If you insist on running a timed query (every 2 seconds), then you need to add other columns to help determine what has changed and what has not changed, just like I already mentioned.

Here is some pseudo-SQL to demonstrate the sequence in the question. Here I will NOT use automatic timestamps, rather will demonstrate with a manual change-flag column. Problems may occur if the timed queries happen to run before changed columns are properly updated. (Using triggers and appropriate transactions could solve this problem.):

INSERT INTO vrp_user_moneys (user_id, wallet, bank, isChanged)
VALUES (@user_id, @newWalletValue, @newBankValue, false);

Later an update happens ...

UPDATE vrp_user_moneys 
SET wallet = @updatedWalletValue, isChanged = true
WHERE user_id = @user_id;

Logging script executes ...

BEGIN TRANSACTION;

INSERT INTO log_de_money (user_id, wallet, bank)
SELECT user_id, wallet, bank
FROM vrp_user_moneys
WHERE (wallet > 4000000 OR bank > 8000000)
   AND isChanged = true;

UPDATE vrp_user_moneys
SET isChanged = false
WHERE (wallet > 4000000 OR bank > 8000000)
   AND isChanged = true;

COMMIT TRANSACTION;
C Perkins
  • 3,733
  • 4
  • 23
  • 37
  • C Perkins thank you for the Asnwer, I made a Schedule to run every 2 seconds, and its working, but it started to insert every 2 seconds the result of the SELECT. I need its to write only if the WALLET or BANK changed know? – Michael RedbuLL Jul 28 '19 at 02:15
  • If you use triggers only, there is no need to schedule queries. A trigger is executed automatically when a certain action takes place, like an insert or update. The trigger can record a log entry automatically when an update is made. Learn about [triggers](https://www.sqlite.org/lang_createtrigger.html) if you do not already understand them. – C Perkins Jul 28 '19 at 03:31
  • But it will insert a second line when as example a WALLET changed value? – Michael RedbuLL Jul 28 '19 at 03:34
  • Then you do not understand the point of the timestamp column. It is there so that you can check what has changed and what has not changed. You can use the timestamp column to either 1) check in the WHERE if a record has changed, or 2) save the timestamp column in the log, then only add log records where the latest timestamp (i.e. max(timestamp) columns do not match. – C Perkins Jul 28 '19 at 03:35