You Asked

Hello

I might be asking you a basic question ,

Question :when I execute below procedure it compiles without failure. I did not declare bind variable here right ? but still it executes ?

create or replace trigger crazy_trigge1r before update
on employees
for each row
begin
if :new.last_name = 'Pataballa' then
:new.salary := :new.salary * 0.1;
dbms_output.put_line('update done for the last name '||:new.last_name);
end if;
end;
/

please correct me I am just trying to understand things. If there is something wrong with my question just ignore.

and Connor said...

OK, this is a trigger not a procedure, and things are slightly different in triggers.

Within a (row level) trigger, we automatically provide the ":new" and ":old" references to the columns as bind variables available to you. It's designed to save you effort.

Similarly, in a normal procedure, if you had:

procedure P is
  x int;
  result int;
begin
  select count(*) into result from T where MY_COL = x;
end;

then we automatically do all the binding for input and output for you. Most other languages would require something like:

DefineSQL(s,"select count(*) into :1 from T where MY_COL = :2");
DefineBind1(s,":1",result,type="Output");
DefineBind2(s,":3",x,type="Input");
Execute(s)

and so forth. PL/SQL does all that under the covers for you. It's nice like that :-)

We dont ignore any questions :-)

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

pseudocolumn

s123, February 06, 2016 - 3:34 am UTC

hello Connor,

appreciate your help on this. I am not able to understand the functionality.

any further help you can provide me understanding on using new. and create row level trigger .

February 06, 2016 - 7:44 am UTC

I'm not entirely sure I understand what you're asking for, so here's a quick tour of triggers for you. Its a simplification of the entire story, but hopefully gets you going.

Triggers are code that executes as part of a insert/update/delete statement.

A trigger can be statement or row level, because (for example), a single 'delete' statement might delete 20 rows. Do we want the trigger to execute just *once* (eg log the fact that user John ran a delete), or do we want the trigger to execute once for *each row* deleted (eg log the actual rows that user John deleted).

The former is a statement trigger, the latter a row level trigger.

For row level triggers, you (may) want access to the row values in question for that particular row being operated on. For that, we have the "new" and "old" syntax. For an insert, there is no "old", and for a delete, there is no concept of "new" for obvious reasons.

Each column value (within a row level) trigger has its own "new", "old" reference, which you can use for interrogation or logging purposes. Similarly, you can actually *modify* these values, for example, as you insert a row, you can alter the :new. referenced columns to change what ends up being inserted. Generally that's asking for trouble, because a front end application may think it has inserted a salary of $10, only to then find $12 in the database etc.

Plenty of good info here as well http://docs.oracle.com/database/121/LNPLS/triggers.htm

Hope this helps.

triggers

m123, February 06, 2016 - 2:31 pm UTC

thankyou

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer