:OLD and :NEW values in PL/SQL

Hi... I saw some lines in pl/sql, that were using this sintax below:

(in the first line it has REFERENCING NEW AS NEW AND OLD AS OLD)

and then:
 
BLQ_CDEMPRESA = :OLD.LCT_CDEMPRES

So, what this sintax means? What means the column name and old.column_name?
Thiago_corporativeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Hope you have provided the Trigger code above.
In a trigger, You have two states of data one before the code is executed and the other after the code is executed.
Earlier is called as OLD value and the later is called as :NEW value.

To initiate that , NEW AS NEW AND OLD AS OLD
Below is the syntax of a trigger.

Hope that clarifies.
CREATE [OR REPLACE] TRIGGER <trigger_name>
    {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
    [REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]
    [FOR EACH ROW [WHEN (<trigger_condition>)]]
    <trigger_body>

Open in new window

0
 
CakinciCommented:
It is a propoerty in Pl/SQL that you can create a trigger that works on both new values and old values (In update trigger for example)

You can use this property to create a log file of changes(or table),auidit the changes or just output purposes.

Let me give you a simple example of code:
When you try to update id field in below table it will output such text:
update employee set id='01' where id='02';
:new.id: 01
:old.id: 02


I hope this will help.

 create table Employee(
      ID                 VARCHAR2(4 BYTE)         NOT NULL
  )
 
 
-- BEFORE UPDATE Trigger
CREATE OR REPLACE TRIGGER employee_before_update
     BEFORE UPDATE
         ON employee
         FOR EACH ROW
      
      BEGIN
      dbms_output.put_line(':new.id: '||:new.id);
      dbms_output.put_line(':old.id: '||:old.id);
 
      END;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.