Hello
I have a trigger that adds the values of the username and the date to a table whenever the table gets INSERT/UPDATE statements. The trigger is as follows:
create or replace TRIGGER BUILDING_QA
BEFORE INSERT OR UPDATE ON BUILDING
FOR EACH ROW
DECLARE
toDayDate DATE := sysdate();
BEGIN
IF INSERTING THEN
:new."ADDED_DATE" := toDayDate;
:new."ADDED_BY" := USER;
ELSE
:new."ADDED_DATE" := :old."ADDED_DATE";
:new."ADDED_BY" := :old."ADDED_BY";
END IF;
:new."CHANGED_BY" := USER;
:new."CHANGED_DATE" := toDayDate;
END;
The BUILDING table looks like this:
| BUILDING_ID | BUILDING_NAME | ADDED_DATE | ADDED_BY | CHANGED_DATE | CHANGED_BY |
|---|
| 1 | main_building | 2015-09-01 | GEOCL | 2015-09-16 | ROGMO |
| 2 | secondary_building | 2013-08-25 | GEOCL | 2014-12-05 | GEOCL |
I want to modify the trigger so that it adds the full name of the user to the buildings tables. The information of each user and its full name is located in another table on another schema. The table looks like the following:
| ID | USERNAME | FULL_NAME |
|---|
| 1 | GEOCL | George Clooney |
| 2 | ROGMO | Roger Moore |
The values of the full_name would be added to a new column in the BUILDING table (for example: ADDED_BY_FULL_NAME).
What is the best way to accomplish this?
Thanks in advanced