Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 442 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
QA trigger question

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
Answers
-
>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.
-
Hello,
write a function that returns the full name when you pass the USER
:new.added_by_full_name := name_function(USER);
Regards
Marcus
-
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.
-
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
-
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.
-
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.4It'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
-
.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.