Re: question about triggers

In <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;
> )
>

This trigger can be modified as given below :

    CREATE TRIGGER Set_attribute
    BEFORE INSERT ON T1
    FOR EACH ROW
    WHEN ( :new.a1 is NULL )
    BEGIN
     :new.a1 := 10;
    END; If you want to define only a default value for a field, use the DEFAULT clause of field definition instead of handling the situation through a trigger.

VK Nair

>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
>
>
Received on Wed Oct 12 1994 - 20:21:43 CET