0

I have a problem with this trigger and I don´t understand why it doesn´t work. I have two tables in my database and I want do create a trigger to update the table "mensajes_info" when I modified the table "mensajes"

CREATE TABLE ESC.MENSAJES (CODIGO NUMBER(20) PRIMARY KEY,
  TEXTO VARCHAR2(200),
  TIPO VARCHAR2(30)) ;

CREATE TABLE MENSAJES_INFO ( TIPO VARCHAR2(30) NOT NULL,
  CUANTOS_MENSAJES NUMBER(2),
  ULTIMO VARCHAR2(200),
  CONSTRAINT MENSAJES_INFO_PK PRIMARY KEY (TIPO ) ENABLE );

create or replace TRIGGER ACTUALIZA_TB_MENS_INFO 
AFTER DELETE OR INSERT OR UPDATE ON ESC.MENSAJES 
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    UPDATE MENSAJES_INFO SET(ULTIMO) SELECT :NEW.TEXT0 FROM ESC.MENSAJES;
  ELSIF DELETING THEN
    UPDATE MENSAJES_INFO SET(CUANTOS_MENSAJES, ULTIMO)
    ((CUANTOS_MENSAJES)-1, NULL) WHERE :OLD.tipo = tipo;
  ELSIF UPDATING THEN
    IF(:OLD.tipo != :NEW.tipo) THEN
      UPDATE  MENSAJES_INFO SET(CUANTOS_MENSAJES, ULTIMO)         
      (select(CUANTOS_MENSAJES)-1, NULL)  WHERE :OLD.tipo != :NEW.tipo;
    ELSE
      UPDATE MENSAJES_INFO SET (CUANTOS_MENSAJES, ULTIMO)
      VALUES(select(CUANTOS_MENSAJES)+1, :NEW.TEXT0) WHERE :new.tipo = tipo ;
    END IF;
  END IF;
END;

I get compilation errors:

Errors for TRIGGER MY_SCHEMA.ACTUALIZA_TB_MENS_INFO:

LINE/COL ERROR
-------- ------------------------------------------------------
3/5      PL/SQL: SQL Statement ignored
3/38     PL/SQL: ORA-00927: missing equal sign
3/45     PLS-00049: bad bind variable 'NEW.TEXT0'
5/5      PL/SQL: SQL Statement ignored
6/5      PL/SQL: ORA-00927: missing equal sign
9/7      PL/SQL: SQL Statement ignored
10/7     PL/SQL: ORA-00927: missing equal sign
12/7     PL/SQL: SQL Statement ignored
13/7     PL/SQL: ORA-00927: missing equal sign
13/42    PLS-00049: bad bind variable 'NEW.TEXT0'

What am I doing wrong?

8
  • 1
    Just saying it "doesn't work" isn't helpful. Do you get a compilation error, a run-time error when you try to insert/delete the table? Where did you find that syntax for your update statements? Commented Jun 12, 2017 at 11:09
  • I have different errors, but the code that one is: Commented Jun 12, 2017 at 11:24
  • Error(6,38): PL/SQL: ORA-00927: falta el signo de igual Commented Jun 12, 2017 at 11:25
  • and another error: Commented Jun 12, 2017 at 11:25
  • 2
    Please edit your question to add new information, particularly code and errors. It would also be helpful to show English error messages. ORA-00927: missing equal sign is because your update statements are malformed, as I hinted at in my previous comment. See this question to start with, and the documentation. Commented Jun 12, 2017 at 11:30

2 Answers 2

1

I doubt that storing number of messages for each type in separate table is good idea, You can always select such data. But if You insist please try something like this:

create or replace trigger actualiza_tb_mens_info
  after delete or insert or update on mensajes
  for each row

begin
  if inserting then

    merge into mensajes_info mi
    using (select :new.tipo tipo, :new.texto texto from dual) m
    on (mi.tipo = m.tipo)
    when matched then update set
      cuantos_mensajes = cuantos_mensajes + 1, ultimo = m.texto
    when not matched then
      insert (tipo, cuantos_mensajes, ultimo) values (m.tipo, 1, m.texto);

  elsif deleting then

    update mensajes_info set
        cuantos_mensajes = cuantos_mensajes - 1, ultimo = null
      where tipo = :old.tipo;

  elsif updating then
    if :old.tipo != :new.tipo then

      update mensajes_info set
          cuantos_mensajes = cuantos_mensajes - 1, ultimo = null
        where tipo = :old.tipo;

      merge into mensajes_info mi
      using (select :new.tipo tipo, :new.texto texto from dual) m
      on (mi.tipo = m.tipo)
      when matched then update set
        cuantos_mensajes = cuantos_mensajes + 1, ultimo = m.texto
      when not matched then
        insert (tipo, cuantos_mensajes, ultimo) values (m.tipo, 1, m.texto);

    else

      update mensajes_info set ultimo = :new.texto 
        where tipo = :new.tipo;

    end if;
  end if;
end;

When inserting use merge statement, when deleting correct update, when updating - it depends on whether type of message changes or not.

Test:

insert into mensajes (codigo, texto, tipo) values (1, 'Text 1', 'Type 1');
insert into mensajes (codigo, texto, tipo) values (2, 'Text 11', 'Type 1');  
insert into mensajes (codigo, texto, tipo) values (3, 'Text 12', 'Type 1');
insert into mensajes (codigo, texto, tipo) values (4, 'Text 21', 'Type 2');

delete from mensajes where codigo = 2;

update mensajes set tipo = 'Type 2' where codigo = 3;

select * from mensajes_info;

TIPO    CUANTOS_MENSAJES  ULTIMO
------  ----------------  --------------------
Type 1                 1  
Type 2                 2  Text 12
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you so much. I didn´t know the sentences "merge into..."
1

You have several issues in your code. The most pressing is that your update statements are malformed, as indicated by the ORA-00927: missing equal sign messages.

UPDATE MENSAJES_INFO SET(ULTIMO) SELECT :NEW.TEXT0 FROM ESC.MENSAJES;

is not valid; firstly you need an equals sign for the assignment as part of the update-set clause; secondly you do not select new/old pseudorecord values from their original table, you just refer directly to the :new.<column>: thirdly your table definition says the column name is texto not text0.

UPDATE MENSAJES_INFO SET ULTIMO = :NEW.TEXTO;

Similarly in the second update, you're missing an equals sign, and you can only set multiple values in parentheses with a subquery:

UPDATE MENSAJES_INFO SET CUANTOS_MENSAJES = CUANTOS_MENSAJES - 1, ULTIMO = NULL

Your third and fourth updates are presumably intended to update the message count for both kinds, and has similar other issues; the third is also goign to update all rows in the table, which isn't what you want, so you probably want something like:

IF(:OLD.tipo != :NEW.tipo) THEN
  UPDATE MENSAJES_INFO SET CUANTOS_MENSAJES = CUANTOS_MENSAJES - 1, ULTIMO = NULL
  WHERE :OLD.tipo = tipo;
END IF;
UPDATE MENSAJES_INFO SET CUANTOS_MENSAJES = CUANTOS_MENSAJES + 1, UNLTIMO = :NEW.TEXT0
WHERE :NEW.tipo = tipo;

So althogether, something more like:

CREATE OR REPLACE TRIGGER ACTUALIZA_TB_MENS_INFO 
AFTER DELETE OR INSERT OR UPDATE ON MENSAJES 
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    UPDATE MENSAJES_INFO SET ULTIMO = :NEW.TEXTO;
  ELSIF DELETING THEN
    UPDATE MENSAJES_INFO SET CUANTOS_MENSAJES = CUANTOS_MENSAJES - 1, ULTIMO = NULL
    WHERE :OLD.tipo = tipo;
  ELSIF UPDATING THEN
    IF(:OLD.tipo != :NEW.tipo) THEN
      UPDATE MENSAJES_INFO SET CUANTOS_MENSAJES = CUANTOS_MENSAJES - 1, ULTIMO = NULL
      WHERE :OLD.tipo = tipo;
    END IF;
    UPDATE MENSAJES_INFO SET CUANTOS_MENSAJES = CUANTOS_MENSAJES + 1, ULTIMO = :NEW.TEXTO
    WHERE :NEW.tipo = tipo;
  END IF;
END;
/

I've also removed unnecessary parentheses. Read more about the update statement, triggers and pseudorecords.

Trying to maintain counts and other aggregate information via triggers can give you headaches though, as simultaneous operations against MENSAJES may leave the MENSAJES_INFO table in a state you don't expect unless access is serialised. A view over the base table would be simpler for the counts:

CREATE VIEW MENSAJES_INFO AS
SELECT TIPO, COUNT(*) AS CUANTOS_MENSAJES
FROM MENSAJES
GROUP BY TIPO;

... but that won't help with ULTIMO...

2 Comments

Alex, your code work. I learn about your comments. .I am a student studying a degree in "computer engineering". I have my exam about ORACLE in one hour. THANK YOU SO MUCH!!!
@VanesaGarcía - PonderStibbons' answer brings up the important issue if whether you will always already have a record in MENSAJES_INFO for every TIPO.. If not, this (and your original) won't insert new records into that table for a never-before-seen TIPO. Look into using merge, as Ponder demonstrates, if you need to deal with that scenario.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.