update trigger take an insert in the same table [message #383933] |
Sat, 31 January 2009 13:23  |
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 #383937 is a reply to message #383936] |
Sat, 31 January 2009 14:55   |
 |
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 #383973 is a reply to message #383933] |
Sun, 01 February 2009 04:49   |
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 #383988 is a reply to message #383933] |
Sun, 01 February 2009 07:01   |
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
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   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Quote: | your statement
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   |
 |
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  |
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
|
|
|