what iis wrong with trigger

[zircon06]

Anyone point me syntax problem with this trigger

CREATE OR REPLACE MPW_MSG_INSERT
BEFORE INSERT ON MSG FOR EACH ROW
BEGIN
IF new.msg_message is like '%|B12|%') THEN
new.msg_message := REPLACEnew.msg_message,'|B12|','|B18|'));
ELSE
( :new.msgdet_message is not like '%B12%');
END IF;
END IF;
END;
/
Thanks in advance

 

[LKBrwnDBA]

Maybe you are missing some code, you have to many "END IF;"





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[BJCooperIT]

It helps if your code is formatted to be a bit more legible and remove the extra parens:

CREATE OR REPLACE MPW_MSG_INSERT
BEFORE INSERT ON MSG FOR EACH ROW
BEGIN
    IF  :new.msg_message is like '%|B12|%' THEN
        :new.msg_message := 
            REPLACE(:new.msg_message,'|B12|','|B18|');
    ELSE[COLOR=red]
        IF :new.msgdet_message is not like '%B12%' THEN
           [b]????????????[/b]
        END IF;[/color]
    END IF;
END;

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[engineer2100]

As Cooper pointed out, the issue is with the line below

 ( :new.msgdet_message is not like '%B12%');

 

[Beilstwh]

As a matter of fact, since it will always replace |B12| with |B18| why not write it as


CREATE OR REPLACE MPW_MSG_INSERT
BEFORE INSERT ON MSG FOR EACH ROW
BEGIN
:new.msg_message := REPLACEnew.msg_message,'|B12|','|B18|');
END;
/

If there is no |B12|, nothing will be changed.

Bill
Oracle DBA/Developer
New York State, USA