Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need help on wrting efficient triggers.

user10151069Jul 10 2018 — edited Jul 18 2018

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.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 15 2018
Added on Jul 10 2018
30 comments
1,477 views