Need help on wrting efficient triggers.

user10151069 wrote:

 

I have a task to create application trigger and need help to write efficient triggers with exceptions.

 

This is my uncompleted trigger. Version Oracle 12c on windows 10 64bits.

 

Reason: On insert of record in a table T1 need trigger to fire and do an update statement on the inserted record with exception. C_name column data has to be updated on Test_lead table with which C_name loggedin.

 

CREATE OR REPLACE TRIGGER TESTLEAD_AfterInsert
AFTER INSERT
ON scott.TEST_LEAD
FOR EACH ROW

 

DECLARE

v_cname varchar2(100);

 

BEGIN

-- Find username of person performing the INSERT into the table
SELECT SYS_CONTEXT ('userenv','session_user') INTO v_cname FROM dual;

-- Update record into same table
UPDATE scott.TEST_LEAD set c_name = v_cname;
END;

/

 

would the above work. Need help on writing an exception on failure and tips to improve the above code.

is application 3-tier?

Consider replacing SELECT with statement below

V_CNAME := SYS_CONTEXT ('userenv','session_user');

As written EVERY row in SCOTT.TEST_LEAD table will be changed.

I suspect that not every row should be changed.

PLEASE read & follow content of URL below

How do I ask a question on the forums?

Why are NONE of your threads marked as ANSWERED?

user10151069

John Thorton wrote:

 

 

Consider replacing SELECT with statement below

 

V_CNAME := SYS_CONTEXT ('userenv','session_user');

Even that is too much; there is no need for the V_CNAME variable (and therefore no need for a DECLARATIONS section at all). The UPDATE can simply be

UPDATE ...    SET ... = SYS_CONTEXT(...)

You will have to explain more about the "exception" part. What specific exception or exceptions do you expect and must handle?

As for the "update", the simplest way to achieve what you seem to want is a BEFORE insert trigger. (Best way would be to avoid triggers altogether, but that's another story.)

Something like this:

create or replace trigger <trigger_name>

  before insert

  on <table_name>

  for each row

begin

  :new.c_name :=  sys_context ('userenv','session_user');

end;

/

As Mathguy said, if you want to set the value of a column for the triggering row then just use :new.column_name = value. And don't bother with the silly select from dual.

"would the above work. Need help on writing an exception on failure and tips to improve the above code."

What exceptions are you expecting? You should only be handling expected exceptions that you can legitimately handle - we will have no idea what your business requirements are so we can't really answer.

If I were you though, I'd think that if this trigger is to be useful then it must be firing for every insert, if it can't succeed then it should raise an exception to the insert statement with the error so that a code fix can be made. This requires zero change to the code that Mathguy shared. I would be alarmed if you'd want to swallow an error and pretend everything was okay to the insert statement.

This statement:

UPDATE scott.TEST_LEAD set c_name = v_cname;

.. has no predicate - which means every single row in the table will be updated.

Are triggers "efficient"? I think not. I'd generally opt for using procedures that perform the DML as needed and avoid triggers as much as possible.

Some insert select or massive update or merge would perform much worse if there are triggers and I think it's best to do all that is to be done in few SQL statements avoiding lots of context switching between SQL and PL/SQL.

BEDE wrote:

 

Are triggers "efficient"? I think not. I'd generally opt for using procedures that perform the DML as needed and avoid triggers as much as possible.

Some insert select or massive update or merge would perform much worse if there are triggers and I think it's best to do all that is to be done in few SQL statements avoiding lots of context switching between SQL and PL/SQL.

We have a trigger on every one of our tables for an application which is used by about 15K users daily. Dropping the triggers would result in no difference to response time for a user call. A few context switches for a user call is nothing. It’s Okay, no, it’s 100% normal to do context switches just don’t do it millions of times for a user call. If your user call updates one or two rows (and let’s face it, that’s probaby all a non-batch process should be doing) then getting worked up about context switches is quite silly.

Triggers have a nasty reputation due to abusive code (like OP generously demoed), but in reality, they’re perfectly fine if you write them well. The only noticeable impact would be during bulk processing when you’re really hitting it hard, and in those controlled circumstances its very normal to be doing things like do the work of the trigger yourself and not letting it fire (which is incredibly easy when you enable EBR and have your triggers on your editioning views only). Context switching is not your only worry for bulk processes, the existence of a trigger that must be fired will switch your redo writing to single row mode, which could be very bad.

But again, in OLTP perfectly fine and should not be feared unless you’ve written some silly logic.

By all means go down the route of only allowing DML to a table via known procedures, but don’t write  restrictive procedures that only handle very simple operations (Table API - TAPI) you should be writing procedures that can fully execute your transactions (transactional API - XAPI), otherwise youre just doing the same thing you tried to not do - row by row calls to procedures!

So, as I thought in OLAP, triggers should not be used. In OLTP they may be used...

Thanks.

user10151069 wrote:

 

I have a task to create application trigger and need help to write efficient triggers with exceptions.

 

This is my uncompleted trigger. Version Oracle 12c on windows 10 64bits.

 

Reason: On insert of record in a table T1 need trigger to fire and do an update statement on the inserted record with exception. C_name column data has to be updated on Test_lead table with which C_name loggedin.

 

CREATE OR REPLACE TRIGGER TESTLEAD_AfterInsert
AFTER INSERT
ON scott.TEST_LEAD
FOR EACH ROW

 

DECLARE

v_cname varchar2(100);

 

BEGIN

-- Find username of person performing the INSERT into the table
SELECT SYS_CONTEXT ('userenv','session_user') INTO v_cname FROM dual;

-- Update record into same table
UPDATE scott.TEST_LEAD set c_name = v_cname;
END;

/

 

would the above work. Need help on writing an exception on failure and tips to improve the above code.

Looks like your intent is to simply force the username into the column c_name.  As already pointed out, your UPDATE is not qualified, so you will be making that update to every row in the table, every time.  But beyond that you don't even need (or want) an update statement.  Trying to perform an update on the row firing the trigger should generate a mutating row error.  No, what you want is to simply assign a new value to to row as it is being inserted.

CREATE OR REPLACE TRIGGER TESTLEAD_BeforeInsert

BEFORE INSERT

ON scott.TEST_LEAD

FOR EACH ROW

BEGIN

  :new.c_name := SYS_CONTEXT ('userenv','session_user')

END;

It's considered RUDE to not mark your thread "answered" if you truly did get an answer.  It's like not saying "Thank you".

Though you're new and have relatively few threads, there all unanswered.

user10151069 wrote:

 

Thanks everyone for your valuable tips and suggestions.

 

Our OLTP application cannot insert user login information and initially we thought that we can insert a dummy value and then the trigger with update statement shall do the rest. I wasn't willing to add trigger as the team suggested.

Now that I got the idea from Sven W. I think this is doable.

 

Insert into scott.table (C1,C2,C3) values (1,2,'sys_context('userenv','session_user')');

If the above is doable then I don't need a trigger.

if application is 3-tier, above does NOT capture end-user name.

Hi,

user10151069 wrote:

 

...

Now that I got the idea from Sven W. I think this is doable.

 

Insert into scott.table (C1,C2,C3) values (1,2,'sys_context('userenv','session_user')');

If the above is doable then I don't need a trigger.

In any event, you don't want the outer quotes.

Insert into scott.table (C1, C2, C3) values (1, 2, sys_context ('userenv', 'session_user'));