RE: locate and replace problem

This is a bug in the implemetation of the REPLACE function. As a workaround you could trim the parameters before calling the APPEND function, i.e.

CREATE TRIGGER sipuser_update FOR sipuser AFTER UPDATE EXECUTE
(
  UPDATE admin.extension
   SET AppData = REPLACE (AppData, RTRIM(:OLD.Name), RTRIM(:NEW.Name))
   WHERE SipUser_id = :OLD.Id AND LOCATE (:OLD.Name, AppData) <> 0;
)

Best Regards,
Thomas


Thomas Anhaus
SAP AG

www.sap.com
Sitz der Gesellschaft/Registered Office: Walldorf, Germany
Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke
Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso Plattner 
Registergericht/Commercial Register Mannheim No HRB 350269

Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt.
Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank.
 
This e-mail may contain trade secrets or privileged, undisclosed, or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying, or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation.
 

> -----Original Message-----
> From: aa aa [mailto:[email protected]] 
> Sent: Montag, 12. März 2007 15:02
> To: [email protected]
> Subject: locate and replace problem
> 
> Hello,
> 
> I need help. In my trigger is not function LOCATE and 
> REPLACE.Locate always
> return 0. If I change :OLD.Name and NEW.Name to string like 
> for example
> 'Mike', it's run.
> 
> Thanx David
> 
> CREATE TABLE admin.sipuser
> (
>     Id               Fixed (11,0)           NOT NULL  DEFAULT 
> SERIAL (1),
>     Name             Varchar (80)  ASCII    NOT NULL  UNIQUE,
> 
>     PRIMARY KEY (Id)
> )
> 
> CREATE TABLE admin.extension
> (
>     Id           Fixed (11,0)          NOT NULL    DEFAULT SERIAL (1),
>        SipUser_id   Fixed (11,0),
>     AppData      Varchar (80) ASCII    NOT NULL    DEFAULT ' ',
>     PRIMARY KEY (Id)
> )
> //
> ALTER TABLE admin.extension
>     FOREIGN KEY fk_sipuser_id (SipUser_id) REFERENCES 
> admin.sipuser (Id) ON
> DELETE CASCADE
> 
> CREATE TRIGGER sipuser_update FOR sipuser AFTER UPDATE EXECUTE
> (
>  UPDATE admin.extension
>    SET AppData = REPLACE (AppData, :OLD.Name, :NEW.Name)
>    WHERE SipUser_id = :OLD.Id AND LOCATE (:OLD.Name, AppData) <> 0;
> )
> 

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[email protected]