Home » SQL & PL/SQL » SQL & PL/SQL » Using Trigger to maintain history (Oracle 10.2g)
Using Trigger to maintain history [message #423933] Tue, 29 September 2009 05:52 Go to next message
singhabhijitkumar
Messages: 7
Registered: February 2008
Location: india
Junior Member
I have a metadata table with the following fields:

PERFORMANCE_METADATA_ID
COUNTRY
PRODUCT_CATEGORY
EXPRESSION
TARGET_COLUMN
EFF_START_DATE
EFF_END_DATE

The data in this table is edited manually using SQL*Developer tool. I want to create a trigger on this table such that when I update the field EXPRESSION or TARGET_COLUMN, it creates a new row with the updated data. The existing row, field EFF_END_DATE should be set to SYSDATE - 1. The EFF_START_DATE of the new row created from trigger should be set to SYSDATE.

In case any row is deleted then the trigger should set the EFF_END_DATE to SYSDATE-1 rather than deleting the row.

If anyone has done anything like this then please help

Report message to a moderator

Re: Using Trigger to maintain history [message #423940 is a reply to message #423933] Tue, 29 September 2009 05:59 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi

You can use after update trigger row level.

Cheers
Soni

Report message to a moderator

Re: Using Trigger to maintain history [message #423943 is a reply to message #423933] Tue, 29 September 2009 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post what you already tried to do, there is no special difficulties in writing this trigger but you have to create a view upon the table and use an instead of trigger at least for the second part (delete->update).
Just one remark, why using "sysdate-1" and not "sysdate" as end date? You will have issue doing so and sysdate is surely the best thing as it is what really happened.

Regards
Michel

[Updated on: Tue, 29 September 2009 06:01]

Report message to a moderator

Re: Using Trigger to maintain history [message #423944 is a reply to message #423933] Tue, 29 September 2009 06:05 Go to previous messageGo to next message
singhabhijitkumar
Messages: 7
Registered: February 2008
Location: india
Junior Member
I am using sysdate-1 for avoiding overlaps and to enable reruns.

WHERE run_date BETWEEN eff_start_date AND eff_end_date

I have never used triggers so wanted help from someone who has already done something like this. If he can share his code I can look into it and modify it accordingly.

Also, i think my approach is leading to mutating trigger because I want the trigger to update the Eff_date_date.

Report message to a moderator

Re: Using Trigger to maintain history [message #423949 is a reply to message #423944] Tue, 29 September 2009 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Do not think, be sure, make test above all if you never write a trigger.

Database Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Quote:
I am using sysdate-1 for avoiding overlaps and to enable reruns.

I don't know what you mean with this but I maintain it is an error.
There is no overlap if a starting point is equal to the ending of another row.

Regards
Michel

Report message to a moderator

Re: Using Trigger to maintain history [message #423954 is a reply to message #423949] Tue, 29 September 2009 06:17 Go to previous messageGo to next message
singhabhijitkumar
Messages: 7
Registered: February 2008
Location: india
Junior Member
see if i keep the eff_end_date of previous version of data same as eff_start_date of the new version, then when i join the table and do a
WHERE tab.run_date BETWEEN metadata_table.eff_start_date AND metadata_table.eff_end_date
then the Metadata table will return two rows. Something like this is done in Data warehousing and is known as SCD type 2.

I have read the about Triggers but wanted someone who has already done this to help me. I thought of creating a BEFORE UPDATE OR DELETE trigger. but i feel this approach will lead to mutating trigger as already explained.

So please share your approach.

Report message to a moderator

Re: Using Trigger to maintain history [message #423958 is a reply to message #423954] Tue, 29 September 2009 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I thought of creating a BEFORE UPDATE OR DELETE trigger.

Do it and post your code.

Quote:
but i feel this approach will lead to mutating trigger as already explained

Check it. It takes 10 minutes to build a test case.
Do I not talk you about instead of trigger?

Regards
Michel

Report message to a moderator

Re: Using Trigger to maintain history [message #423961 is a reply to message #423954] Tue, 29 September 2009 08:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@singhabhijitkumar - the ethos of this sight is something like this:

we believe that you will learn much more from trying to do something yourself, with us correcting problems that you can't get round than you will from us writing the whole thing for you, so this is what we try to encourage.

Additionally, many of us have day jobs working with Oracle, and the incentive to do your job for you, without getting your pay isn't great.

Report message to a moderator

Re: Using Trigger to maintain history [message #424010 is a reply to message #423933] Tue, 29 September 2009 21:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
This is one of my favorite things, building history solutions for oracle schemas. I have done a significant amount of it and infact have a complete solution for both Transaction Based (rollback) History, and Bi-Temporal History.

History is one of those things that everyone thinks they can do but few people actually do well. To that end, you should take a look out on the internet and do some reading. In particular you should study documents on BI-TEMPORAL history. Not because you want to do it, but because it will help you understand better what to look for in a historical implementation.

All this said, the advice you have been given so far it spot on. You must use views and instead-of-triggers to make it work.

However, your solution as you described is not typical and contains flaws. For example, what happens when the same employee is updated twice in one day? What happens if the same employee is updated twice in the same second? How will you tell the difference between historical versions of your rows given these kinds of scenarios?

Also, ask yourself, WHY? Why do you want to even keep history anyway? Does your solution support what you want to do? What might you also want to do now that you have history data? Does your solution suport these things as well? How easy is it to use the historical data you have captured? Do you have to write new programs that use special time aware sql or have you been smart and created a way to re-use existing code without modification, to give you historical answers?

There are lots of questions and design choices and you should think about them and practice your examples before you go public with anything. History should not be taken lightly.

Good luck, Kevin

Report message to a moderator

Re: Using Trigger to maintain history [message #424020 is a reply to message #424010] Tue, 29 September 2009 22:56 Go to previous messageGo to next message
singhabhijitkumar
Messages: 7
Registered: February 2008
Location: india
Junior Member
Hey Kevin! How are you? I really liked your post on outer joins. It was very, very helpful.
I have request for you. In my previous project, which was using Oracle 9i on AIX server, we had data coming in XML file. It was a hierarchichal XML and the data was loaded into multiple relational tables.

The XML was first validated against its XSD and if there was an error, the details were reported and the load was stopped.

If the XML validated against its XSD then its data would be loaded into multiple relational tables.

All this was done using Java and canonical mapping between attributes and table columns was used. The beauty of the Java code was that if an additional attribute was added in XML, I simply needed to add the column in the corresponding table without any change in code.

I want to know if this can be done in an easy way in 10g or later version... if there is any presentation or anything which can be of help.

Report message to a moderator

Re: Using Trigger to maintain history [message #432777 is a reply to message #423958] Thu, 26 November 2009 04:29 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
Hi,

I tried to use following SQL query im my trigger but got an error with the message of mutating table:

ssid := :new.study_subject_id;
SELECT user_account.user_name , study_subject.label as label, study.unique_identifier as study_uid, subject.unique_identifier as subject_uid INTO user_name, label, study_uid, subject_uid FROM user_account RIGHT JOIN subject ON user_account.user_id = subject.owner_id RIGHT JOIN study_subject ON subject.subject_id = study_subject.subject_id RIGHT JOIN study ON study.study_id = study_subject.study_id WHERE study_subject.study_subject_id = ssid AND study.unique_identifier LIKE '%DOPPLER_%' order by study_subject.subject_id;


Any ideas?

Thanks!

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #432778 is a reply to message #432777] Thu, 26 November 2009 04:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Read Michel's posts more carefully.

You can do this, but only by creating a view on your table, and creating INSTEAD OF triggers to handle insets, updates and deletes.

Report message to a moderator

Re: Using Trigger to maintain history [message #432875 is a reply to message #423933] Thu, 26 November 2009 23:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Here is a quick and dirty example. This should pique your interest in the subject matter and show you that with some creativity, what you want can be achieved with this technology.

Once again:

Quote:
you must do some reading about instead-of-triggers

SQL> create table a (a number);

Table created.

SQL> alter table a add primary key (a);

Table altered.

SQL> 
SQL> create table b (a number,prior_a number,dlt_flag varchar2(1),op_date date);

Table created.

SQL> 
SQL> create or replace view va as select * from a;

View created.

SQL> 
SQL> create or replace trigger ioiud_va
  2  instead of insert or update or delete on va
  3  for each row
  4  begin null;
  5     if inserting then
  6        insert into a values (:new.a);
  7        insert into b (a,op_date) values (:new.a,sysdate);
  8     elsif updating then
  9        update a set a = :new.a where a = :old.a;
 10        insert into b (a,prior_a,op_date) values (:new.a,:old.a,sysdate);
 11     elsif deleting then
 12        delete from a where a = :old.a;
 13        insert into b (a,dlt_flag,op_date) values (:old.a,'Y',sysdate);
 14     end if;
 15  end;
 16  /

Trigger created.

SQL> show errors
No errors.
SQL> 
SQL> alter session set nls_date_format = 'dd-mon-rrrr hh24:mi:ss';

Session altered.

SQL> select * from va;

no rows selected

SQL> select * from b;

no rows selected

SQL> insert into va (a) values (1);

1 row created.

SQL> select * from va;

         A
----------
         1

SQL> select * from b;

         A    PRIOR_A D OP_DATE
---------- ---------- - --------------------
         1              27-nov-2009 00:26:18

SQL> update va set a = 2;

1 row updated.

SQL> select * from va;

         A
----------
         2

SQL> select * from b;

         A    PRIOR_A D OP_DATE
---------- ---------- - --------------------
         1              27-nov-2009 00:26:18
         2          1   27-nov-2009 00:26:25

SQL> delete from va;

1 row deleted.

SQL> select * from va;

no rows selected

SQL> select * from b;

         A    PRIOR_A D OP_DATE
---------- ---------- - --------------------
         1              27-nov-2009 00:26:18
         2          1   27-nov-2009 00:26:25
         2            Y 27-nov-2009 00:26:30

SQL> rollback;

Rollback complete.

SQL> select * from va;

no rows selected

SQL> select * from b;

no rows selected


The above is only for illustrative purposes. As I indicated in my prior reply, there are many details to keeping history correctly. To understand and implement a database wide solution will likely take you three months to figure out. That is how long it took me. But I now believe I have a very good solution. Time will tell.

Good luck, Kevin

Report message to a moderator

Re: Using Trigger to maintain history [message #432933 is a reply to message #432875] Fri, 27 November 2009 07:06 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
Hi,

I was trying a little bit and this is what i created:

 CREATE OR REPLACE FORCE VIEW "CLINICA"."VIEW1" ("STUDY_SUBJECT_ID", "LABEL", "SECONDARY_LABEL", "SUBJECT_ID", "STUDY_ID", "STATUS_ID", "ENROLLMENT_DATE", "DATE_CREATED", "DATE_UPDATED", "OWNER_ID", "UPDATE_ID", "OC_OID") AS 
  SELECT 
    "STUDY_SUBJECT_ID","LABEL","SECONDARY_LABEL","SUBJECT_ID","STUDY_ID","STATUS_ID","ENROLLMENT_DATE","DATE_CREATED","DATE_UPDATED","OWNER_ID","UPDATE_ID","OC_OID" 
FROM study_subject;
 

  CREATE OR REPLACE TRIGGER "CLINICA"."TRIALWORKLIST_UPDATE_TRIGGER" 
instead of insert on view1
declare
  user_name user_account.user_name% TYPE;
  label study_subject.label% TYPE;
  ssid study_subject.study_subject_id% TYPE;
  study_uid study.unique_identifier% TYPE;
  subject_uid subject.unique_identifier% TYPE;
  site_id varchar2(255);
  study_id varchar2(255);
  start_pos number;
BEGIN
  ssid := :new.study_subject_id;
  SELECT user_account.user_name , study_subject.label as label, study.unique_identifier as study_uid, subject.unique_identifier as subject_uid INTO user_name, label, study_uid, subject_uid FROM user_account RIGHT JOIN subject ON user_account.user_id = subject.owner_id RIGHT JOIN study_subject ON subject.subject_id = study_subject.subject_id RIGHT JOIN study ON study.study_id = study_subject.study_id WHERE study_subject.study_subject_id = ssid AND study.unique_identifier LIKE '%DOPPLER_%' order by study_subject.subject_id;
  start_pos := instr(study_uid,'_');
  study_id := substr(study_uid,0,start_pos);
  site_id := substr(study_uid,start_pos+1);
  INSERT INTO trialworklist(clinical_trial_site_id,clinical_trial_time_point_id,clinical_trial_protocol_id, patient_id, modalities_in_study, other_patient_ids) VALUES('" + site_id + "','BASELINE','" + study_uid + "','DOPPLER_" + label + "','US','"subject_uid"');
END;



/
ALTER TRIGGER "CLINICA"."TRIALWORKLIST_UPDATE_TRIGGER" ENABLE;

I got no errors about mutating anymore, but also my trialworklist was still empty...

Kind regards,

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #432935 is a reply to message #432933] Fri, 27 November 2009 07:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Did you insert into the new view, or into the original table?

Report message to a moderator

Re: Using Trigger to maintain history [message #432939 is a reply to message #432935] Fri, 27 November 2009 08:30 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
JRowbottom wrote on Fri, 27 November 2009 14:45
Did you insert into the new view, or into the original table?


If you mean insert in the trigger: into the new view

... CREATE OR REPLACE TRIGGER "CLINICA"."TRIALWORKLIST_UPDATE_TRIGGER" 
instead of insert on view1
...

Report message to a moderator

Re: Using Trigger to maintain history [message #433075 is a reply to message #432939] Sun, 29 November 2009 13:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
no, he means this:

create or replace view vxyz...

create or replace trigger instead of insert or update or delete on vxyz...

insert into xyz...

(opps, my insert was to the table instead of my new view)

Kevin

[Updated on: Sun, 29 November 2009 13:50]

Report message to a moderator

Re: Using Trigger to maintain history [message #433109 is a reply to message #433075] Mon, 30 November 2009 02:56 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
Hi,

This is my code in the block:

ssid := :new.study_subject_id;
  SELECT user_account.user_name , study_subject.label as label, study.unique_identifier as study_uid, subject.unique_identifier as subject_uid INTO user_name, label, study_uid, subject_uid FROM user_account RIGHT JOIN subject ON user_account.user_id = subject.owner_id RIGHT JOIN study_subject ON subject.subject_id = study_subject.subject_id RIGHT JOIN study ON study.study_id = study_subject.study_id WHERE study_subject.study_subject_id = ssid AND study.unique_identifier LIKE '%DOPPLER_%' order by study_subject.subject_id;
  start_pos := instr(study_uid,'_');
  study_id := substr(study_uid,0,start_pos);
  site_id := substr(study_uid,start_pos+1);
  INSERT INTO trialworklist(clinical_trial_site_id,clinical_trial_time_point_id,clinical_trial_protocol_id, patient_id, modalities_in_study, other_patient_ids) VALUES('" + site_id + "','BASELINE','" + study_uid + "','DOPPLER_" + label + "','US','"subject_uid"');


The only insert I do, is into another table (trialworklist)

Report message to a moderator

Re: Using Trigger to maintain history [message #433111 is a reply to message #433109] Mon, 30 November 2009 03:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
PLEASE try to follow what Kevin and I are saying.

You have a table STUDY_SUBJECT.

You have created a view, VIEW1 on this table, and created a set of INSTEAD OF triggers on this view.

When you complained that:Quote:

I got no errors about mutating anymore, but also my trialworklist was still empty...
, was this after an inset into VIEW1, or after an insert into STUDY_SUBJECT

In order to use the functionality in the new INSTEAD OF triggers, you must do your inserts into the view rather than into the table.

Report message to a moderator

Re: Using Trigger to maintain history [message #433112 is a reply to message #433109] Mon, 30 November 2009 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
And keep your lines in 80 characters.
Do you think that a line of 500 characters is readable?
Can you see the error in such long lines when you even don't see the end of it?

Regards
Michel

Report message to a moderator

Re: Using Trigger to maintain history [message #433113 is a reply to message #433111] Mon, 30 November 2009 03:25 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
JRowbottom wrote on Mon, 30 November 2009 10:08
In order to use the functionality in the new INSTEAD OF triggers, you must do your inserts into the view rather than into the table.


The idea about my code is not to do an insert on a table where the view was created on, but another table.

Kind regards,

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #433140 is a reply to message #433113] Mon, 30 November 2009 06:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And that is what the view you have created will do - every time you insert a record into the view, a record will be inserted into a different table to the one that the view was created on.

You said:Quote:
I got no errors about mutating anymore, but also my trialworklist was still empty...


Can you show us the code that you ran?

Report message to a moderator

Re: Using Trigger to maintain history [message #433182 is a reply to message #433140] Mon, 30 November 2009 09:31 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
Hi,


It is a web application for patient registration (OpenClinica).

Kind regards,

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #433283 is a reply to message #433182] Tue, 01 December 2009 03:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Have you checked the code by doing a manual insert into the view?

Report message to a moderator

Re: Using Trigger to maintain history [message #433286 is a reply to message #433283] Tue, 01 December 2009 04:12 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
Hi,

An insert of the view is paired with inserts on various tables (in the application) which will be selected for the insert on the new tabel.
For that reason is it not easy to do a manual check. Of course I can try to do a single select and insert to test the trigger itself...

Kind regards,

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #433289 is a reply to message #433286] Tue, 01 December 2009 04:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would seem to be a reasonable first step.....

Report message to a moderator

Re: Using Trigger to maintain history [message #433292 is a reply to message #433289] Tue, 01 December 2009 04:58 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
This results in the following: 

  CREATE OR REPLACE FORCE VIEW "CLINICA"."VIEW1" ("STUDY_SUBJECT_ID", "LABEL", "SECONDARY_LABEL", "SUBJECT_ID", "STUDY_ID", "STATUS_ID", "ENROLLMENT_DATE", "DATE_CREATED", "DATE_UPDATED", "OWNER_ID", "UPDATE_ID", "OC_OID") AS 
  SELECT 
    "STUDY_SUBJECT_ID","LABEL","SECONDARY_LABEL","SUBJECT_ID","STUDY_ID","STATUS_ID","ENROLLMENT_DATE","DATE_CREATED","DATE_UPDATED","OWNER_ID","UPDATE_ID","OC_OID" 
FROM study_subject;
 

  CREATE OR REPLACE TRIGGER "CLINICA"."TRIALWORKLIST_UPDATE_TRIGGER" 
instead of insert on view1
declare
  user_name user_account.user_name% TYPE;
  label study_subject.label% TYPE;
  ssid study_subject.study_subject_id% TYPE;
  study_uid study.unique_identifier% TYPE;
  subject_uid subject.unique_identifier% TYPE;
  site_id varchar2(255);
  study_id varchar2(255);
  start_pos number;
BEGIN
  ssid := :new.study_subject_id;
  --SELECT user_account.user_name , study_subject.label as label, study.unique_identifier as study_uid, subject.unique_identifier as subject_uid INTO user_name, label, study_uid, subject_uid FROM user_account RIGHT JOIN subject ON user_account.user_id = subject.owner_id RIGHT JOIN study_subject ON subject.subject_id = study_subject.subject_id RIGHT JOIN study ON study.study_id = study_subject.study_id 
  --WHERE study_subject.study_subject_id = ssid AND study.unique_identifier LIKE '%DOPPLER_%' order by study_subject.subject_id;
  SELECT study_subject.label INTO label from study_subject WHERE study_subject.study_subject_id = ssid;
  --start_pos := instr(study_uid,'_');
  --study_id := substr(study_uid,0,start_pos);
  --site_id := substr(study_uid,start_pos+1);
  --INSERT INTO trialworklist(clinical_trial_site_id,clinical_trial_time_point_id,clinical_trial_protocol_id, patient_id, modalities_in_study, other_patient_ids) VALUES('" + site_id + "','BASELINE','" + study_uid + "','DOPPLER_" + label + "','US','"subject_uid"');
  INSERT INTO trialworklist(patient_id) VALUES ('DOPPLER_" + label + "');
END;



/
ALTER TRIGGER "CLINICA"."TRIALWORKLIST_UPDATE_TRIGGER" ENABLE;


with the insert:

insert into view1 (study_subject_id,label) values ('62','5');


I got back an "no data error".

This gives me a sign that I haven't understood the complet concept... Sad

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #433294 is a reply to message #433292] Tue, 01 December 2009 05:05 Go to previous messageGo to next message
Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Gee, what an ugly code! Could you not remove all those unnecessary (commented) lines?

It appears that
SELECT study_subject.label INTO label from study_subject WHERE study_subject.study_subject_id = ssid;
returned nothing (which resulted in NO-DATA-FOUND error). So, either make sure it returns a single value, or create an exception handling section at the end of the trigger.

Report message to a moderator

Re: Using Trigger to maintain history [message #433295 is a reply to message #423933] Tue, 01 December 2009 05:05 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Posting the exact error message always helps

Report message to a moderator

Re: Using Trigger to maintain history [message #433297 is a reply to message #433295] Tue, 01 December 2009 05:14 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
Sorry about the comments!

(error below)

Kind regards,

Jan

error:

An error was encountered performing the requested operation:

ORA-01403: no data found
ORA-06512: at "CLINICA.TRIALWORKLIST_UPDATE_TRIGGER", line 14
ORA-04088: error during execution of trigger
'CLINICA.TRIALWORKLIST_UPDATE_TRIGGER'
01403. 00000- "no data found"
*Cause:
*Action:
Vendor code 1403Errot at Line 1

[Updated on: Tue, 01 December 2009 05:26]

Report message to a moderator

Re: Using Trigger to maintain history [message #433299 is a reply to message #433297] Tue, 01 December 2009 05:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As @Littlefoot pointed out, there's only one SELECT statement in the trigger:
SELECT study_subject.label INTO label from study_subject WHERE study_subject.study_subject_id = ssid;

So I guess that you have no record in the STUDY_SUBJECT table with a study_subject_id of 65.

Given that this was the record that you were trying to insert, it's absence is quite reasonable.

I no longer have any idea what it is that you are trying to do, and the code we're looking at is nothing to do with the original problem.
Can you explain the problem, as you currently understand it?

If all that you are trying to do is to insert a record into another table when a record is inserted into STUDY_SUBJECT then you don't need this View + Trigger solution - you could have done that with a normal trigger.

Report message to a moderator

Re: Using Trigger to maintain history [message #433301 is a reply to message #433299] Tue, 01 December 2009 05:55 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
JRowbottom wrote on Tue, 01 December 2009 12:39

If all that you are trying to do is to insert a record into another table when a record is inserted into STUDY_SUBJECT then you don't need this View + Trigger solution - you could have done that with a normal trigger.

This is how I came into this instead-of-trigger... I started with a normal trigger but got the "mutating table" error. For this reason they advised me to create a view...

Kind regards,

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #433305 is a reply to message #433301] Tue, 01 December 2009 06:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Looking back through the posts, you complain about a mutating table error from a trigger with this code in it:
ssid := :new.study_subject_id;
SELECT user_account.user_name 
      ,study_subject.label as label
      ,study.unique_identifier as study_uid
      ,subject.unique_identifier as subject_uid 
INTO   user_name
      ,label
      ,study_uid
      ,subject_uid
FROM  user_account 
      RIGHT JOIN subject ON user_account.user_id = subject.owner_id  
      RIGHT JOIN study_subject ON subject.subject_id = study_subject.subject_id 
      RIGHT JOIN study ON study.study_id = study_subject.study_id 
WHERE study_subject.study_subject_id = ssid 
AND   study.unique_identifier LIKE '%DOPPLER_%' 
order by study_subject.subject_id;


Now, assuming that SSID is a unique id, this query can return (at best) one record - the one you just inserted. If this is the case, you just want to use the :NEW notation to refer to the new fields, and add a check to see if :NEW.UNIQUE_IDENTIFIER LIKE '%DOPPLER_%'

If SSID isn't unique, then you'll probably have to go with the View / Trigger solution, and include an insert into STUDY_SUBJECT in the view before the SELECT.

Report message to a moderator

Re: Using Trigger to maintain history [message #433310 is a reply to message #433305] Tue, 01 December 2009 07:28 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
JRowbottom wrote on Tue, 01 December 2009 13:26

Now, assuming that SSID is a unique id, this query can return (at best) one record - the one you just inserted. If this is the case, you just want to use the :NEW notation to refer to the new fields, and add a check to see if :NEW.UNIQUE_IDENTIFIER LIKE '%DOPPLER_%'

I think the expression ":NEW.UNIQUE_IDENTIFIER" can only be used if unique_identifier is a column of study_subject table,no?

Regarding the ssid, it is supposed to be unique given by the application but in the database it is declared as a number variable.

Kind regards,


Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #433314 is a reply to message #433310] Tue, 01 December 2009 08:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Fair point about unique_identifier - but, as it isn't in the STUDY_SUBJECT table, you could execute a query to ckeck it's value without risking a mutating table error.

Either way, your reply fails to advance the solution.

Is the SSID column unique in STUDY_METHOD or not?

Report message to a moderator

Re: Using Trigger to maintain history [message #433321 is a reply to message #433314] Tue, 01 December 2009 09:48 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
I tried a normal trigger and got the following erro in my application log file:

Exception while executing dynamic statement, EntityDAO.execute: INSERT INTO study_subject
(LABEL, SUBJECT_ID, STUDY_ID, STATUS_ID,
DATE_CREATED, OWNER_ID, ENROLLMENT_DATE, SECONDARY_LABEL, OC_OID)
VALUES (?,?,?,?,sysdate,?,?,?,?): ORA-04091: table CLINICA.STUDY_SUBJECT is mutating, trigger/function may not see it
ORA-06512: at "CLINICA.TRIALWORKLIST_UPDATE_TRIGGER", line 12
ORA-04088: error during execution of trigger 'CLINICA.TRIALWORKLIST_UPDATE_TRIGGER'

12/01 16:14:50.901 [http-80-Processor25] INFO o.a.o.e.OpenClinicaException:61 - ----> Timestamp: 01/12/2009 16:14:50:901 Message Info: Could not create study subject.

Kind regards,

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #433322 is a reply to message #433321] Tue, 01 December 2009 09:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Of course it did - using a trigger to do an insert into the table that the trigger is on will always cause a mutating table error.

You seem to be having trouble grasping the concepts involved here, as well as problems actually answering the questions I'm asking you.

Option 1: A normal Before (or After) Insert Trigger on STUDY_SUBJECT

If you can do your processing without any DML on STUDY_SUBJECT then this is probably the best approach. You can refer to the values of the row that you're in the process of inserting by using the :NEW notation, and you can insert/update other tables.
The row that you were inserting will go into the table unless the trigger raises an unhandled exception, in which case the whole transaction will be rolled back.

Option 2: A View on STUDY_SUBJECT and an INSTEAD OF INSERT trigger

If your processing requires you to perform DML (including SELECTS) on the table that your trigger is on then this is one possible solution.
You will need to insert into the View instead of the table, and you will need to include an explicit INSERT statement to insert into STUDY_METHOD.

Report message to a moderator

Re: Using Trigger to maintain history [message #433323 is a reply to message #433322] Tue, 01 December 2009 10:05 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
Hi,

Thank you so much for being patient with me. You're completely right by saying that I'm grasping the concepts.

So I will take care of your advise!

Kind regards,

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #433398 is a reply to message #433322] Wed, 02 December 2009 02:23 Go to previous messageGo to next message
uzleuven
Messages: 16
Registered: November 2009
Junior Member
JRowbottom wrote on Tue, 01 December 2009 16:57

Option 2: A View on STUDY_SUBJECT and an INSTEAD OF INSERT trigger

If your processing requires you to perform DML (including SELECTS) on the table that your trigger is on then this is one possible solution..


Yes it does (study_subject is the table the trigger is on):

 SELECT user_account.user_name , study_subject.label as label, study.unique_identifier as study_uid, subject.unique_identifier as subject_uid INTO user_name, label, study_uid, subject_uid FROM user_account RIGHT JOIN subject ON user_account.user_id = subject.owner_id RIGHT JOIN study_subject ON subject.subject_id = study_subject.subject_id RIGHT JOIN study ON study.study_id = study_subject.study_id 
 WHERE study_subject.study_subject_id = ssid AND study.unique_identifier LIKE '%DOPPLER%' order by study_subject.subject_id;

Quote:
You will need to insert into the View instead of the table


Quote:
You will need to include an explicit INSERT statement to insert into STUDY_METHOD


Do I have to include this 'INSERT into study_subject' in my instead of insert trigger or in my view? I'm not sure how I have to create my view...

Kind regards,

Jan

Report message to a moderator

Re: Using Trigger to maintain history [message #433400 is a reply to message #433398] Wed, 02 December 2009 02:42 Go to previous messageGo to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
Do I have to include this 'INSERT into study_subject' in my instead of insert trigger or in my view?


Yes - inside an INSTEAD OF trigger you need to write code for any DML that you want to perform.

Report message to a moderator

Previous Topic: datatype for status like columns
Next Topic: Package Not Getting Compiled (merged 3)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Aug 24 12:51:57 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.