What does ':' colon mark means in Oracle?

Question

 if (:new.first_name is not null) then
       :new.first_name := substr(regexp_replace(:new.first_name,'[^'||chr(1)||'-'||chr(127)||']',null),1,20);
    end if;

can someone help me with this code? I cannot understand it!

Answer 1

This code is likely from an UPDATE or INSERT trigger, it refers to a pseudo row containing the new values for the row

UPDATE x
SET y = 'z'
WHERE y = 'a'

A trigger on this update would have an :old pseudorow where column y is 'a' and a :new pseudorow where y is 'z'. If you modify the value of the :new in a before trigger, it will change what is written to the table