0

I have an error: 'ORA-04092: cannot COMMIT in a trigger' when trying to execute ddl command in one simple oracle after update trigger. Trigger needs to create public database link after one field in column is updated. Here is the source:

create or replace
TRIGGER CreateLinkTrigger
after UPDATE of Year ON tableInit 
for each row
DECLARE
    add_link VARCHAR2(200);
BEGIN
IF :new.year = '2014'
then
    add_link := q'{create public database link p2014 connect to test14 identified by temp using 'ora'}';
    execute immediate add_link;
END IF;
END;

So, as You can see i need to create new public database link after new year has been activated. So when i try to update table 'tableInit' with year value of '2014' i get ORA-04092 error. Is there any way to avoid this error, or another solution for this? Thanks...

Ivan Tadic
  • 143
  • 2
  • 9
  • 1
    why do want to Create DBLINK inside Trigger first place?. – simplify_life Dec 11 '13 at 11:37
  • Because at Newyear's eve, when my clients finalize all their transactions in application i have new State Law with new taxes and many other changes coming on force at exactly 01.01.2014 00:00, so i need to redirect them fast to a new schema with already defined new laws and taxes exactly on that time :/. The problem is that there are over 20 clients who need this, so changing taxes and everything else on application, one client by one on newYears eve is not an option for me... :) – Ivan Tadic Dec 11 '13 at 11:44
  • So can't you have a job scheduled to run at that time to create the link? What causes the table update that you're trying to trigger the link creation from - is that also happening at a fixed time, or as part of a process that could initiate the link change as part of its flow rather than relying on the trigger? e.g. the update and dynamic create in a procedure? (Also, if the new schema is in the same DB then a synonym would be more appropriate than a link; but even a separate schema seems an odd design). – Alex Poole Dec 11 '13 at 11:47
  • Table is not updated at a fixed time, but there is a procedure triggered by a button that my client is using when he is done with his work on 31.12.2013. That procedure is updating year column with 2014 and that causes tableInit to update to '2014'. So then i need to create a new link to a new schema, with new taxes and everything defined, so that my client can work with new data given by that schema. New schema is not always in the same database... :/ – Ivan Tadic Dec 11 '13 at 12:07
  • 1
    So why not create the link in the same procedure, rather than trying to do something complicated with a trigger? The only downside I can see is that the DDL will still issue an implicit commit, which you might not want in your procedure; but if that's an issue your procedure could submit a job to do it asynchronously instead? – Alex Poole Dec 11 '13 at 12:21
  • The new link is not permanent, it's only temporarily. I will change everything back to as it was few days after Newyear's eve. That's is the reason why i'am not trying to include this in my stored procedure, because i will delete the trigger and cahnge the link after few days... BTW Thanks Very Much Alex and simply_life for your effort.. :) My only goal here is to have a day and night off on NewYears eve as you can see... I'am trying to prepare everything as much as i can so i don't end up alone on my computer at midnight... :D – Ivan Tadic Dec 11 '13 at 12:22

1 Answers1

2

Creating a database link on the fly seems like an unusual thing to do; your schema should generally be static and stable. However, if you must, it would be simpler to wrap the update and the link in a procedure, or just issue two statements - presumably whatever performs the update is fairly controlled anyway, otherwise you'd have to deal with multiple people triggering this multiple times, which would be even more of a mess.

You can probably make this work by adding PRAGMA autonomous_transaction; to your trigger, as demonstrated for a similar issue (creating a view rather than a link) in this answer, but I'm not in a position to test that at the moment.

create or replace
TRIGGER CreateLinkTrigger
after UPDATE of Year ON tableInit 
for each row
DECLARE
    add_link VARCHAR2(200);
    PRAGMA autonomous_transaction;
BEGIN
    ...

You could also make the trigger submit an asynchronous job to perform the DDL, as described in this answer, and there's more of an example in this answer, where you'd change the job's anonymous block to do your execute immediate.

It would probably be better to just create the links for the next few years in advance during a maintenance window, or on a schedule, or from a procedure; rather than trying to associate a schema change to a data change.

Community
  • 1
  • 1
Alex Poole
  • 183,384
  • 11
  • 179
  • 318