QA trigger question

2884407

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

Unknown

>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.

Marwim

Hello,

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

:new.added_by_full_name := name_function(USER);

Regards

Marcus

Chris Hunt

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.

Mike Kutz

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

Unknown

 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.

Mike Kutz

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

Unknown

.

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.