SQL & PL/SQL

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.

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 MyTrigger
after insert on my_consultant_table
for each row
begin
  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?

Why do you need a trigger and an "audit" table at all?  Looks to me like you could just add a status column to your 'consultants' table.

This is simply to demonstrate how a trigger can be used so that a consultant must gain approval from an admin.

Consultants are using an apex application to view their own data, and to request a change to either name or postcode.

So it's an admin that allows these by changing the value from Pending to Authorised.

Just for an assignment!

JazzyB wrote:
This is simply to demonstrate how a trigger can be used so that a consultant must gain approval from an admin.
Consultants are using an apex application to view their own data, and to request a change to either name or postcode.
So it's an admin that allows these by changing the value from Pending to Authorised.
Just for an assignment!

Oh, so the whole thing is a homework question . . . .

I honestly tried so many methods I don't remember haha, only been learning SQL just shy of a year so I still make them mistakes.

Basically I want it so that the update will take place on the consultant table once that 'Pending' in the audit table has been changed to 'Authorised'.

At the moment, the consultant's changes are being immediately updated.

So I'm unsure how to do this as the 'Pending' value is in a separate table.

Hope this makes sense!

JazzyB wrote:
I honestly tried so many methods I don't remember haha, only been learning SQL just shy of a year so I still make them mistakes.
Basically I want it so that the update will take place on the consultant table once that 'Pending' in the audit table has been changed to 'Authorised'.
At the moment, the consultant's changes are being immediately updated.
So I'm unsure how to do this as the 'Pending' value is in a separate table.
Hope this makes sense!

So you want to do the update, but have the update float in some limbo somewhere until the audit table is updated?

That's not going to work, certainly not with triggers. The effects of the trigger are part of the same transaction that caused the trigger to fire. All the changes have to be either committed or rolled back together and no other user can see the audit record to approve it until the commit happens.

Look at Andrew's suggestion in reply #15 - you'll need something along those lines, not triggers.

Thanks for such an insightful response

I'll look into this and can hopefully get it working

Much appreciated!