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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

QA trigger question

2884407Sep 16 2015 — edited Sep 16 2015

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_IDBUILDING_NAMEADDED_DATEADDED_BYCHANGED_DATECHANGED_BY
1main_building2015-09-01

GEOCL

2015-09-16ROGMO
2secondary_building2013-08-25GEOCL2014-12-05GEOCL

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:

IDUSERNAMEFULL_NAME
1GEOCLGeorge Clooney
2ROGMORoger 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

Comments

cormaco

If you install Virtualbox on your MacBook, you can use this appliance to get an Oracle database:
Developer Day - Hands-on Database Application Development (oracle.com)

Billy Verreynne

Mac was supported years ago with 10g. Think that even RAC support was planned. (https://docs.oracle.com/cd/B19306_01/install.102/b25286.pdf)
As I heard things went south between Oracle and Apple, and the support for Mac was discontinued.

L. Fernigrini

There are no Mac version for Oracle Databases, but as cormaco mentioned you can easily use VirtualBox to have a VM with Linux (you can use Oracle Linux) and install Oracle 18c XE on it.
Or you can use the VM he already pointed, it has Oracle 19c pre installed, and you can add 18c XE if you need (if you just want to test or do some Proof of Concept, then using the 19c should be OK, if you want to use it for commercial / production then you need either a license or to use the free XE edition).

1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 14 2015
Added on Sep 16 2015
7 comments
245 views