Home » SQL & PL/SQL » SQL & PL/SQL » Trigger (Oracle 10g)
Trigger [message #339725] Fri, 08 August 2008 04:18 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Dear All,
Am havinf a trigger like below
CREATE OR REPLACE TRIGGER trg_crm AFTER INSERT ON TABLE_NAME
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

UPDATE TABLE_NAME SET SEQ_ID=101 WHERE seq_id IS NULL;
END;

While inserting on the table it should give mutating trigger,but am not getting the same.Why?Kindly suggets.
Re: Trigger [message #339735 is a reply to message #339725] Fri, 08 August 2008 05:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because of
Quote:
PRAGMA AUTONOMOUS_TRANSACTION


Regards
Michel
Re: Trigger [message #339746 is a reply to message #339735] Fri, 08 August 2008 06:12 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I strongly suspect that your code doesn't do what you expect it does.
If you are relying on this trigger to ensure that if you insert a row with a null value in SEQ_ID then this will be replaced with 101 then you are in for some disappointment.

Example:
SQL> create table test_0065(col_1 number,col_2 number);

Table created.

SQL> 
SQL> create or replace Trigger test_0065_trg after insert on test_0065 for each row
  2  declare
  3    pragma autonomous_transaction;
  4  begin
  5    update test_0065 set col_2 = -1 where col_2 is null;
  6    commit;
  7  end;
  8  /

Trigger created.

SQL> 
SQL> insert into test_0065 values (1,null);

1 row created.

SQL> 
SQL> select * from test_0065;

     COL_1      COL_2
---------- ----------
         1

As you can see, the row that was inserted is untouched by the trigger.

If what I described is what you want, replace your trigger with this:
 CREATE OR REPLACE TRIGGER trg_crm BEFORE INSERT ON TABLE_NAME
FOR EACH ROW
WHEN (new.seq_id is null)
BEGIN
  :new.SEQ_ID := 101;
END;
Previous Topic: using case and group by [merged]
Next Topic: Event Count
Goto Forum:
  


Current Time: Thu Dec 08 22:00:21 CST 2016

Total time taken to generate the page: 0.12362 seconds