Home » SQL & PL/SQL » SQL & PL/SQL » update trigger take an insert in the same table (Oracle 10g)
update trigger take an insert in the same table [message #383933] Sat, 31 January 2009 13:23 Go to next message
Beule
Messages: 5
Registered: January 2009
Location: Hamburg
Junior Member
Hi,

maybe I have a table (PM) like this

id name foreign_key
1 Kalle null

update PM set name = 'Dieter' where id = 1


The result of the fireing update trigger should looks like this:

id name foreign_key
1 Dieter null
2 Kalle 1

I have no idea to solve this problem?!

(The Mutation error in Oracle Database Triggers ???)

Many Thanks to our help

Götz
Re: update trigger take an insert in the same table [message #383936 is a reply to message #383933] Sat, 31 January 2009 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is not SQL but programmatic, so do it in a procedure.

Regards
Michel
Re: update trigger take an insert in the same table [message #383937 is a reply to message #383936] Sat, 31 January 2009 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
However you can do it with an "instead of" trigger but it is not recommended:
SQL> create table pm (id integer primary key, name varchar2(20), fk integer references pm(id));

Table created.

SQL> create sequence pm_seq;

Sequence created.

SQL> rename pm to pm_tab;

Table renamed.

SQL> create or replace view pm as select * from pm_tab;

View created.

SQL> create or replace trigger pm_trig instead of update on pm for each row
  2  begin
  3    insert into pm_tab values (pm_seq.nextval, :old.name, :old.id);
  4    update pm_tab set name = :new.name where id = :old.id;
  5  end;
  6  /

Trigger created.

SQL> insert into pm values (pm_seq.nextval, 'Kalle', null);

1 row created.

SQL> select * from pm;
        ID NAME                         FK
---------- -------------------- ----------
         1 Kalle

1 row selected.

SQL> update PM set name = 'Dieter' where id = 1;

1 row updated.

SQL> select * from pm;
        ID NAME                         FK
---------- -------------------- ----------
         1 Dieter
         2 Kalle                         1

2 rows selected.

But some modifications have to be done to make it work in multiuser environment.

Regards
Michel

[Updated on: Sat, 31 January 2009 15:06]

Report message to a moderator

Re: update trigger take an insert in the same table [message #383969 is a reply to message #383933] Sun, 01 February 2009 03:28 Go to previous messageGo to next message
Beule
Messages: 5
Registered: January 2009
Location: Hamburg
Junior Member
Bonjour,
thanks for your answer.

My problem is the following. I'm working with VisualWorks (VW) (Smalltalk) and the ObjectLens. The ObjectLens is an OO/R-Mapper. If I make a check against the database VW take a look of an existing table "PM". There is no mapping between objects and views. If I make a modification against the object "PM", the ObjectLens fire an update like I describe before.

For the sybase db its not a problem....

Now I can make many modifications (every update must be a stored procedure).

Oooohhhhh noooooo Confused

..any ideas???

Thank you very much!

Götz
Re: update trigger take an insert in the same table [message #383972 is a reply to message #383969] Sun, 01 February 2009 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Now I can make many modifications (every update must be a stored procedure).

So what is the problem? Do all the modifications you mentionned in the stored procedure.

Regards
Michel
Re: update trigger take an insert in the same table [message #383973 is a reply to message #383933] Sun, 01 February 2009 04:49 Go to previous messageGo to next message
Beule
Messages: 5
Registered: January 2009
Location: Hamburg
Junior Member
Hi Michel,

my problem is a lot of work. We have no stored procedures for insert and updates. Now I must write for every table (with history) a stored procedure and I must take many modifications in the frontend (VisualWorks) so that VW have to call stored procedures instead of the generating insert/update statements.

Regards
Götz

[Updated on: Sun, 01 February 2009 04:50]

Report message to a moderator

Re: update trigger take an insert in the same table [message #383975 is a reply to message #383973] Sun, 01 February 2009 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I gave you an example that requires no change in the application.
But you have to know that this is NO MORE the same application but a NEW one and so thinking that there will be no cost is not reasonable.

Regards
Michel
Re: update trigger take an insert in the same table [message #383988 is a reply to message #383933] Sun, 01 February 2009 07:01 Go to previous messageGo to next message
Beule
Messages: 5
Registered: January 2009
Location: Hamburg
Junior Member
Hi Michel

you wrote:
I gave you an example that requires no change in the application.

see my answer below
If I make a check against the database VW take a look of an existing table "PM"

your statement
rename pm to pm_tab;


After VW check against the database -> Message: "VisualWorks is missing the table pm".

Your example works fine, but in combination with the oo-framework from VW you get problems. Objects only mappping tables. If I do the following statement in VW (Smalltalk) "(pm name: 'Dieter') update" VW generates the required update.

VW has now to call procedures. C'est la vie

Regards
Götz
Re: update trigger take an insert in the same table [message #383996 is a reply to message #383988] Sun, 01 February 2009 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
your statement
rename pm to pm_tab;


After VW check against the database -> Message: "VisualWorks is missing the table pm".

But after there is:
create or replace view pm as select * from pm_tab;

So PM exists, see the rest of the example, I always use PM to insert, select and update.

Quote:
Objects only mappping tables.

Are you sure it makes a distinction between table and view?Anyway, it is better to make it clear and do it in procedure.

Regards
Michel

[Updated on: Sun, 01 February 2009 08:29]

Report message to a moderator

Re: update trigger take an insert in the same table [message #384008 is a reply to message #383933] Sun, 01 February 2009 14:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Here are two more methods to choose from.

The first method uses dbms_job within a before update row trigger to run the insert upon commit of the update.

The second method uses a package to hold an array of old values, a before update trigger to empty the array, a before update row trigger to store the old values to the array, and an after update trigger to do the inserts using the values from the array in the package.

SCOTT@orcl_11g> -- table:
SCOTT@orcl_11g> CREATE TABLE pm
  2    (id	     INTEGER PRIMARY KEY,
  3  	name	     VARCHAR2 (20),
  4  	foreign_key  NUMBER REFERENCES pm (id))
  5  /

Table created.

SCOTT@orcl_11g> -- sequence and triggers:
SCOTT@orcl_11g> CREATE SEQUENCE pm_seq
  2  /

Sequence created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER pm_before_insert
  2    BEFORE INSERT ON pm
  3    FOR EACH ROW
  4  BEGIN
  5    SELECT pm_seq.NEXTVAL
  6    INTO   :NEW.id
  7    FROM   DUAL;
  8  END pm_before_insert;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER pm_before_update_row
  2    BEFORE UPDATE ON pm
  3    FOR EACH ROW
  4  DECLARE
  5    v_job  NUMBER;
  6  BEGIN
  7    DBMS_JOB.SUBMIT
  8  	 (v_job,
  9  	  'INSERT INTO pm (name, foreign_key)
 10  	   VALUES (''' || :OLD.name || ''', ' || :OLD.id || ');');
 11  END pm_before_update_row;
 12  /

Trigger created.

SCOTT@orcl_11g> -- test of insert and update:
SCOTT@orcl_11g> INSERT INTO pm (name) VALUES ('Kalle')
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT * FROM pm
  2  /

        ID NAME                 FOREIGN_KEY
---------- -------------------- -----------
         1 Kalle

SCOTT@orcl_11g> UPDATE pm SET name = 'Dieter' WHERE id = 1
  2  /

1 row updated.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> EXEC DBMS_LOCK.SLEEP (5)

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM pm
  2  /

        ID NAME                 FOREIGN_KEY
---------- -------------------- -----------
         1 Dieter
         2 Kalle                          1

SCOTT@orcl_11g> 



SCOTT@orcl_11g> -- table:
SCOTT@orcl_11g> CREATE TABLE pm
  2    (id	     INTEGER PRIMARY KEY,
  3  	name	     VARCHAR2 (20),
  4  	foreign_key  NUMBER REFERENCES pm (id))
  5  /

Table created.

SCOTT@orcl_11g> -- sequence, package, and triggers:
SCOTT@orcl_11g> CREATE SEQUENCE pm_seq
  2  /

Sequence created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER pm_before_insert
  2    BEFORE INSERT ON pm
  3    FOR EACH ROW
  4  BEGIN
  5    SELECT pm_seq.NEXTVAL
  6    INTO   :NEW.id
  7    FROM   DUAL;
  8  END pm_before_insert;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE pm_pkg
  2  AS
  3    TYPE pm_array IS TABLE OF pm%ROWTYPE INDEX BY BINARY_INTEGER;
  4    old_pm_vals  pm_array;
  5    empty_array  pm_array;
  6  END pm_pkg;
  7  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER pm_before_update
  2    BEFORE UPDATE ON pm
  3  BEGIN
  4    pm_pkg.old_pm_vals := pm_pkg.empty_array;
  5  END pm_before_udpate;
  6  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER pm_before_update_row
  2    BEFORE UPDATE ON pm
  3    FOR EACH ROW
  4  DECLARE
  5    i  INTEGER := pm_pkg.old_pm_vals.COUNT + 1;
  6  BEGIN
  7    pm_pkg.old_pm_vals(i).id := :OLD.id;
  8    pm_pkg.old_pm_vals(i).name := :OLD.name;
  9  END pm_before_update_row;
 10  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER pm_after_update
  2    AFTER UPDATE ON pm
  3  BEGIN
  4    FOR i IN 1 .. pm_pkg.old_pm_vals.COUNT LOOP
  5  	 INSERT INTO pm (name, foreign_key)
  6  	 VALUES (pm_pkg.old_pm_vals(i).name, pm_pkg.old_pm_vals(i).id);
  7    END LOOP;
  8  END pm_after_update;
  9  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- test of insert and update:
SCOTT@orcl_11g> INSERT INTO pm (name) VALUES ('Kalle')
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT * FROM pm
  2  /

        ID NAME                 FOREIGN_KEY
---------- -------------------- -----------
         1 Kalle

SCOTT@orcl_11g> UPDATE pm SET name = 'Dieter' WHERE id = 1
  2  /

1 row updated.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT * FROM pm
  2  /

        ID NAME                 FOREIGN_KEY
---------- -------------------- -----------
         1 Dieter
         2 Kalle                          1

SCOTT@orcl_11g> 



Re: update trigger take an insert in the same table [message #384197 is a reply to message #383933] Mon, 02 February 2009 12:19 Go to previous message
Beule
Messages: 5
Registered: January 2009
Location: Hamburg
Junior Member
Hi Michel,

I must say Sorry!!! Your solution works fine.

If I make a check against the datamodel from VW and VW is missing a column, then I created it from the datamodel, not from the integreated datamodeler in VW.

Hi Barbara,
your both solutions looks very tricky. I need time to understand it. I'm Oracle beginner.

Barbara, Michel thank you .....

Regards
Happy Götz
Previous Topic: Determine rows without a Date/Time column?
Next Topic: Temporary tables
Goto Forum:
  


Current Time: Sat Feb 08 19:41:11 CST 2025