Re: question about triggers

In article <37gpbf$3ec_at_news-rocq.inria.fr>, dimi_at_nikita.inria.fr (Dimitri Tombroff ) writes:
|> I am a beginner in writing triggers with Oracle and I have a
|> simple question. I'll use a short example:
|> Suppose I have a table T with attribute a1 and a2. What I want to do is
|> the following : each time a tuple is inserted with a NULL value
|> for attribute a1, set it to (say) 10.
|>
|> My problem is : a row trigger like
|>
|> CREATE TRIGGER Set_attribute
|> AFTER INSERT ON T1
|> FOR EACH ROW
|> WHEN ( new.a1 = NULL )
|> (
|> UPDATE T1
|> SET a1 = 10;
|> )
|>
|> should be rejected because I attempt to modify a mutating table , right ?
|> (Furthermore it is ambiguous since I don't know if it updates only
|> the inserted tuples or some other tuples as well)
|>
|> So how do you do this ?
|>
|> CREATE TRIGGER Set_attribute
|> AFTER INSERT ON T1
|> FOR EACH STATEMENT
|> (
|> UPDATE T1
|> SET a1 = 10
|> WHERE a1 = NULL ;
|> )
|>
|> should be accepted but it doesn't have the semantics I want since
|> it could update tuples not inserted by the triggering SQL statement.
|>
|> Thanks in advance for any help,
|>
|> Dimitri Tombroff
|>

You want to use a row level trigger, and the :new record. This is the row which will be inserted. Like this (note the differences between this example and yours):

create trigger set_attribute
BEFORE insert on t1
for each ROW
begin
  if :new.a1 is NULL then
    :new.a1 := 10;
  end if;
end set_attribute;

See chapter 8 of the Application Developer's Guide for more information. Received on Thu Oct 13 1994 - 17:20:23 CET