Home » SQL & PL/SQL » SQL & PL/SQL » Mutating trigge Issue...
Mutating trigge Issue... [message #185964] Fri, 04 August 2006 09:26 Go to next message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
Hi All,

I have a requirement which is as follows.

There is a table T
On Insert of Table T a trigger code is written to parse the one of Clob column of T.Once data is parsed data is entered into various tables.My requirement is that once data is entered into different tables ,row on which trigger code is fired should be deleted.

Is this possible in the same trigger code.

Rahul Priyadarshy


Re: Mutating trigge Issue... [message #185980 is a reply to message #185964] Fri, 04 August 2006 11:03 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is not trigger that mutates, but table.

There are plenty answers to this problem on the Internet and this Forum - use Google and/or ORAFAQ search engine.
Re: Mutating trigge Issue... [message #186032 is a reply to message #185964] Fri, 04 August 2006 19:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You could create a view on the table and create an instead of trigger on the view and insert through the view, instead of the table, as demonstrated below.

SCOTT@10gXE> CREATE TABLE T (t_clob_col CLOB)
  2  /

Table created.

SCOTT@10gXE> CREATE TABLE a (a_col VARCHAR2 (30))
  2  /

Table created.

SCOTT@10gXE> CREATE TABLE b (b_col VARCHAR2 (30))
  2  /

Table created.

SCOTT@10gXE> CREATE OR REPLACE VIEW t_view AS SELECT * FROM t
  2  /

View created.

SCOTT@10gXE> CREATE OR REPLACE TRIGGER t_view_trig
  2    INSTEAD OF INSERT ON t_view
  3  BEGIN
  4    INSERT INTO a (a_col)
  5    VALUES (SUBSTR (:NEW.t_clob_col, 1, INSTR (:NEW.t_clob_col, ' ') - 1));
  6    INSERT INTO b (b_col)
  7    VALUES (SUBSTR (:NEW.t_clob_col, INSTR (:NEW.t_clob_col, ' ') + 1));
  8  END t_view_trig;
  9  /

Trigger created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> INSERT  INTO t_view (t_clob_col)
  2  SELECT  'This is a test.' FROM DUAL
  3  UNION ALL
  4  SELECT  'And another test.' FROM DUAL
  5  /

2 rows created.

SCOTT@10gXE> COMMIT
  2  /

Commit complete.

SCOTT@10gXE> SELECT * FROM t_view
  2  /

no rows selected

SCOTT@10gXE> SELECT * FROM t
  2  /

no rows selected

SCOTT@10gXE> SELECT * FROM a
  2  /

A_COL
------------------------------
This
And

SCOTT@10gXE> SELECT * FROM b
  2  /

B_COL
------------------------------
is a test.
another test.

SCOTT@10gXE> 



Re: Mutating trigge Issue... [message #186284 is a reply to message #186032] Mon, 07 August 2006 05:24 Go to previous messageGo to next message
rahul.priyadarshy
Messages: 28
Registered: December 2005
Junior Member
Hi Barbara,

Thanks a lot for the solution.
Is this possible to implement the whole at Table level itself instead of View level.
Re: Mutating trigge Issue... [message #186358 is a reply to message #186284] Mon, 07 August 2006 12:29 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You could do your inserts into the other tables in an after insert row trigger and do the delete from the table that you inserted into in an after insert statement level trigger, as demonstrated below. This assumes that you will never have any rows in the t table, since it will delete all of them.

SCOTT@10gXE> CREATE TABLE T (t_clob_col CLOB)
  2  /

Table created.

SCOTT@10gXE> CREATE TABLE a (a_col VARCHAR2 (30))
  2  /

Table created.

SCOTT@10gXE> CREATE TABLE b (b_col VARCHAR2 (30))
  2  /

Table created.

SCOTT@10gXE> CREATE OR REPLACE TRIGGER t_after_insert_for_each_row
  2    AFTER INSERT ON t
  3    FOR EACH ROW
  4  BEGIN
  5    INSERT INTO a (a_col)
  6    VALUES (SUBSTR (:NEW.t_clob_col, 1, INSTR (:NEW.t_clob_col, ' ') - 1));
  7    INSERT INTO b (b_col)
  8    VALUES (SUBSTR (:NEW.t_clob_col, INSTR (:NEW.t_clob_col, ' ') + 1));
  9  END t_after_insert_for_each_row;
 10  /

Trigger created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> CREATE OR REPLACE TRIGGER t_after_insert
  2    AFTER INSERT ON t
  3  BEGIN
  4    DELETE FROM t;
  5  END t_after_insert;
  6  /

Trigger created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> INSERT  INTO t (t_clob_col)
  2  SELECT  'This is a test.' FROM DUAL
  3  UNION ALL
  4  SELECT  'And another test.' FROM DUAL
  5  /

2 rows created.

SCOTT@10gXE> COMMIT
  2  /

Commit complete.

SCOTT@10gXE> SELECT * FROM t
  2  /

no rows selected

SCOTT@10gXE> SELECT * FROM a
  2  /

A_COL
------------------------------
This
And

SCOTT@10gXE> SELECT * FROM b
  2  /

B_COL
------------------------------
is a test.
another test.

SCOTT@10gXE>

Previous Topic: Is there any DBMS property or any other property to get Recent executed SQL from Procedure
Next Topic: Inserts
Goto Forum:
  


Current Time: Sun Dec 11 00:34:15 CST 2016

Total time taken to generate the page: 0.20405 seconds