Forum Stats

  • 3,839,017 Users
  • 2,262,438 Discussions
  • 7,900,836 Comments

Discussions

Simple Trigger Issue?

JazzyB
JazzyB Member Posts: 8
edited Jan 3, 2019 8:35AM in SQL & PL/SQL

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!

Tagged:

Best Answer

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Jan 3, 2019 8:07AM 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

«1

Answers

  • mathguy
    mathguy Member Posts: 10,595 Blue Diamond
    edited 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
    Gary_A Member Posts: 624 Bronze Badge
    edited Jan 2, 2019 2:41PM

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

  • JazzyB
    JazzyB Member Posts: 8
    edited 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
    jaramill Member Posts: 4,299 Gold Trophy
    edited 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
    JazzyB Member Posts: 8
    edited 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
    JazzyB Member Posts: 8
    edited 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
    jaramill Member Posts: 4,299 Gold Trophy
    edited 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
    mathguy Member Posts: 10,595 Blue Diamond
    edited 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
    JazzyB Member Posts: 8
    edited 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
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited 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?