Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Question - How to insert multiple rows?
Trigger Question - How to insert multiple rows? [message #193293] Fri, 15 September 2006 10:16 Go to next message
skiskis
Messages: 10
Registered: June 2006
Junior Member
Hi,

I have written a trigger which does not work properly and produces the following errors:

PLS-00049 Bad bind variable 'NEW'
PL/SQL:ORA-00903: Invalid table name

My code is as follows:

CREATE OR REPLACE TRIGGER Trg_Task_Message
BEFORE INSERT
ON TADMIN.TASK_XML_HISTORY_tmp
DECLARE
BEGIN
INSERT INTO TASK_XML_HISTORY
SELECT * FROM :new;
END;
/

I'd like to be able to select multiple rows for insert. Is this possible? What is the correct syntax in which to do so within a trigger?

Thanks,

Scott
Re: Trigger Question - How to insert multiple rows? [message #193302 is a reply to message #193293] Fri, 15 September 2006 10:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The ":new" references exaclty 1 row of the insert/update ":old" one row of the delete/update trigger, so you can't get "multiple" rows out there.

But you can create a row-level trigger, like for example :

SQL> CREATE TABLE t1 (
  2    c1 VARCHAR2(10),
  3    c2 VARCHAR2(10)
  4  );

Table created.

SQL>
SQL> CREATE TABLE t2 (
  2    dat DATE,
  3    c1 VARCHAR2(10),
  4    c2 VARCHAR2(10)
  5  );

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER trig
  2  BEFORE INSERT ON t1
  3  FOR EACH row
  4  DECLARE
  5  BEGIN
  6  INSERT INTO t2 VALUES (SYSDATE, :NEW.c1, :NEW.c2);
  7  END;
  8  /

Trigger created.

SQL>
SQL> INSERT INTO t1 VALUES ('some', 'data');

1 row created.

SQL>
SQL> SELECT * FROM t1;

C1         C2
---------- ----------
some       data

SQL>
SQL> SELECT * FROM t2;

DAT       C1         C2
--------- ---------- ----------
15-SEP-06 some       data

SQL>
SQL> DROP TABLE t1;

Table dropped.

SQL>
SQL> DROP TABLE t2;

Table dropped.


Re: Trigger Question - How to insert multiple rows? [message #193323 is a reply to message #193302] Fri, 15 September 2006 12:47 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
It looks like though that you want to add a second row to the same table. This does not make sense as each insert will triger the insert trigger, etc. and you will be in a recursive loop.
Re: Trigger Question - How to insert multiple rows? [message #193625 is a reply to message #193293] Mon, 18 September 2006 09:08 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
First off, :new refers to a specific field (like :new.c1). Second, using :new by itself has no meaning. Third, I don't think you can use a variable to specify a table name (or field name). Variables are for values. Finally, it is a "really bad" idea to attempt to update information in the table you're running the trigger against, especially if you're doing it with a "before insert".
Previous Topic: what are *.dbf files
Next Topic: How to retrieve columns by its order
Goto Forum:
  


Current Time: Thu Dec 08 06:00:05 CST 2016

Total time taken to generate the page: 0.19070 seconds