Forum Stats

  • 3,838,105 Users
  • 2,262,332 Discussions
  • 7,900,506 Comments

Discussions

Categories

QA trigger question

2884407
2884407 Member Posts: 3
edited Sep 16, 2015 5:35PM in SQL & PL/SQL

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

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Unknown
    Unknown
    edited Sep 16, 2015 10:30AM

    >What is the best way to accomplish this?

    The best way is to hope that somebody here does your job for you & rewrite the trigger to include the new & desired functionality.

    Alternatively you could demonstrate your PL/SQL coding abilities & modify the trigger yourself.

    · Share on TwitterShare on Facebook
  • Marwim
    Marwim Member Posts: 3,651 Gold Trophy
    edited Sep 16, 2015 10:31AM

    Hello,

    write a function that returns the full name when you pass the USER

    :new.added_by_full_name := name_function(USER);

    Regards

    Marcus

    · Share on TwitterShare on Facebook
  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy
    edited Sep 16, 2015 10:36AM

    What have you tried so far?

    Mind you, it doesn't sound like a very sensible thing to do. If you're already storing a key in your table from which you can look up the full name elsewhere, why denormalise your database by adding the full name column? There will be (admittedly minor) performance implications involved in doing this lookup whenever a row is inserted, and what will you do when a user changes their name?

    Consider creating a view instead.

    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Sep 16, 2015 12:11PM

    Change your code

    :new."ADDED_BY" := USER;

    To

    :new."ADDED_BY" := coalesce( v('APP_USER'), USER );

    This way, the trigger will work when you create an APEX front end for your application.

    The conversion to full name is simple.

    MK

    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Sep 16, 2015 12:50PM 
     I have a trigger that adds the values of the username and the date to a table whenever the table gets INSERT/UPDATE statements.

    Why? Why do you want to clutter up your tables with two mostly USELESS columns? When was the last time the info was actually used and was found to be reliable for ANY purpose?

    Often table data is modified by batch processes and not real users.

    Those attributes are at the row level and NOT the column level. So if I change the salary and you change an insignificant column one second after my change no one will EVER know that I touched the database.

    You should use AUDITING if you need to know who is messing with your tables. if you REALLY need to track things at the row level then create a log table and have your trigger log that info: table_name, PK of row, user/date making the change.

    But please don't clutter up your tables with useless junk like that.

     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:

What is the best way to accomplish this?

    The BEST WAY is: DON'T DO THAT!

    Now you want to actually DENORMALIZE the data. Not only does that violate fundamental principles of data modeling but it will now affect the performance of the system.

    Again - assuming you actually NEED a full name then link to that 'other table in another schema' when you do the query.

    That is what relational databases are designed for. That is why you should NOT DENORMALIZE the data. What happens when the persons name changes to correct a mispelling? Now the info no longer matches the data in that 'other' table.

    Please - abandon that idea. It is TOTALLY unnecessary and does nothing but impact the performance of your system.

    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited Sep 16, 2015 1:24PM
    rp0428 wrote:
    You should use AUDITING if you need to know who is messing with your tables. if you REALLY need to track things at the row level then create a log table and have your trigger log that info: table_name, PK of row, user/date making the change.
    If you want to do auditing, you use Flashback Data Archive [FDA].
    Unfortunatly, FDA has a ~1sec resolution.  The "updated_on" column was still needed for my app.
    Only on 12c+ can you automagically record additional information (ie sys_context )
    Even then, it will be tough to ensure that all applications will provide the required information for auditing purpose.
    The "updated by" column may still be needed.
    FDA is included with all editions starting with 11.2.0.4
    It's part of an EE Addon ($$) for 11.1.0.1 - 11.2.0.3 (Total Recal for 11.1.0.x, Adv. Compression for 11.2.0.1-11.2.0.3)
    MK
    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    edited Sep 16, 2015 5:35PM
    .
    The "updated by" column may still be needed.
    Your mileage may vary but I haven't worked with/for a single org in 25+ years where those 4 columns (created_by, created_date, modified_by, modified_date) were EVER useful for troubleshooting a problem.

    They aren't sufficient or accurate enough for 1) federal auditing purposes 2) Sarbanes-Oxley or 3) HIPAA. And IMHO the data isn't used often enough to warrant corrupting even one table (let alone MOST tables) with 4 such columns.

    They also can take considerable space. Each date column is 7 bytes just for data. Each 'name' column is probably from 5-20 bytes. That means a MINIMUM of 24 characters per row for data that may NEVER be modified or even used except in rare instances.

    The purported need and use of those columns is NOT relational in nature but rather to serve an auditing purpose.

    As such a serial logging solution is much more appropriate and performant and interferes far less with the normal use of the table(s) and data.

    Until someone can show me a solution using those four columns that performs as well as a simple logging solution I will stick to my opinion that using such columns is, for all intents and purposes, a BUG in the application and data model.

    · Share on TwitterShare on Facebook
This discussion has been closed.