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?
updatestatements?ORA-00927: missing equal signis because your update statements are malformed, as I hinted at in my previous comment. See this question to start with, and the documentation.