Error in Trigger

Question

I am making a system that deals with monitoring cattle and I have a question about creating triggers after update.

The situation is as follows: in my bank there are several tables that have foreign keys to each other. Within these tables, there are the tables "cattle" and the table "tag". The "cattle" table stores information about cattle and the "tag" table stores information about the tracking device that I will use to track cattle. Both tables have the field "status".

In the "cattle" table there is a field called "tag_id" which is the foreign key related to the "tag_id" field of the "tag" table. With this, by the table "cattle" it is possible to know which is the tag that is associated with this cattle.

What I want to do is ride a Trigger after update so that, when in the system the status of some record of the "cattle" table is changed to "Inactive", the status of the tag associated with cattle becomes "Available" automatically and the "tag_id" field of the "cattle" table becomes null.

For example, if I take cattle with a cattle tag = 1 and change its status to "Inactive", I wanted the status of the tag associated with this cattle to have its "status" field changed to "Available" automatically in the "tag" table and the "tag_id" field of this row in the "cattle" table to be empty.

I am using the postgresql database and the pgadmin program as a database manager. I have already created Trigger Function and Trigger by associating to the "cattle" table, but when Trigger is activated the following error appears:

ERROR: query has no Destination for result data. HINT : if you want to discard the Results of a SELECT, use PERFORM Instead.

The point is that I never used this perform command and even searching the internet, I’m not really understanding how to do it. Below I will also be posting the SQL code I used to create Trigger and Trigger Function:

CREATE OR REPLACE FUNCTION public.atualiza_status_tag()
RETURNS trigger LANGUAGE plpgsql
AS
'begin

select tag_status FROM TAG where tag.tag_id = new.tag_id;

IF (NEW.gado_status = "Inativo")
THEN
UPDATE TAG
SET tag_status = "Disponivel";
UPDATE GADO
SET tag_id = null;
END IF;
end; ';


CREATE TRIGGER tratamento_tag AFTER UPDATE
ON gado FOR EACH ROW  
  EXECUTE PROCEDURE atualiza_status_tag();

I wait for answers and thank you all.

(EDITED) Guys, I got it. My Rigger declaration method wasn’t letting me run a String check. So I was making a mistake on the line IF (NEW.gado_status = "Inactive"). I am posting below the functional code:

CREATE OR REPLACE FUNCTION public.atualiza_status_tag()
RETURNS trigger
AS
$$
begin

IF NEW.gado_status = 'Inativo'
THEN
UPDATE TAG
SET tag_status = 'Disponivel'
where tag_id = old.tag_id;

UPDATE GADO
SET tag_id = null
where tag_id = old.tag_id;

END IF;
return null;
end
$$  LANGUAGE plpgsql





CREATE TRIGGER tratamento_tag AFTER UPDATE
ON gado FOR EACH ROW  
EXECUTE PROCEDURE atualiza_status_tag();

Answer 1

Try to land this way.

CREATE OR REPLACE FUNCTION public.atualiza_status_tag()
RETURNS trigger LANGUAGE plpgsql
AS
'begin

   IF NEW.gado_status = 'Inativo'
   THEN
     UPDATE TAG
     SET tag_status = 'Disponivel';
     where tag_id = old.tag_id

    UPDATE GADO
    SET tag_id = null;
    where tag_id = new.tag_id

  END IF;
end; ';


CREATE TRIGGER tratamento_tag AFTER UPDATE
ON gado FOR EACH ROW  
  EXECUTE PROCEDURE atualiza_status_tag();

Your select made no sense, else if it came to running all the data from your tables would have been changed due to lack of filter where tag_id = Old.tag_id.

Answer 2

I managed to solve the problem. The point is that with the declaration method I was using at the beginning, I was having trouble declaring Strings. So I was having trouble on the line IF NEW.gado_status = "Active".

So I try to declare Trigger’s code directly and it worked. Note that I also added a Return null; at the end, because it needs to have a return.

CREATE OR REPLACE FUNCTION public.atualiza_status_tag()
RETURNS trigger
AS
$$
begin

IF NEW.gado_status = 'Inativo'
THEN
UPDATE TAG
SET tag_status = 'Disponivel'
where tag_id = old.tag_id;

UPDATE GADO
SET tag_id = null
where tag_id = old.tag_id;

END IF;
return null;
end
$$  LANGUAGE plpgsql





CREATE TRIGGER tratamento_tag AFTER UPDATE
ON gado FOR EACH ROW  
EXECUTE PROCEDURE atualiza_status_tag();