Re: How do I CREATE TRIGGER?

Xuequn Xu <xux_at_informa.bio.caltech.edu> wrote in message news:7nn8vs$5u4_at_gap.cco.caltech.edu...
> Try a better approach:
>
> create trigger trig1 before insert on testtab2
> for each row
> BEGIN
> :new.id = headid.nextval;
> end;
> /

It needs some revision.
NEXTVAL is a pseudocolumn, it should be used in a SQL statement. In addtion, you must deal with the UPDATE command. The revised edtion is:

create or replace trigger trig1
before insert or update of id on testtab2 for each row
begin
  if inserting then
    select headid.nextval into :new.id from dual;   else
    :new.id:=:old.id;
  end if;
end;
/

And, it's better that the trigger name is relevant to the table name and its action.

> Notice that you should use "/" after "end;" to tell sqlplus or svrmgrl to
> execute the PL/SQL block.
>
> Robert Xu, Oracle DBA
> Caltech, Pasadena, CA
>
>
> kev (kevin.porter_at_fast.no) wrote:
> : I'm trying to create a trigger to autonumber an id field.
>
> : In svrmgrl, I wrote:
>
> : create trigger trig1 before insert of id on testtab2
> : 2> DECLARE
> : 3> BEGIN
> : 4> insert into testtab2 (id) values (headid.nextval);
> : 5> END;
> : 6> ;

Never do so. It would cause recursion. Received on Sat Jul 31 1999 - 09:26:34 CDT