Re: trigger to insert a few records

If you want to insert rows into the table USER_SUPPLIER_SEQ for each row inserted into the table SUPPLIERS you should to do this in a row level trigger.

The statement level trigger is fired once even if several rows are inserted in the
SUPPLIERS table. Thats why you dont have access to :new.supplier_id in the statement level trigger - there can not exits any current value in this trigger.

JhMa

<daud11_at_hotmail.com> wrote in message news:7k7q5u$fno$1_at_nnrp1.deja.com...
> Hi
>
> I am trying to create a trigger that will insert a few records into
> table B whenever there is an insert in table A. The code that I have is
> giving me compilation errors and I am not sure why.
>
> code for trigger of table suppliers:
> ************************************
>
> create or replace trigger trig1_user_supplier_seq1
> after insert
> on suppliers
> declare
> cursor users_cursor is
> select userid from users;
> tmp_userid char(2);
> begin
> open users_cursor;
> loop
> fetch users_cursor into tmp_userid;
> exit when users_cursor%NOTFOUND;
> insert into user_supplier_seq
> values (tmp_userid, supplierid , 0)
>
> end loop;
> close users_cursor;
> end;
>
> ***************************************
>
> Basically, whenever a new supplier record is added, I would like to add
> all users found in users table together with the current supplier id
> and 0 into the user_supplier_seq table.
> The problem is at the insert statement (supplier id) but I am not sure
> how to correct it. I need the current value of the supplier id (the one
> which has just been inserted and fired the trigger). I have tried
> :new.supplierid for supplierid but still did not work because :new can
> only be used for row trigger.
>
> Hope to get some help from this forum.
>
> thanks
> Daud
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Wed Jun 16 1999 - 10:49:49 CDT