Trigger error

Navigation
2

I'm making a system that moves cattle tracking and I'm having a question about creating triggers after update.

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

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 "cattle" table it is possible to know which is the tag that is associated with this cattle.

What I want to do is mount a trigger after update so that when the status of any record of the "cattle" table changes to "Idle" in the system, the tag status associated with cattle "becomes" Available "automatically and the" tag_id "field of the" cattle "table becomes null.

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

I'm using the postgreSQL cattle database and the pgadmin program as database manager. I already created the trigger function and the trigger associated with the "cattle" table, but when the 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've never used this perform command and even searching on the internet, I'm not quite understanding how it's done. Below I will also be posting the SQL code I used to create the trigger and the 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 hope for answers and thank you all.

(EDITED) Guys, I did it. My trigger statement method was not letting you perform a String check. So I was giving the IF (NEW.gado_status="Inactive") line error. I'm posting the functional code below: 

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();
    
asked by anonymous 29.10.2016 / 23:13

2 answers

2

Try to look at it as follows. 

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 at all, else if this were to run all the data in your tables would have been changed due to lack of the where tag_id = Old.tag_id filter.

    
30.10.2016 / 23:04
2

I was able to solve the problem. The issue is that with the declaration method I was using at the beginning, I was having trouble declaring strings. So I had problems with the line IF NEW.gado_status="Active".

So, I tried to directly declare the trigger code and it worked. Note that I also added a return null; in 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();
    
31.10.2016 / 22:21
Sequential Static List How to make a button with Razor and Css