Trigger to reset row

[fantasma]

Greetings,

I'm having some trouble creating a trigger that, before I insert something in a table, it resets an existing record field to 0.

Example

I have in the table a field named "Name" and another named "Active". Each field has a diferent ID

The last entry of a "Name" should alaways be the active one, but, I must have several entries that can be active, difering in the name and ID.

However, its a bit complex, and I can't think in a solution. Anyone could give me a help to a possible implementation/solution?

Thanks in advance...

[debasisdas]

Why not solve that using a Procedure instead.

[fantasma]

Because I needed C++ to insert a row directly in the DB, and before that, update a single field. However I managed to solve this problem with this trigger

CREATE OR REPLACE TRIGGER RESET_ACTIVO BEFORE
  INSERT ON TABELA
  FOR EACH ROW 
  BEGIN
  
  UPDATE TABELA SET TABELA.ACTIVO = 0 WHERETABELA.NOME = :NEW.NOME;
  
  END;

Thanks for your concern anyway ;)