Home » SQL & PL/SQL » SQL & PL/SQL » execution of procedure in trigger
execution of procedure in trigger [message #219388] Wed, 14 February 2007 04:00 Go to next message
donind
Messages: 95
Registered: February 2007
Member
How to execute a procedure in before insert trigger?

any help really appreciated

Thanks in adv
Re: execution of procedure in trigger [message #219391 is a reply to message #219388] Wed, 14 February 2007 04:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Just include a call to the procedure in the trigger.
CREATE OR REPLACE TRIGGER bri_trg
BEFORE INSERT ON table FOR EACH ROW
BEGIN
  procedure;
END;
Re: execution of procedure in trigger [message #219395 is a reply to message #219391] Wed, 14 February 2007 04:24 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Here is the procedure. whenever we inserts a new row the procedure has to fire. But it is not doing so. But the new record is getting inserted into acknoeldgement_backup table.

create or replace trigger ack_bkp before insert on acknowledgement for each row
begin
insert into acknowledgement_backup (
Ack_Type,
Ack_ID,
Ack_Status,
Ack_Cost,
Ack_Error_Message,
Ack_Date
) values
(:new.Ack_Type,:new.Ack_ID,:new.Ack_Status,:new.Ack_Cost,:new.Ac
k_Error_Message,sysdate);
proc_Ack_Processing(); /* Here is the procedure I am calling*/
end;

Thanks in advance
Re: execution of procedure in trigger [message #219402 is a reply to message #219395] Wed, 14 February 2007 04:39 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

Quote:
But it is not doing so


is it giving any errors.what exactly does the procedure do?.and one more thing get rid of the "()" if the procedure does not have any arguments.


regards,
Re: execution of procedure in trigger [message #219407 is a reply to message #219402] Wed, 14 February 2007 04:44 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Its a simple procedure which I want to execute in before insert trigger.

CREATE OR REPLACE PROCEDURE proc_Ack_Processing AS
sqlstring1 LONG;
sqlstring2 LONG;
p_curr_cost_per_unit mst_item.curr_cost_per_unit%TYPE;
p_state_model mst_item.state_model%TYPE;
pragma autonomous_transaction;

BEGIN

FOR sqlstring1 IN (SELECT * FROM ACKNOWLEDGEMENT)
LOOP
IF (sqlstring1.ack_TYPE='I') THEN
SELECT state_model, curr_cost_per_unit into p_state_model, p_curr_cost_per_unit FROM mst_item WHERE item_id=TRIM(sqlstring1.ack_id);
IF sqlstring1.ack_cost!=p_curr_cost_per_unit OR p_curr_cost_per_unit IS null THEN
UPDATE mst_item SET curr_cost_per_unit=sqlstring1.ack_cost WHERE item_id=TRIM(sqlstring1.ack_id);
END IF;
FOR sqlstring2 IN (SELECT * FROM state_matrix)
LOOP
IF sqlstring2.ack_status=sqlstring1.ack_status AND sqlstring2.FROM_state=p_state_model AND sqlstring2.master_entity='MST_ITEM' THEN
UPDATE mst_item SET state_model=sqlstring2.to_state WHERE item_id=TRIM(sqlstring1.ack_id);
END IF;
END LOOP;
END IF;
commit
end loop;
end proc_Ack_Processing;
Re: execution of procedure in trigger [message #219412 is a reply to message #219407] Wed, 14 February 2007 05:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What happens when the trigger fires?
Do you get an error?
Does your database crash and refuse to restart?
Does the code cause the hordes of the living dead to rise and eat your brain?

Give us a clue!

Let me guess - when this trigger fires you get a Mutating Table error?

If so, that's because the procedure you're calling from the trigger does a SELECT on the table that the trigger is based on. There are standard ways to work round this problem.
Re: execution of procedure in trigger [message #219420 is a reply to message #219412] Wed, 14 February 2007 05:42 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hey I am just querrying the same table based on that I am updating other table.
Its not giving any error?

How to meet this requirement?

any help really helpfull.

Thanks in advance
Re: execution of procedure in trigger [message #219431 is a reply to message #219420] Wed, 14 February 2007 06:15 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
any help really helpfull.

Oddly, thats what I was thinking too. My psychic helmet is in the shop for repairs, so my ability to remotely diagnose your problems without you providing any information at all is a bit limited today.

What you've told us so far is that the trigger fires, the row gets inserted into Acknowledgment_Backup, and then the procedure doesn't fire, and no errors are raised.

I'm afraid that from the code you've posted, this is a contradictory set of statements:

Your trigger must be valid, or you'd get an error raised.
The procedure must be being called, as it is referenced in the trigger.
The procedure must be working, as you aren't getting an exception raised.

So, you need to go therough the procedure and add some debug code to it to find out why it isn't doing the things you think it should.
First, run this little set of statements:
CREATE TABLE log_table  (id    number(10)
                        ,text  varchar2(2000));
                        
CREATE SEQUENCE log_seq;

CREATE OR REPLACE procedure do_log (p_text  in  varchar2) as
  pragma autonomous_transaction;
begin
  insert into log_table (id,text)
  values
  (log_seq.nextval
  ,p_text);
  
  commit;
end do_log;
/


And then recreate your procedure like this: (I took the opportunity to remove a couple of unneeded parameters, and to move the commit to where it needs to go.
CREATE OR REPLACE PROCEDURE proc_Ack_Processing AS
p_curr_cost_per_unit mst_item.curr_cost_per_unit%TYPE;
p_state_model        mst_item.state_model%TYPE;

pragma autonomous_transaction;

BEGIN
do_log('Start');
FOR sqlstring1 IN (SELECT * FROM ACKNOWLEDGEMENT) LOOP
  do_log('Got ack_type='||sqlstring1.ack_type);
  IF (sqlstring1.ack_TYPE='I') THEN
    SELECT state_model, curr_cost_per_unit into p_state_model, p_curr_cost_per_unit FROM mst_item WHERE item_id=TRIM(sqlstring1.ack_id);
    do_log('sqlstring1.ack_cost='||sqlstring1.ack_cost);
    do_log('p_curr_cost_per_unit='||p_curr_cost_per_unit);
    do_log('sqlstring1.ack_id='||sqlstring1.ack_id);
    IF sqlstring1.ack_cost!=p_curr_cost_per_unit OR p_curr_cost_per_unit IS null THEN
      UPDATE mst_item SET curr_cost_per_unit=sqlstring1.ack_cost WHERE item_id=TRIM(sqlstring1.ack_id);
    END IF;
    FOR sqlstring2 IN (SELECT * FROM state_matrix) LOOP
      do_log('sqlstring2.ack_status='||sqlstring2.ack_status);
      do_log('sqlstring1.ack_status='||sqlstring1.ack_status);
      do_log('sqlstring2.FROM_state='||sqlstring2.FROM_state);
      do_log('p_state_model='||p_state_model);
      do_log('sqlstring2.master_entity='||sqlstring2.master_entity);
      IF sqlstring2.ack_status=sqlstring1.ack_status AND sqlstring2.FROM_state=p_state_model AND sqlstring2.master_entity='MST_ITEM' THEN
        UPDATE mst_item SET state_model=sqlstring2.to_state WHERE item_id=TRIM(sqlstring1.ack_id);
      END IF;
    END LOOP; 
  END IF;
END LOOP;
COMMIT;
do_log('Stop');
end proc_Ack_Processing; 


This will put lots of lovely debug info into the table Log_table that should let you know why the procedure isn't doing anything
Re: execution of procedure in trigger [message #219571 is a reply to message #219431] Thu, 15 February 2007 00:39 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi ,

I have seen the log table by executing the procedure from trigger. Its not entering the cursor for loop.
When I tried to execute the same procedure seperately its enetering the cusrsor looop and able to produce the desired results.

I think it's because of table acknowledgement I am using. The trigger is on the same table and also in procedure the cursor is on same table.

Just let me now how to solve this problem?

Thanks in advance
Re: execution of procedure in trigger [message #219616 is a reply to message #219571] Thu, 15 February 2007 03:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Ok, the problem that you;ve got here is that you're using features of Oracle (specifically autonomous transactions) that you don't understand, and thats causing problems.

An autonomous transaction is just that - it is a completely seperate transaction that runs in the same session as another transaction. The really important point about this is that as it is a seperate transaction, it CANNOT see any uncommited changes that your main transaction has made.

So, if transaction A inserts a row R into table T1, and this causes a trigger to fire on the table T1, and this trigger runs an autonomous transaction B that looks at table T1, then transaction B cannot see the row R.
Proof:
SQL> set serveroutput on size 10000
SQL> create table test_transaction (col_1  number);

Table created.
SQL> declare
  2  
  3    procedure test is
  4      pragma autonomous_transaction;
  5  
  6      v_count  number;
  7    begin
  8      select count(*)
  9      into v_count
 10      from test_transaction;
 11      dbms_output.put_line('Rows visible by autonomous transaction '||v_count);
 12      commit;
 13    end;
 14  begin
 15  
 16    test;
 17    insert into test_transaction values (2);
 18    test;
 19    commit;
 20    test;
 21  end;
 22  /
Rows visible by autonomous transaction 0
Rows visible by autonomous transaction 0
Rows visible by autonomous transaction 1

PL/SQL procedure successfully completed.


This looks like the root cause of your problem.
I'm guessing that you don't currently have any data in the table ACKNOWLEDGMENTS, and that you are inserting data into this table.
Without understanding exactly what you're trying to do, its difficult to work out how to fix this.
As I'm sure you know, you can refer to the newly created row with the :NEW prefix in the trigger itself - possibly you could pass the values needed into the procedure as parameters.

Re: execution of procedure in trigger [message #219624 is a reply to message #219616] Thu, 15 February 2007 03:42 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Here I am Having two tables Ack and ack1
whenever a row is inserted in table ack it should fire the trigger and insert the current record into ack1 table and also it should execute procedure. which is in trigger?

Ack
I item3 S 12 aaaa

ACK1
I item3 S 12 aaa

In procedure I am have cursor forloop on same table ack.

If you need anything let me know.

Thanks
Re: execution of procedure in trigger [message #219627 is a reply to message #219624] Thu, 15 February 2007 03:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm a little confused; your last post provided precisely no additional information - why did you bother typeing it?

The procedure is where your problems are, so that's where you need to be concentrating.
Does this procedure need to process all the rows in the ACKNOWLEDGEMENT table, or just the new row that has been added?

Is this procedure going to be called from anywhere else?
Re: execution of procedure in trigger [message #219630 is a reply to message #219624] Thu, 15 February 2007 03:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You followed somebody's advice to use an autonomous transaction to circumvent the mutating table error, didn't you?
That would be a very bad idea, as you found out the hard way..
Re: execution of procedure in trigger [message #219631 is a reply to message #219627] Thu, 15 February 2007 03:57 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
It is a table maintained by other group. Every time before getting a new records the table gets truncated and inserts only new records. But Ack1 table contains entire rows.
Re: execution of procedure in trigger [message #219644 is a reply to message #219631] Thu, 15 February 2007 04:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is there any chance of you actually answering the questions I ask?
Re: execution of procedure in trigger [message #219646 is a reply to message #219644] Thu, 15 February 2007 04:49 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
I'm a little confused; your last post provided precisely no additional information - why did you bother typeing it?

The procedure is where your problems are, so that's where you need to be concentrating.
1.Does this procedure need to process all the rows in the ACKNOWLEDGEMENT table, or just the new row that has been added?
Ans. It has to process all the rows in acknowledgement table. Before inserting any new rows in acknowledgement table. The table is truncated. So it contains only new rows. we have to process for that rows.

2.Is this procedure going to be called from anywhere else?
Ans.It is called from before insert trigger. When a new row is inserted it has to process for that row which is in acknowledgement table.

Any more querries.

Thanks in advance
Re: execution of procedure in trigger [message #219665 is a reply to message #219646] Thu, 15 February 2007 06:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you PLEASE try to provide clear, unambiguous answers.

Quote:
1.Does this procedure need to process all the rows in the ACKNOWLEDGEMENT table, or just the new row that has been added?
Ans. It has to process all the rows in acknowledgement table. Before inserting any new rows in acknowledgement table. The table is truncated. So it contains only new rows. we have to process for that rows.


This procedure is called once for each row that is added to the table. The way it is written at the moment, it will process all of the rows that are in the table for each row that is added. Is this correct, or does the procedure just need to process the row that has just been added?

Quote:

2.Is this procedure going to be called from anywhere else?
Ans.It is called from before insert trigger. When a new row is inserted it has to process for that row which is in acknowledgement table.


But, getting back to the actual question I asked: Is this procedure going to be called from anywhere else? ie is the trigger the only place that calls this procedure, or are there other places in your system that will call it?

One additional question - how are you adding the rows to this table? Is it a long list of individual INSERT statements, SQLLdr, external table, or something else?
Re: execution of procedure in trigger [message #219670 is a reply to message #219665] Thu, 15 February 2007 06:49 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi

1.This procedure is called once for each row that is added to the table. The way it is written at the moment. It is correct.

2.No, the procedure is not going to be called from anywhere else other than trigger.

3.Adding rows thru informatica tool (Workflow monitor)

Thanks in advance for your help.
Re: execution of procedure in trigger [message #219694 is a reply to message #219670] Thu, 15 February 2007 10:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I reckon you should be able to use this solution.
Split the trigger in two - Onr Before Insert Row level one to handle the inserts into the other table, and one After Insert Statement level one to do a single call to the procedure after all the inserts are done.

create or replace trigger ack_BRI before insert on acknowledgement for each row 
begin
insert into acknowledgement_backup (
Ack_Type,Ack_ID,Ack_Status,Ack_Cost,Ack_Error_Message,Ack_Date 
) values
(:new.Ack_Type,:new.Ack_ID,:new.Ack_Status,:new.Ack_Cost,:new.Ac
k_Error_Message,sysdate);
end;


CREATE OR REPLACE TRIGGER ack_AIS
after insert on acknowledgment
begin
  proc_Ack_Processing;
end;


CREATE OR REPLACE PROCEDURE proc_Ack_Processing AS
p_curr_cost_per_unit mst_item.curr_cost_per_unit%TYPE;
p_state_model        mst_item.state_model%TYPE;

BEGIN
FOR sqlstring1 IN (SELECT * FROM ACKNOWLEDGEMENT) LOOP
  IF (sqlstring1.ack_TYPE='I') THEN
    SELECT state_model, curr_cost_per_unit into p_state_model, p_curr_cost_per_unit FROM mst_item WHERE item_id=TRIM(sqlstring1.ack_id);
    IF sqlstring1.ack_cost!=p_curr_cost_per_unit OR p_curr_cost_per_unit IS null THEN
      UPDATE mst_item SET curr_cost_per_unit=sqlstring1.ack_cost WHERE item_id=TRIM(sqlstring1.ack_id);
    END IF;
    FOR sqlstring2 IN (SELECT * FROM state_matrix) LOOP
      IF sqlstring2.ack_status=sqlstring1.ack_status AND sqlstring2.FROM_state=p_state_model AND sqlstring2.master_entity='MST_ITEM' THEN
        UPDATE mst_item SET state_model=sqlstring2.to_state WHERE item_id=TRIM(sqlstring1.ack_id);
      END IF;
    END LOOP; 
  END IF;
END LOOP;
end proc_Ack_Processing; 

Re: execution of procedure in trigger [message #219726 is a reply to message #219670] Thu, 15 February 2007 12:55 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
donind wrote on Thu, 15 February 2007 06:49
1.This procedure is called once for each row that is added to the table. The way it is written at the moment. It is correct.

So processing should go like this:
  1. ACKNOWLEDGEMENT table starts out empty.
  2. You insert one row. The trigger calls the procedure, which processes one row.
  3. You insert a second row. The procedure processes two rows - the new one and the one that was already there.
  4. You insert a third row. The procedure processes three rows - the new one and the two that were already there.
That just seems rather strange business logic.
Re: execution of procedure in trigger [message #219776 is a reply to message #219726] Thu, 15 February 2007 23:29 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,


Yes , Acknowledgement table starts out empty.

1.If we insert one row in acknowledgement table.The procedure has to process fro that one row.

2.After processing the acknowledgement table gets truncated.

2.Again if we inserts 3 new rows. The procedure has to process for all these 3 rows.


This is how I need?

Thanks
Re: execution of procedure in trigger [message #219797 is a reply to message #219776] Fri, 16 February 2007 03:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Any news on whether my suggestion works?
Re: execution of procedure in trigger [message #219829 is a reply to message #219776] Fri, 16 February 2007 04:19 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
donind wrote on Thu, 15 February 2007 23:29
Yes , Acknowledgement table starts out empty.

1.If we insert one row in acknowledgement table.The procedure has to process fro that one row.

2.After processing the acknowledgement table gets truncated.

2.Again if we inserts 3 new rows. The procedure has to process for all these 3 rows.

This is how I need?

Maybe, but it's not how your version would work. That is how JRowbottom's works.

You had a row-level trigger (fired for each row - insert three rows, it fires three times) that each time it was called processed ALL rows in the ACKNOWLEDGEMENT table.

[Updated on: Fri, 16 February 2007 04:20]

Report message to a moderator

Re: execution of procedure in trigger [message #220232 is a reply to message #219388] Mon, 19 February 2007 13:15 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Why would you even bother with the procedure, simply do the following.

create or replace trigger ack_bkp before insert on acknowledgement for each row
declare
  sqlstring1 LONG;
  sqlstring2 LONG;
  p_curr_cost_per_unit mst_item.curr_cost_per_unit%TYPE;
  p_state_model mst_item.state_model%TYPE;

begin
  insert into acknowledgement_backup (Ack_Type,Ack_ID,Ack_Status,Ack_Cost,Ack_Error_Message,Ack_Date)
  values(:new.Ack_Type,:new.Ack_ID,:new.Ack_Status,:new.Ack_Cost,:new.Ack_Error_Message,sysdate);
  IF (:new.ack_TYPE='I') THEN
    SELECT state_model, curr_cost_per_unit into p_state_model, p_curr_cost_per_unit 
    FROM mst_item 
    WHERE item_id=TRIM(:new.ack_id);
    IF :new.ack_cost!=p_curr_cost_per_unit OR p_curr_cost_per_unit IS null THEN
      UPDATE mst_item SET curr_cost_per_unit=:new.ack_cost 
      WHERE item_id=TRIM(:new.ack_id);
    END IF;
    FOR sqlstring2 IN (SELECT * FROM state_matrix) loop
      IF sqlstring2.ack_status=:new.ack_status AND sqlstring2.FROM_state=p_state_model AND sqlstring2.master_entity='MST_ITEM' THEN
        UPDATE mst_item SET state_model=sqlstring2.to_state WHERE item_id=TRIM(:new.ack_id);
      END IF;
    END LOOP;
  END IF;
end;

[Updated on: Mon, 19 February 2007 13:15]

Report message to a moderator

Re: execution of procedure in trigger [message #220289 is a reply to message #220232] Tue, 20 February 2007 01:29 Go to previous message
donind
Messages: 95
Registered: February 2007
Member
Hey Thanks a lot. Now its working fine. Really it worked for my scenario.

Thanks for ur help
Previous Topic: data in sample table can't be inserted into db- error :ORA-00054
Next Topic: help needed
Goto Forum:
  


Current Time: Sat Dec 10 05:07:16 CST 2016

Total time taken to generate the page: 0.10137 seconds