Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 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
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Simple Trigger Issue?

I am attempting to create a trigger that will insert values into an audit table for approval by an admin user. The trigger will insert new values that are added into a consultant table into this audit table.
<span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">DROP TABLE </span><span class="typ" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #2b91af;">MyAuditTable</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>CREATE TABLE </span><span class="typ" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #2b91af;">MyAuditTable</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/> audit_id INTEGER NOT NULL</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/> new_name VARCHAR2 </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">30</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/> new_postcode VARCHAR2 </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">20</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/> status VARCHAR2 </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="lit" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">15</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">),</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/> CONSTRAINT pk_MyAuditTable PRIMARY KEY </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> audit_id </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">);</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>drop trigger </span><span class="typ" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #2b91af;">MyTrigger</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>create trigger </span><span class="typ" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #2b91af;">MyTrigger</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/>after insert on my_consultant_table<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">for</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> each row<br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">begin</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/> insert </span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">into</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="typ" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #2b91af;">MyAuditTable</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> values </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">(</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/> </span><span class="typ" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #2b91af;">MySeq</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">nextval</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">new</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">con_name</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/> </span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">:</span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">new</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">.</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">con_postcode</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">,</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"> <br/> </span><span class="str" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #7d2727;">'Pending'</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">)</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="kwd" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #101094;">end</span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">;</span><span class="pln" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;"><br/></span><span class="pun" style="font-style: inherit; font-weight: inherit; font-family: inherit; color: #303336;">/</span>
The trigger has no errors but does not insert data into my audit table.
Ideally looking to create a second trigger so that once 'pending' has been changed to something along the lines of 'authorised' the changes are made.
Can anyone provide insight as to how I can overcome this so that the data is correctly inserted into my audit table and not instantly updated into the consultant table?
Many Thanks!
Best Answer
-
JazzyB wrote:Thanks for the response,It was that semicolon and after INSERT was changed to after UPDATE as I'm attempting to demo on existing dataA silly mistake but now works.I am attempting to only let these changes pass if the status in the audit table is changed to 'Authorised'Is this possible by using an if statement within the trigger?Or does a seperate BEFORE UPDATE trigger need to be made for my consultant table?Thanks again
I don't see how this would work, the update must happen in order for the trigger to fire...
If you want to add an authorization step to updates then you might want to consider:
Create a staging table with the same columns and primary key, add additional column(s) for authorization status (and authorized by person etc)
Create a view on this table which doesn't include authorization columns and also filters out rows that have been authorized. Grant inserts,deletes and select on this view by the users that you don't wish to update the original table directly but do wish to be able to get their changes authorized.
Grant updates to the staging table to users that can authorize, you could use a trigger here if you wanted to make sure that they set an authorized by person to themselves.
Write a procedure that is owned by the owner of the main table that merges into the main table selecting only authorized changes from the staging table, and then deletes the staging rows that were used.
-edit
Or instead of deleting rows from the staging table you can archive them off into an audit table
Answers
-
A few thoughts...
Whether intentionally or by mistake, you marked your question as Assumed Answered (even before a single reply was posted). This may suggest that you already found the answer? Please clarify.
Then: you say the trigger works fine but does not insert data. I don't know your definition of "works fine"; for most people, if a trigger (or anything else) doesn't do what it is supposed to do, then it doesn't "work fine". What did you mean by "it works fine"?
Then: if you get an error saying that a column name from the CONSULTANT table is an "invalid identifier", what makes you think the error has anything with the trigger?
-
A blind insert without naming the columns is an accident waiting to happen.
-
Hi mate, accidentally hit the assumed answered button.
I meant to say the trigger should technically work as there are no errors (ran into a few previously), but simply doesn't insert into the audit table.
The invalid identifier seemed to vanish, and looked like a glitch
Really unsure as to why it won't insert into this table??
-
POST the DDL of the "consultant_table". There's no such thing as a "glitch". Either it's a true bug in Oracle that you found or it's a bug in your code.
-
Thanks for the reply mate
Yeah definitely, I'm just trying to demonstrate audit tables.
Revised my question a little due to an update
-
I was in object browser and that's what I was shown, but now just says no data found eventhough code hasn't been changed. That's what was meant by "glitch".
Is the code below sufficient?
CREATE TABLE "MY_CONSULTANT_TABLE"
( "CONSULTANT_ID" NUMBER(*,0) NOT NULL ENABLE,
"CON_NAME" VARCHAR2(25) NOT NULL ENABLE,
"CON_POSTCODE" VARCHAR2(10) NOT NULL ENABLE,
"HIGHEST_QUAL" NUMBER(*,0) NOT NULL ENABLE,
"CON_REGISTERED" DATE,
"PERMISSIONS" VARCHAR2(20),
CONSTRAINT "PK_MY_CONSULTANT_TABLE" PRIMARY KEY ("CONSULTANT_ID")
USING INDEX ENABLE
)
/
-
That is what you should always post, (DDLs of your objects, and DMLs of your data).
No data found Oracle error means several things but the main one is your query returned no rows or a table is empty or your query has nothing at all to return.
-
I am unable to reproduce what you reported. I simply copied and pasted all your code (in the proper order, adding the creation of MySeq and fixing a syntax error in your trigger code: a missing semicolon after the INSERT statement), then I inserted a row in the base table. A row was inserted in the audit table as expected. Here is what I did (see below). Please try to figure out what you did differently from what I did below, since what you reported so far does not match with my experience.
CREATE TABLE "MY_CONSULTANT_TABLE" ( "CONSULTANT_ID" NUMBER(*,0) NOT NULL ENABLE,"CON_NAME" VARCHAR2(25) NOT NULL ENABLE,"CON_POSTCODE" VARCHAR2(10) NOT NULL ENABLE,"HIGHEST_QUAL" NUMBER(*,0) NOT NULL ENABLE,"CON_REGISTERED" DATE,"PERMISSIONS" VARCHAR2(20),CONSTRAINT "PK_MY_CONSULTANT_TABLE" PRIMARY KEY ("CONSULTANT_ID") USING INDEX ENABLE )/DROP TABLE MyAuditTable;CREATE TABLE MyAuditTable ( audit_id INTEGER NOT NULL, new_name VARCHAR2 (30), new_postcode VARCHAR2 (20), status VARCHAR2 (15), CONSTRAINT pk_MyAuditTable PRIMARY KEY ( audit_id ));create sequence myseq;drop trigger MyTrigger;create trigger MyTriggerafter insert on my_consultant_tablefor each rowbegin insert into MyAuditTable values ( MySeq.nextval, :new.con_name, :new.con_postcode, 'Pending'); -- added the semicolon here (missing from your code)
end;/insert into my_consultant_table (consultant_id, con_name, con_postcode, highest_qual) values (1001, 'Will', 'D29', 5);select * from myaudittable; AUDIT_ID NEW_NAME NEW_POSTCODE STATUS ---------- ------------------------------ -------------------- --------------- 1 Will D29 Pending -
Thanks for the response,
It was that semicolon and after INSERT was changed to after UPDATE as I'm attempting to demo on existing data
A silly mistake but now works.
I am attempting to only let these changes pass if the status in the audit table is changed to 'Authorised'
Is this possible by using an if statement within the trigger?
Or does a seperate BEFORE UPDATE trigger need to be made for my consultant table?
Thanks again
-
If your real code was missing the semi-colon then the trigger wouldn't have compiled and any attempt to insert (or is that update?) into my_consultant_table would have failed with an error.
So how did you manage to miss that?
What does "only let these changes pass" mean?