CODEWITHSUNDEEP
oracle-databaseplsqltriggers
user3579222
user3579222

Reputation: 1420

Oracle Trigger: When Prefix NEW with Colon (:)

Here I found the following example

CREATE OR REPLACE TRIGGER Print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
  FOR EACH ROW
WHEN (new.Empno > 0)
DECLARE
    sal_diff number;
BEGIN
    sal_diff  := :new.sal  - :old.sal;
    dbms_output.put('Old salary: ' || :old.sal);
    dbms_output.put('  New salary: ' || :new.sal);
    dbms_output.put_line('  Difference ' || sal_diff);
END;
/

Within the WHEN I write new while after BEGIN I write :new. What is the difference between new and :new

Upvotes: 0

Views: 560

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

From the documentation:

In the trigger_body of a simple trigger or the tps_body of a compound trigger, a correlation name is a placeholder for a bind variable. Reference the field of a pseudorecord with this syntax:

:pseudorecord_name.field_name

In the WHEN clause of a conditional trigger, a correlation name is not a placeholder for a bind variable. Therefore, omit the colon in the preceding syntax.

So you do NOT have the colon in this line:

WHEN (new.Empno > 0)

But you do in the body, e.g.:

sal_diff  := :new.sal  - :old.sal;

Triggers can sometimes cause confusion because they're in two parts; essentially a SQL/DDL part which in your case is:

CREATE OR REPLACE TRIGGER Print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON Emp_tab
  FOR EACH ROW
WHEN (new.Empno > 0)

and then the PL/SQL body which in you case start from:

DECLARE

but in other cases there won't be a declaration section and then it would start from

BEGIN

When a trigger has a compilation error the line number reported for PLS- errors is counted from the start of the PL/SQL section, not form the CREATE - which can be confusing...

Upvotes: 4

Related Questions