Re: question about triggers

: 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

Try this:

CREATE OR REPLACE TRIGGER Set_attribute
  BEFORE INSERT ON T1
  FOR EACH ROW
BEGIN
  IF (:new.a1 IS NULL) THEN
    :new.a1 := 10;
  END IF;
END; Sujata Received on Thu Oct 13 1994 - 18:51:41 CET