-
1. Re: Need help on wrting efficient triggers.
John Thorton Jul 11, 2018 1:37 AM (in response to user10151069)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 ROWDECLARE
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?
-
2. Re: Need help on wrting efficient triggers.
mathguy Jul 11, 2018 1:45 AM (in response to John Thorton)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(...)
-
3. Re: Need help on wrting efficient triggers.
mathguy Jul 11, 2018 1:54 AM (in response to user10151069)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;
/
-
4. Re: Need help on wrting efficient triggers.
AndrewSayer Jul 11, 2018 5:18 AM (in response to user10151069)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.
-
5. Re: Need help on wrting efficient triggers.
Billy~Verreynne Jul 11, 2018 5:54 AM (in response to user10151069)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.
-
6. Re: Need help on wrting efficient triggers.
BEDE Jul 11, 2018 5:56 AM (in response to AndrewSayer)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.
-
7. Re: Need help on wrting efficient triggers.
AndrewSayer Jul 11, 2018 7:05 AM (in response to BEDE)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!
-
8. Re: Need help on wrting efficient triggers.
Sven W. Jul 11, 2018 10:16 AM (in response to user10151069)1 person found this helpfulSince you are on a 12c database you can replace your trigger with a column that uses DEFAULT values.
I wrote an article some time ago that covers most of the performance considerations. In essense a trigger based solution will be slower when A LOT of inserts are done. For typical applications you will not notice a difference (allthough there will be one).
https://svenweller.wordpress.com/2016/02/24/sequence-and-audit-columns-with-apex-5-and-12c/
Here is the optimized example table. Since you are probably not using apex, we can remove some of the code that I had in the blog post.
create
sequence
swe_demo_seq cache 20;
create
table
swe_demo
(id number
default
swe_demo_seq.nextval
primary
key
,col1 number
,col2 varchar2(30)
,inserted_date
date
default
sysdate
not
null
,inserted_from varchar2(128)
default
sys_context(
'userenv'
,
'session_user'
)
not
null
);
-
9. Re: Need help on wrting efficient triggers.
BEDE Jul 11, 2018 10:15 AM (in response to AndrewSayer)So, as I thought in OLAP, triggers should not be used. In OLTP they may be used...
Thanks.
-
10. Re: Need help on wrting efficient triggers.
EdStevens Jul 11, 2018 11:49 AM (in response to user10151069)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 ROWDECLARE
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;
-
11. Re: Need help on wrting efficient triggers.
jaramill Jul 11, 2018 1:46 PM (in response to user10151069) -
12. Re: Need help on wrting efficient triggers.
user10151069 Jul 11, 2018 1:50 PM (in response to user10151069)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.
-
13. Re: Need help on wrting efficient triggers.
John Thorton Jul 11, 2018 1:53 PM (in response to user10151069)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.
-
14. Re: Need help on wrting efficient triggers.
Frank Kulash Jul 11, 2018 2:00 PM (in response to user10151069)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'
)