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.