Simple Trigger Issue?

JazzyB · Jan 2, 2019 10:08AM

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.

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 ) ); 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') end; /

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!

AndrewSayer · Jan 3, 2019 8:04AM

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

mathguy · Jan 2, 2019 11:19AM

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?

Gary_A · Jan 2, 2019 2:41PM

A blind insert without naming the columns is an accident waiting to happen.

JazzyB · Jan 2, 2019 4:33PM

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

jaramill · Jan 2, 2019 4:36PM

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.

JazzyB · Jan 2, 2019 4:37PM

Thanks for the reply mate

Yeah definitely, I'm just trying to demonstrate audit tables.

Revised my question a little due to an update

JazzyB · Jan 2, 2019 4:46PM

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

)

/

jaramill · Jan 2, 2019 6:47PM

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.

mathguy · Jan 2, 2019 7:02PM

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

JazzyB · Jan 3, 2019 7:33AM

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

Cookiemonster76 · Jan 3, 2019 7:50AM

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?

Forum Stats

Quick Links

Categories

Simple Trigger Issue?

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Simple Trigger Issue?

Best Answer

Answers