trigger to insert a few records

From: <daud11_at_hotmail.com>
Date: Wed, 16 Jun 1999 09:18:58 GMT
Message-ID: <7k7q5u$fno$1@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 - 04:18:58 CDT