Trigger for a Select

Asked

Viewed 1,607 times

1

I would like to know if it is possible to execute a Trigger when a SELECT is performed in a given table, for example:

CREATE TABLE IF NOT EXISTS "Conversa" (
  "idConversa" SERIAL NOT NULL,
  "idEmissorConversa" INT NOT NULL,
  "idReceptorConversa" INT NOT NULL,
  "mensagemConversa" VARCHAR(250) NOT NULL,
  "statusConversa" INT NOT NULL,
  "horaConversa" TIMESTAMP NOT NULL,
  PRIMARY KEY ("idConversa")

This is the table, the concept of it is to create a Chat, where I will store the Id of who sent the message, ID of who received the message, the statusConversa would be the item in which I wanted to change with Trigger, because: 

statusConversa = 0 -- Significa que a mensagem não foi lida(carregada)
statusConversa = 1 -- Significa que a mensagem foi lida(carregada)

So, when a SELECT is performed in this message, the Trigger must change the statusConversa to 1. So that later, when checking via AJAX if there is a new message, it looks for the messages with statusConversa = 0.

sql postgresql select trigger

1 answer

2


Unable to use a Trigger for a select.

Maybe Voce could use a SELECT FOR UPDATE like this 

 SELECT * FROM Conversa FOR UPDATE;
 UPDATE Conversa SET statusConversa = 1;

  • In this case all items that come from this select will have their statusConversa set as 1, even if it is already set as 1, correct?

  • yes, but you can send the timestamp of the last conversation in the client(browser?) and so return the right result too. I don’t know if this method is efficient

  • Yes, I could take the last timestamp added in the chat block, and compare it to the last of the select, but then I would have to list only the after (newer) of this timestamp, how could I? (The problem is that I’ve never worked very hard with timestamp, so I’m kind of lost in his comparisons, and I find it easier to do it by statusConversa...

Browser other questions tagged sql postgresql select trigger

You are not signed in. Login or sign up in order to post.